Adding a Table Lock on Purpose

Question

Post reply

Adding a Table Lock on Purpose

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
March 1, 2013 at 2:14 pm

#290463

Hello Everyone
I have a rather odd situation. I want to add a lock to a table on purpose. I need to update and then select from that table before anything else can happen. This is in the middle of a very large stored procedure, and I cannot log that table during the entire transaction of the entire stored procedure. I can Lock the table for the very minimum amount of time.
@NumberOfListToInsert is set by @@ROWCOUNT after an Insert into a table. I then re-key that table starting with the number from the Keys table
UPDATE
dbo.Keys
SET MaxCount = (MaxCount + @NumberOfListToInsert) + 5
WHERE
TableName = 'Diagnosis';
SET @MaxKeyNumber = (SELECT MaxCount AS MaxKeyCount
FROM dbo.Keys
WHERE TableName = 'Diagnosis')
I need for both of these to start and complete before any other query can access the "Keys" table.
Can I simply wrap this in a Being Tran and Commit Tran? Or should I use TABLOCKX ?
I am not sure how to use TABLOCKX, so if you can give an example that would be very helpful.
Thanks in advance for your help, suggestions and code samples.
Andrew SQL DBA
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

Erin Ramsay

SSCrazy Eights

Points: 9728

March 1, 2013 at 3:03 pm

#1592598

Nevermind. I misread the query.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

AndrewSQLDBA (3/1/2013)
Hello Everyone
I have a rather odd situation. I want to add a lock to a table on purpose. I need to update and then select from that table before anything else can happen. This is in the middle of a very large stored procedure, and I cannot log that table during the entire transaction of the entire stored procedure. I can Lock the table for the very minimum amount of time.
@NumberOfListToInsert is set by @@ROWCOUNT after an Insert into a table. I then re-key that table starting with the number from the Keys table
UPDATE
dbo.Keys
SET MaxCount = (MaxCount + @NumberOfListToInsert) + 5
WHERE
TableName = 'Diagnosis';
SET @MaxKeyNumber = (SELECT MaxCount AS MaxKeyCount
FROM dbo.Keys
WHERE TableName = 'Diagnosis')
I need for both of these to start and complete before any other query can access the "Keys" table.
Can I simply wrap this in a Being Tran and Commit Tran? Or should I use TABLOCKX ?
I am not sure how to use TABLOCKX, so if you can give an example that would be very helpful.
Thanks in advance for your help, suggestions and code samples.
Andrew SQL DBA

Firstly, you don't need two statements for that...one will do

UPDATE

dbo.Keys

SET @MaxKeyNumber = MaxCount = (MaxCount + @NumberOfListToInsert) + 5

WHERE

TableName = 'Diagnosis';

Now the other point of not locking the table for the whole process - that all depends on how you are working - do you have BEGIN TRAN....COMMIT TRAN anywhere?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

As Magoo suggests, if you include the UPDATE and SELECT in a single transation, then only people that did the end-around with things like WITH(NOLOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED will be able to read from your table. I don't recall for sure but I believe if you add the hint WITH(TABLOCKX) to the UPDATE, it might prevent that. You'd have to try it to be sure. I'd do it for you but I just don't have the time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Thank You to everyone

I have been working with TabLockX, wrapped in a transaction. And I changed the code to perform everything all together. Seems to do the trick

Thanks again to everyone for all your assistance and suggestions.

Greatly appreciate it

Andrew SQLDBA