Need advice on TABLOCKX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need advice on TABLOCKX

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2012-01-12 : 02:31:01

Hi

I know that the command TABLOCKX is use to gain exclusive lock of a table. After the table is locked, when will it be unlocked?

Considering I have these 2 statements:


Select * From TableA with (TABLOCKX)

Insert Into Tabla A Values(@newValue)

When I applied TABLOCKX on Select statement, when will the TableA be unlocked? After Statement 1 or 2?

When I am looking for is that the TableA is unlocked after statement 2 as no other process can access TableA without both statement being executed.

Kindly advice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 03:14:09

the locking hint will have scope only for select statement. so once select statement is executed it should get released

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 03:14:47

if you want tableA to be locked why not wrap both the statement inside a transaction?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-12 : 05:00:13

If you want the X Lock held until the update, this will do it

BEGIN TRANSACTION
BEGIN TRY
Select * From TableA with (TABLOCKX)

Insert Into Tabla A Values(@newValue)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- any other error handling goes here
END CATCH

--
Gail Shaw
SQL Server MVP

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2012-01-12 : 19:22:37

This seems very good solution. Will try it out.

Sorry, I am not familiar with TRANSACTION. What if I apply the TRANSACTION structure and remove the TABLOCKX, does it produce same result?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-12 : 19:36:29

No, without the TablockX hint it'll be a shared table lock, not an exclusive. So it'll prevent anyone else from inserting, updating or deleting, but will allow others to read and, if this piece of code could be run in parallel by multiple users it could deadlock.

--
Gail Shaw
SQL Server MVP

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2012-01-12 : 19:47:35

So by applying the TRAN and TABLOCKX together, whenever there is multiple access, it should apply the queue concept is it? If yes, this is wnat I am looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 03:32:16

quote:
Originally posted by ryanlcs

So by applying the TRAN and TABLOCKX together, whenever there is multiple access, it should apply the queue concept is it? If yes, this is wnat I am looking for.

yep...only after lock is released it can be accessed by next process

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources