DEADLOCK

[Brett]

I am unable to control the granularity of locks in our queries.
We are running queries through MTS and are getting deadlocks.

The batch includes two inserts and one select query - all are hitting on
only one table. This table has a unique clustered and a unique nonclustered index as well
as a primary key.

Within the batch, I have given a table hint to set transaction isolation level
to READCOMMITTED, ROWLOCK for the insert statements, like this

INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........

and the same for the Select statement.

SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)

When I run sp_lock on the spid, I get output indicating that
SS7 is placing a IX lock on the table. I'm pretty sure this is
a big contributor to the deadlock.

I get the deadlock when I try to run more than one client with similar insert parameters.

How can I control the granularity to just rowlocks?

All help is appreciated.
Thanks

Brett

[Gary Andrews]

Its not clear to me why you want to control the locking scheme.

As best as I understand, unless you have an identified problem
you are attempting to resolve, leave the locking to SQL Server.

Therefore my 1st choice would be to eliminate trying to control
the locking.

My 2nd choice, maybe your application would allow you to break
the one batch into two and put the select into a batch by itself???????

Or break into three batches????

Heres hoping for the best...


------------
Brett at 7/27/00 3:52:24 PM

I am unable to control the granularity of locks in our queries.
We are running queries through MTS and are getting deadlocks.

The batch includes two inserts and one select query - all are hitting on
only one table. This table has a unique clustered and a unique nonclustered index as well
as a primary key.

Within the batch, I have given a table hint to set transaction isolation level
to READCOMMITTED, ROWLOCK for the insert statements, like this

INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........

and the same for the Select statement.

SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)

When I run sp_lock on the spid, I get output indicating that
SS7 is placing a IX lock on the table. I'm pretty sure this is
a big contributor to the deadlock.

I get the deadlock when I try to run more than one client with similar insert parameters.

How can I control the granularity to just rowlocks?

All help is appreciated.
Thanks

Brett

Thanks for the reply.

I already ran the idea of breaking up the batch with the developers.
They insist that it's integral to have all the statements run
as one - thereby getting a point-in-time picture. However,I'm with you on that one.

I want to control the locking scheme, because when I run the batch
without any attempt to control, I get the deadlocks.

I would like to know that when I set ROWLOCK that I am getting ROWLOCK and not some other higher level lock as well. Does anyone have a process or methodology to share?

------------
Gary Andrews at 7/27/00 4:18:22 PM

Its not clear to me why you want to control the locking scheme.

As best as I understand, unless you have an identified problem
you are attempting to resolve, leave the locking to SQL Server.

Therefore my 1st choice would be to eliminate trying to control
the locking.

My 2nd choice, maybe your application would allow you to break
the one batch into two and put the select into a batch by itself???????

Or break into three batches????

Heres hoping for the best...


------------
Brett at 7/27/00 3:52:24 PM

I am unable to control the granularity of locks in our queries.
We are running queries through MTS and are getting deadlocks.

The batch includes two inserts and one select query - all are hitting on
only one table. This table has a unique clustered and a unique nonclustered index as well
as a primary key.

Within the batch, I have given a table hint to set transaction isolation level
to READCOMMITTED, ROWLOCK for the insert statements, like this

INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........

and the same for the Select statement.

SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)

When I run sp_lock on the spid, I get output indicating that
SS7 is placing a IX lock on the table. I'm pretty sure this is
a big contributor to the deadlock.

I get the deadlock when I try to run more than one client with similar insert parameters.

How can I control the granularity to just rowlocks?

All help is appreciated.
Thanks

Brett

[Gary Andrews]

I don't have any first hand experience with deadlocks,
so can't provide any practical advice (sorry).

Its my understanding that even when locking hints
are provided, SQL 7 will escalate if it thinks it needs
to.

You may want to go to www.sqlmag.com, select
menu pick "topics". There are several topics on locking.

I have a book called "Learn SQL Server 7 in 21 days",
by QUE (I think). It has an excellent section describing
locking. How to use EM to see who has what. I think
there is a stored procedure called sp_lock (or locks).

I assume you have already checked BOL. I know BOL
frequently does not present the topics in a way which
is helpful or in many cases (so it seems to me) what
I am looking for is simply omitted.

Again, best wishes...




------------
Brett at 7/27/00 3:52:24 PM

I am unable to control the granularity of locks in our queries.
We are running queries through MTS and are getting deadlocks.

The batch includes two inserts and one select query - all are hitting on
only one table. This table has a unique clustered and a unique nonclustered index as well
as a primary key.

Within the batch, I have given a table hint to set transaction isolation level
to READCOMMITTED, ROWLOCK for the insert statements, like this

INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........

and the same for the Select statement.

SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)

When I run sp_lock on the spid, I get output indicating that
SS7 is placing a IX lock on the table. I'm pretty sure this is
a big contributor to the deadlock.

I get the deadlock when I try to run more than one client with similar insert parameters.

How can I control the granularity to just rowlocks?

All help is appreciated.
Thanks

Brett

[Brett Cavanagh]

Thanks for the extra fuel. I think you are right about SS7
escalating on it's own and I will check out sqlmag/topics.

Thanks again.


------------
Gary Andrews at 7/28/00 1:14:22 PM

I don't have any first hand experience with deadlocks,
so can't provide any practical advice (sorry).

Its my understanding that even when locking hints
are provided, SQL 7 will escalate if it thinks it needs
to.

You may want to go to www.sqlmag.com, select
menu pick "topics". There are several topics on locking.

I have a book called "Learn SQL Server 7 in 21 days",
by QUE (I think). It has an excellent section describing
locking. How to use EM to see who has what. I think
there is a stored procedure called sp_lock (or locks).

I assume you have already checked BOL. I know BOL
frequently does not present the topics in a way which
is helpful or in many cases (so it seems to me) what
I am looking for is simply omitted.

Again, best wishes...




------------
Brett at 7/27/00 3:52:24 PM

I am unable to control the granularity of locks in our queries.
We are running queries through MTS and are getting deadlocks.

The batch includes two inserts and one select query - all are hitting on
only one table. This table has a unique clustered and a unique nonclustered index as well
as a primary key.

Within the batch, I have given a table hint to set transaction isolation level
to READCOMMITTED, ROWLOCK for the insert statements, like this

INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........

and the same for the Select statement.

SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)

When I run sp_lock on the spid, I get output indicating that
SS7 is placing a IX lock on the table. I'm pretty sure this is
a big contributor to the deadlock.

I get the deadlock when I try to run more than one client with similar insert parameters.

How can I control the granularity to just rowlocks?

All help is appreciated.
Thanks

Brett