Release of S locks

Question

Release of S locks

slowjoe

SSC-Addicted

Points: 442
More actions
April 27, 2007 at 9:04 am

#95298

Hello there,
can anyone confirm that in transaction isolation level "read committed" SQL Server 2000 does not release share locks, which are issued for a query, until all affected rows of a particular page are read (= until the page is processed completely)?
In my eyes this would be more restrictive than "read committed" is supposed to be. Other DBMSes release a share lock immediately after having read that row. Thus, SQL Server 2000 works somewhere in between "read committed" and "repeatable read".
If this is true, is SQL Server 2005 working the same way?
Regards,
Johannes

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 1

I don't believe that the above is correct. The shared locks will not be released in the example below (which is not issued from a query but from an INSERT statement):

Open 2 windows in query analyzer or any other SQL tool. The two windows should be working on the same DB. In the first window, type:

CREATE TABLE MyTable (col1 int)

GO

and execute it. The in the same window (window #1), type and execute the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

INSERT INTO MyTable VALUES(1)

Now go to the other window and type and execute

SELECT * FROM MyTable

It will hang; all is locked.

To exit, run

COMMIT TRAN

in window #1.

However, if the transaction contains a query (which to the best of my understanding is what you're asking about), there is no shared lock on all rows in the page. You can test it easily by doing:

Window #1:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT * FROM MyTable

Window #2:

INSERT INTO MyTable VALUES(2)

(then make sure to commit again in Window #1).

Also- if you have problems with locking all rows in a page, why not use ROWLOCK exlicitly?

The only meaning transactional change in SQL 2K5 (that I know of) is the new SNAPSHOT transaction mode. I do not know enough about it to elaborate though.

slowjoe SSC-Addicted Points: 442 More actions · Answer 2

Hello,

thanks for your reply.

Well, this is not exactly, what I was talking about.

First, I should have mentioned that I use ROWLOCK.

Second, I don't say that a query maintains the share locks it has set until the end of a transaction. It probably does not even maintain the locks until the end of its execution. (Which is good and desired in "read committed" mode.)

Let me give an example:

_______________________________________________________

Preconditions: transaction isolation level "read committed", explicit use of hint ROWLOCK.

Scenario:

There are data rows A, B, C, D and E. These are distributed across two pages P1 and P2.

P1 contains A, B and C. P2 contains D and E.

There may be more data on these pages... doesn't matter.

A query is issued which selects all 5 rows.

What happens when using SQL Server:

#1 - Row A is read from P1, thus an S lock is set

#2 - Row B is read from P1, thus an S lock is set

#3 - Row C is read from P1, thus an S lock is set

#4 - All corresponding rows from P1 are read, S locks on row A, B and C are released

#5 - Row D is read from P2, thus an S lock is set

#6 - Row E is read from P2, thus an S lock is set

#7 - All corresponding rows from P2 are read, all S locks on row D and E are released

Any other DBMS that I know of (like DB2) works like this:

#1 - Row A is read from P1, thus an S lock is set

#2 - S lock on row A is released

#3 - Row B is read from P1, thus an S lock is set

#4 - S lock on row B is released

#5 - ...

_______________________________________________________

This is what I have heard... I would be glad, if someone can point me to official documentation, that states row locks are released immediately after having read the particular row.

Regards,

Johannes

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

slowjoe SSC-Addicted Points: 442 More actions · Answer 4

slowjoe

SSC-Addicted

Points: 442

April 28, 2007 at 1:40 am

#703106

"Serializable" would make it even worse...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Sorry, Joe... I misread the post quite badly... thought you were trying to do locked reads.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 6

I have read the SQL architecture books several times and cannot recall any lock coverage in granular details, which is what you are looking for. Very interesting question. I would very much like to know the answer myself... very interesting...

slowjoe SSC-Addicted Points: 442 More actions · Answer 7

Finally, I think, that I have misunderstood something.

I could imagine such a behavior for default settings, which are READ COMMITTED and PAGE-LEVEL LOCKING. Then it would make sense to release locks, when a page is done processing.

Regards,

Johannes