Why SERIALIZABLE level does not lock the table for other session in SQL Server

concurrencyquerysql serversql-server-2008sql-server-2008-r2

I want to Prevent concurrency in my table for Delete, Insert, Update, and Select
so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how to fix it ?

What have I tried :

1- Create Table_1

    --Create Sample Table
CREATE TABLE [dbo].[Table_1](
    [Number] [bigint] NOT NULL,
    [Price] [bigint] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Table_1] ([Number], [Price]) VALUES (1, 1000)
GO
INSERT [dbo].[Table_1] ([Number], [Price]) VALUES (2, 3000)
GO
INSERT [dbo].[Table_1] ([Number], [Price]) VALUES (3, 8000)
GO

2- Query for Session one with delay :

--SESSION 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

WAITFOR DELAY '00:00:10.000'
UPDATE Table_1 SET Price = 6000 WHERE NUMBER = 1
SELECT * FROM Table_1

COMMIT TRANSACTION;

3- Query for session two , and this query most wait for session one till end proecess but it's not ! :

--SESSION 2 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

SELECT * FROM Table_1

COMMIT TRANSACTION;

enter image description here

Best Answer

Your delay is in the wrong place.

You're waiting for ten seconds in transaction 1 before making any changes.

Transaction 2 has no reason to wait before transaction 2 has made any changes.

Put the delay after the update, and you will see blocking.

The serializable isolation level guarantees an outcome the same as if the transactions had run sequentially, in some order. It does not require the transactions to actually behave that way.

If you want to prevent all concurrency, take an exclusive table lock. See the related Q & A Request Sch-M lock on a table for details.

Related Question