CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
Matthew Crews
Matthew Crews

Reputation: 4295

Does TRANSACTION ISOLATIoN LEVEL SERIALIZABLE create READ lock

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

Upvotes: 3

Views: 2513

Answers (2)

MWillemse
MWillemse

Reputation: 980

TRUNCATE TABLE will acquire a exclusive schema 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 presented 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.

Upvotes: 1

James Z
James Z

Reputation: 12318

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

Upvotes: 2

Related Questions