Does TRANSACTION ISOLATIoN LEVEL SERIALIZABLE create READ lock

2
votes

I can't seem to find a straight answer on what should be a simple question. If I create a transaction in T-SQL and set the ISOLATION LEVEL to SERIALIZABLE, does this create a READ lock on the tables that I am modifying?

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO    
TRUNCATE TABLE TBL_PRODUCTS;
GO
    **INSERT RECORDS HERE**
GO
    COMMIT TRANSACTION;
GO
2
sqlsql-servertsqltransactions

2 Answers

1
votes

TRUNCATE TABLE will acquire a exclusive shema modify lock on the table preventing all users from reading from the table (unless they use TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or WITH(NOLOCK)) and writing to the table (no exceptions for writing). The exclusive lock will be released at COMMIT TRANSACTION.

EDIT: As Martin Smith pointed out in his comment below the truncate table will acquire a schema modify lock. Meaning there are no other user will be able to read or modify the table whatsoever until a commit or rollback has taken place.

0
votes

Yes, it will lock the table, and these are the rules for serializable:

  • Statements cannot read data that has been modified but not yet committed by other transactions.
  • No other transactions can modify data that has been read by the current transaction until the current transaction completes.
  • Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

https://msdn.microsoft.com/en-us/library/ms173763.aspx