Alternatively, you could change the isolation level of the connection to Repeatable Read, which isn't a bad solution. However, doing so changes the isolation behavior for all operations on that connection. To affect just the lock on the row that has the text pointer, the REPEATABLEREAD hint is a better choice. (Two other locking hints, HOLDLOCK and SERIALIZABLE, would also prevent the text pointer from being invalidated, but they're stronger than is necessary. Not only do these two hints, which are equivalent, prevent the retrieved data from changing, they can also prevent insertion of new rows into the table.)
When SQL Server acquires a text pointer for text data in a table that has text in row enabled, the data row is locked with a shared lock if the transaction's isolation level is the default READ COMMITTED or higher and the database isn't in read-only or single-user mode. The lock ensures that nobody else can modify or delete the row while you have a text pointer on a text value from that row. SQL Server releases this lock when the text pointer becomes invalid. At the end of the transaction, all text pointers are invalid, so the locks are all released. The shared locks on rows with in-row text are held until the end of a transaction, so you can't get a text pointer for in-row text except in a user-defined transaction.
Listing 3 creates a new table that has a text column and inserts one row into it. It then attempts to obtain a text pointer and use READTEXT to return some of the data in the text column. The code in Listing 3 doesn't work, and the error message you receive is misleading:
Server: Msg 7101, Level 16, State 1, Line 3
You cannot use a text pointer for a table with
option 'text in row' set to ON.
Server: Msg 7133, Level 16, State 1, Line 4
NULL textptr (text, ntext, or image pointer)
passed to READTEXT function.
However, attempting to perform the same text pointer assignment and READTEXT operation inside a user-defined transaction, as Listing 4 shows, succeeds.
When the text data is outside the data row, you don't have to use a user-defined transaction except to guarantee that the text pointer remains valid. For text-in-row data, you can't use a text pointer unless you've defined a transaction.
End of Article
Prev. page
1
[2]
next page -->
