CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Developer Webs
Developer Webs

Reputation: 1021

SQL Server : Tablock, locks before or after select?

In the following example, when using with (tablockx) is the locking done before the select determines the max value, or after? Is there a race condition with insert statements inserting into table "table1", or is it guaranteed that @foo will contain the max value found in the table until the transaction is committed or rolled back?

begin Transaction

declare @foo int = (select max(col1) from table1 with (tablockx))
-- Is it possible that max(col1) can be > @foo here?

Commit Transaction

If it matters, I'm using SQL Server 2008 R2

Upvotes: 2

Views: 2769

Answers (2)

S3S
S3S

Reputation: 25122

Is the locking done before the select determines the max value, or after

Before, by the query optimizer before it is executed. It wouldn't help to do it after, after all. This is the locking method for statement you are executing.

Is there a race condition with insert statements inserting into table "table1"

No, since you are using TABLOCKX and not just TABLOCK. The latter allows shared locks, but you are getting an exclusive lock on the table via TABLOCKX.

Is it guaranteed that @foo will contain the max value found in the table until the transaction is committed or rolled back

Yup, all other transactions will be blocked (deletes, inserts, updates, etc)

TESTING

To test this, create a table an insert a value

create table t1_delete (col1 int)
insert into t1_delete
values (1)
go

Next, in one SSMS panel run your code but comment out the COMMIT TRAN

begin Transaction

declare @foo int = (select max(col1) from t1_delete with (tablockx))
-- Is it possible that max(col1) can be > @foo here?
select @foo
--Commit Transaction

Now, in a new SSMS window try to insert a new value, or whatever

insert into t1_delete
values(2)

You will notice the query spins. You can see why if you run exec sp_whoIsActive from Adam Mechanic in another query window. Specifically, check out the blocking_session_id for your insert session. It will be the session with sql_test like begin transaction declare @foo....

Don't forget to commit the transaction after the test

Upvotes: 2

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

Tablockx will grab an exclusive lock that is released when the transaction is committed or rolled back. So your comment area would be blocked.

Upvotes: 2

Related Questions