SQL Server Does TRANSACTION ISOLATIoN LEVEL SERIALIZABLE create READ lock

xdnvmnnf 于 2023-03-22 发布在其他

关注(0)|答案(2)|浏览(111)

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

sql-server

来源：https://stackoverflow.com/questions/28704177/does-transaction-isolation-level-serializable-create-read-lock

2条答案

按热度按时间

zaq34kh61#

TRUNCATE TABLE will acquire a exclusiveschema 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.

赞(0）回复(0）举报 2023-03-22

a8jjtwal2#

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

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server Does TRANSACTION ISOLATIoN LEVEL SERIALIZABLE create READ lock

2条答案

相关问题

热门标签

最新问答