2

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

1 Answer 1

6

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.

2
  • thanks, so is this another way : SELECT MAX(NUMBER + 1) FROM Table_1 WITH (TABLOCK,HOLDLOCK) ? Aug 12 at 14:47
  • 4
    No, that's unnecessarily complicated and redundant. HOLDLOCK just means SERIALIZABLE. They're synonyms. Just take the exclusive table lock as in the linked example.
    – Paul White
    Aug 12 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.