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

General SQL Server Forums

New to SQL Server Programming

TABLOCKX

Author

Topic

marduk
Starting Member

2 Posts

Posted - 2009-10-21 : 11:52:22

I'm trying to establish an exclusive locked session. The syntax I'm using is:
BEGIN TRAN
SELECT * FROM Sales WITH (TABLOCKX, HOLDLOCK)

When I try to execute this I receive an error saying incorrect syntax. It seems that SQL is not recognizing the TABLOCKX command. I've gone through the books online and found a nearly identical statement for this. Can anyone give me an idea why this won't execute. I'm using a Standard Edition SQL 2005 running on an Enterprise Edition Server 2003.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-21 : 12:06:57

Try this:


declare @int int
BEGIN TRAN
set @int = (SELECT top 1 1 FROM Sales WITH (TABLOCKX, HOLDLOCK))

marduk
Starting Member

2 Posts

Posted - 2009-10-21 : 12:30:15

RickD,
Thanks, that worked perfectly.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-21 : 14:27:24

quote:
Originally posted by marduk

RickD,
Thanks, that worked perfectly.

[slaps face]

you might need this

SELECT @@TRANCOUNT

[/slaps face]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-21 : 15:57:29

quote:
Originally posted by X002548

quote:
Originally posted by marduk

RickD,
Thanks, that worked perfectly.

[slaps face]

you might need this

SELECT @@TRANCOUNT

[/slaps face]

Well, they only said that they wanted to establish a lock - they never said anything about releasing it :)

Be One with the Optimizer
TG

rajeshawate2001
Starting Member

1 Post

Posted - 2011-05-03 : 05:49:45

hi,

i want to use TABLOCKX lock on my table. i have used same query given in above post.
but problem is lock does not get released after completion of transaction holding lock on table. hence another transaction keeps waiting.
following are my 2 query i tried in query analyzer of sql server.
Please help me...

Query 1:
BEGIN TRAN A
SELECT * FROM alerts with (TABLOCKX, HOLDLOCK)

declare @cnt bigint
set @cnt = 1000000
print 'start time = '+cast(getdate() as varchar)
while @cnt > 0
begin
print 'Cnt = ' + cast(@cnt as varchar)
set @cnt = @cnt - 1
end
print 'end time = '+cast(getdate() as varchar)
COMMIT TRAN A
-----------------------------------------------------------------------------------
Query 2:
BEGIN TRAN B
print 'start time = '+cast(getdate() as varchar)
SELECT * FROM alerts --with (TABLOCKX)
print 'end time = '+cast(getdate() as varchar)
COMMIT TRAN B

Please let me know if i'm doing anything wrong.

Thank in advance...
Rajesh.

rajeshawate2001

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-05-03 : 06:44:05

The code works fine for me. Not sure why you are committing when you have nothing to commit, you could just as easily ROLLBACK, but there you go.

The second query will not run until the first is complete as it is waiting on the release of the lock.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources