How to implement Serializable Isolation Level in SQL Server

Question

I need to implement a serializable isolation level in SQL Server but I've tried many ways and I don't get it.

I need to lock 1 row in one transaction (It doesn´t matter if lock the complete table). So, another transaction can´t even select the row (don´t read).

The last thing I tried:

For transaction 1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SELECT code FROM table1 WHERE code = 1

-- Here I select in another instance the same row

COMMIT TRAN

For transaction 2:

BEGIN TRAN
SELECT code FROM table1 WHERE code = 1
COMMIT TRAN

I would expect that transaction 2 wait until transaction 1 commit the operation, but the transaction 2 gives me the row.

Anyone can explain me if I miss something?

Answer 1

SQL Server conforms to the strict definition of a Serializable query. That is, there must be a result that can logically be generated IF both queries ran in serial order - Transaction 1 finishing before Transaction 2 can start, or vice versa.

This results in some effects that can be different than you would expect. There is a great explanation of the Serializable isolation level over at SQLPerformance.com that makes clear some of what this logical serializability ends up meaning. (Very helpful site, that one.)

For your above queries, there is no logical requirement to prevent the second query from reading the same row as the first query. No matter in what order the queries are run, they will both return the same data without modifying it. Since the Query Analyzer can identify this, there is no reason to place a read lock on the data. However, if one of the queries performed an update on the data, then (warning - logic assumption here, since I don't actually know the internals of how SQL Server handles this) the QA would set a stronger lock on the selected rows.

TL;DR - SQL Server wants to minimize blocking, so it uses logical analysis to see what types of locks are needed for a serializable isolation level, and it (tries to) use the minimum number and strength of locks needed to achieve its goal.

Now that we've dealt with that - there are only two ways that I can think of to lock a row so that no one else can read it: using XLOCK + TABLOCK (locking the whole table - not a recommended practice) or having some form of a field on each row that is updated when you start your process - something like an SPID field, or a bit flag for Locked. When you update it within your transaction, only SELECTs with NOLOCK hints will be able to read it.

Clearly, neither of these are optimal. I recommend the "This row is busy - go away" flag, as that's probably the approach I would take for an (almost) absolute lock on a row.

Answer 2

According to the documentation:

SERIALIZABLE Specifies the following:

• 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.

If you're not making any changes to data with an INSERT, UPDATE, or DELETE inside transaction 1, SQL will release the Shared Lock after the read completes.

What you might want to try is adding a table hit to prevent the row lock from being released until the end of transaction 1.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SELECT code 
FROM table1 WITH(ROWLOCK, HOLDLOCK)
WHERE code = 1

COMMIT TRAN

Answer 3

Maybe you can solve this with some hack like this?

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
UPDATE someTableForThisHack set val = CASE WHEN val = 1 THEN 0 else 1 End
SELECT code from table1.....
COMMIT TRANSACTION

So you create a table someTableForThisHack and insert one row to it.