Share Read Lock is blocking with another running connections

Question

Share Read Lock is blocking with another running connections

mshah23

SSChasing Mays

Points: 649
More actions
September 9, 2008 at 11:52 am

#382576

We are facing a wait issue when our application runs in DEBUG mode.
it is SQL Server 2005 with SP2 environment and we are using a SQL Server 2005 driver.
WAS is on 6.0.1
In DEBUG mode, at one point once connection is in processs to update one table and created 3 "X" locks with "KEY" lock type on that table, actually in same time a new connection creates which select some different row from the same table.
When I look the java code, it is opening a record set and trying to do the rs.next.
it open a record set, however as soon as the rs.next statement excute it create a two new share read locks
1. First share mode lock got successfully executed. I am assuming this is for the row which is
inside the table.
2. however in the same time I am seeing one more share read lock which is in WAIT mode.
I am not able to understand why it is creating 2 share read locks, when there is one row?
For testin purpose, When I deleted that particular row. In this condition while doing a rs.next
it creates a one share read lock which goes in WAIT mode. again the same question
if there is no row then why it is creating a share read lock which is going into the WAIT mode.
This issue is coming in both the isolation level READ COMMITED and REPETABLE READ.
Hope I exlpain correctly. Please let me know if you need more clarifications.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You have two connections looking at the same table. Each generates a Share Read Lock. If they both lock the same row, you'll get two. This is normal and expected behavior and should not cause a problem for you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mshah23 SSChasing Mays Points: 649 More actions · Answer 2

Thanks for the reply.

In second connection which is getting fire a select stmnt, actually firing with diffrent key set.

not the same which is blocked by first connection using the update/delete/insert.

and the second connection which is fire with a selection criteria which is not exist in the table

even thogh while doing a rs.next it is creating a share read lock and goes in WAIT condition.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Locks aren't always just for the rows affected. Often, with updates, inserts, etc., they can spread to pages, extents and even the whole table. Depends on how much data SQL thinks it will have to move around to accomplish the command.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mshah23 SSChasing Mays Points: 649 More actions · Answer 4

In the first connection UPDATE and DELETE are firing with Primary key, even for single only and creating a KEY lock only. for insert also ...I know any new connection select query will wait only when it try to select same INSERTING value which is yet not commited by the first connection.

do you have any way to select some more data for current locks in the database? I am follwing

database motior window which comes with SQL Server 2005.

Steve Jones - SSC Editor SSC Guru Points: 736232 More actions · Answer 5

sp_lock

http://msdn.microsoft.com/en-us/library/aa213025(SQL.80).aspx

mshah23 SSChasing Mays Points: 649 More actions · Answer 6

yeah using this only...I came to know about the KEY locks using this command.

In later analysis I found, as soon as rs.next (with 0 row) executes it creates a new share WAIT lock on the table and if I see the last statement which is executed under this waiting process it shows the Cusrsor name (CUR0000000054) somthing.

Java code is opening this cursor in updatable mode.

As per as I know as soon as the recordset open it retrun the output into the resultset and that data will be in memory till you close the recordset. So after successfully opening a recordset (with 0 rows), when the rs.next execute why it is going to open a one more share read lock on the table, as there is no rows satisfying the select query in that recordset.?