Lock confusion

[shumaker]

I created a test case to exemplify an issue I'm having in a production
application. Below are the scripts. The confusion for me is that if
I run the statements with RepeatableRead isolation level in separate
transactions, then the select statement blocks waiting for the update
statement to complete. I have a Monitor.Wait right after the
ExecuteNonQuery and before Commit for the update transaction so that I
can examine what is happening with the select statement if the update
statement take a long time to complete(or is also being blocked by
something else).

Normally the update transaction would complete, and then the select
would run, but in production I have a more complex situation where
deadlocking is occuring. Before I move on, I want to understand why
the select is waiting for the update to complete.

The statements are operating on mutually exclusive sets of data. One
where Color is Blue, and the other where Color is not blue. So I
don't understand what resource they are both trying to acquire.

In the 2005 studio manager the "Locks by Object" for the Color table
shows 2 locks for the select process, and two locks for the update
process. The first lock for each is granted, and the "Description" is
different. The second lock for each has an identical "Description" of
type Key, and this looks to be the conflict, as the update process has
Status of GRANT and the select process has a status of WAIT for that
resource.

All of the locks are of type KEY.

The case in production is somewhat more complicated, as the conflicts
are on PAGE locks, and thus(I speculated) mutually exclusive where
clauses wouldn't prevent deadlocks, because one record from each
mutually exclusive set could occupy the same page. So this is what I
thought was causing my deadlocking in the first place, but now it
appears that it may not have to do with page level locking.

The select statement is being run with an OleDbAdapter. Both
transaction use RepeatableRead. I have considered using a lower
transaction level, but I'm worried about some schedules that might
produce bad results in production, but I will explore that in more
detail later.

UPDATE Colors SET IsPretty = 'false' WHERE Color <> 'Blue';
SELECT * FROM Colors WHERE Color = 'Blue';

CREATE TABLE [dbo].[Colors](
[SomeKey] [int] IDENTITY(1,1) NOT NULL,
[Color] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsPretty] [bit] NULL,
CONSTRAINT [PK_Colors] PRIMARY KEY CLUSTERED
(
[SomeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Contents:
1 Blue True
2 Green False
3 Orange False

 

[Mary Chipman [MSFT]]

If I understand the situation correctly, you're going to get the same
result with read committed. SQL Server will not allow reads on data
that is locked (i.e., not yet committed) unless you use read
uncommitted, which isn't what you want because you'll get a dirty
read. Not very much time has to elapse before you hit a deadlock and
SQL Server has to decide on a deadlock victim. You can use the SET
DEADLOCK_PRIORITY option in your transaction to designate the victim
and then retry the transaction.

I'd recommend using stored procedures to implement explicit
transactions for your data logic, not client code. This will give you
more control over concurrency errors.

Another option is to implement snapshot isolation, which is designed
for applications where you don't want readers to block writers (and
vice versa). You get the last saved version of the row in a SELECT
(not a dirty read). For more information, see
http://msdn2.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx.

I'm not sure why you feel you need to use OleDb - SqlClient is a
better choice for SQL Server apps.

-mary

I created a test case to exemplify an issue I'm having in a production
application. Below are the scripts. The confusion for me is that if
I run the statements with RepeatableRead isolation level in separate
transactions, then the select statement blocks waiting for the update
statement to complete. I have a Monitor.Wait right after the
ExecuteNonQuery and before Commit for the update transaction so that I
can examine what is happening with the select statement if the update
statement take a long time to complete(or is also being blocked by
something else).

Normally the update transaction would complete, and then the select
would run, but in production I have a more complex situation where
deadlocking is occuring. Before I move on, I want to understand why
the select is waiting for the update to complete.

The statements are operating on mutually exclusive sets of data. One
where Color is Blue, and the other where Color is not blue. So I
don't understand what resource they are both trying to acquire.

In the 2005 studio manager the "Locks by Object" for the Color table
shows 2 locks for the select process, and two locks for the update
process. The first lock for each is granted, and the "Description" is
different. The second lock for each has an identical "Description" of
type Key, and this looks to be the conflict, as the update process has
Status of GRANT and the select process has a status of WAIT for that
resource.

All of the locks are of type KEY.

The case in production is somewhat more complicated, as the conflicts
are on PAGE locks, and thus(I speculated) mutually exclusive where
clauses wouldn't prevent deadlocks, because one record from each
mutually exclusive set could occupy the same page. So this is what I
thought was causing my deadlocking in the first place, but now it
appears that it may not have to do with page level locking.

The select statement is being run with an OleDbAdapter. Both
transaction use RepeatableRead. I have considered using a lower
transaction level, but I'm worried about some schedules that might
produce bad results in production, but I will explore that in more
detail later.

UPDATE Colors SET IsPretty = 'false' WHERE Color <> 'Blue';
SELECT * FROM Colors WHERE Color = 'Blue';

CREATE TABLE [dbo].[Colors](
[SomeKey] [int] IDENTITY(1,1) NOT NULL,
[Color] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsPretty] [bit] NULL,
CONSTRAINT [PK_Colors] PRIMARY KEY CLUSTERED
(
[SomeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Contents:
1 Blue True
2 Green False
3 Orange False