UPDATETEXT with Locking
-
Does anyone know, given that using the 'updatetext' function requires an initial query to obtain the text pointer of the field in question, what the locking situation is?
Ideally I need to lock the row from the moment I obtain the text pointer to make sure that its still under my control when I come to update the text.
Can I use the 'with (updlock)' on the query to obtain the text pointer? But would the lock be dropped once I call 'updatetext'?
Do these locking hints actually last longer than a single line of T-SQL?
Thoughts welcome.
-
Locking hints have different scopes, depending on the hint and also the isolation level. Locking as a subject is quite huge, there are even entire books dedicated to it.
Many scenarios are quite easy to test, though. Just run the statement in one QA window with either a BEGIN TRAN without a commit, or a WAITFOR statement, then go to the 2nd window and see what is locked and what not...
Curios part: is it necessary to have a text column? Sounds a bit awkward that the traffic should be so high as to warrant worries about change management for text data..
/Kenneth
-
OK - thats handy.
Do you know of anything better than sp_lock to determine locking info?
We have a document management system with the documents stored in the DB. Documents are based on Templates with merge fields, so when someone creates a new Document then a stored procedure replaces the macro fields with the computed result e.g. the actual name of the client, todays date etc.
-
There are virtually tons of good info about all aspects on locking in BOL. I urge you to take a look there.
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply