deadlocks

If an instance of SQL 2005 was in use and was using row versioning,
under what circumstances would the below error occur?

Transaction (Process ID 56) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction

We used to get this sort of thing when a large copy process was running
under a transaction, but all it was doing was reading the records and
creating brand new records yet would still lock the entire table. Once
we enabled the row versioning, we stopped having this issue, but it
seems that there are some circumstances in which it still happens, i.e.
the above error.

Any ideas how that might occur?pb648174 (google@.webpaul.net) writes:
> If an instance of SQL 2005 was in use and was using row versioning,
> under what circumstances would the below error occur?
> Transaction (Process ID 56) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction
> We used to get this sort of thing when a large copy process was running
> under a transaction, but all it was doing was reading the records and
> creating brand new records yet would still lock the entire table. Once
> we enabled the row versioning, we stopped having this issue, but it
> seems that there are some circumstances in which it still happens, i.e.
> the above error.
> Any ideas how that might occur?

Without knowledge of the code, and not have seen the deadlock trace?
Not even knowing which of the two varities of snapshot isolation
you are using. SET TRANSACTION LEVEL SHAPSHOT, or READ COMMITTED
SNAPSHOT?

To get a deadlock trace in the SQL Server error log, enable trace
flags 1222 and 3605. (It used be 1204, but 1222 is a new flag, which
gives better information.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I didn't realize there were multiple kinds.. We are using ALTER
DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

My questions is more of a general one - If row versioning is being used
and a particular record is involved in a transaction, should other
transactions just get the older version and not have to respect any
locks? We are seeing blocking happen for normal read operations, which
seems like it shouldn't happen. A write blocking I could see, but the
read blocking doesn't make sense to me.|||pb648174 (google@.webpaul.net) writes:
> I didn't realize there were multiple kinds.. We are using ALTER
> DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

The other one you achieve with ALTER DATABASE db SET
ALLOW_SNAPSHOT_ISOLATION ON. Transactions what want snapshots, then
need to say SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

The two yields slight different results. Pure shapshot isolation, gives
you the state of the database as it looked when the transaction started.
Read Committed Snapshot Isolation (RCSI) is an alternate implementation
of the read committed isolation level. An RCSI transaction can pick up
data that did not exist when the transaction started, but that committed
before the transaction came about to read it.

> My questions is more of a general one - If row versioning is being used
> and a particular record is involved in a transaction, should other
> transactions just get the older version and not have to respect any
> locks? We are seeing blocking happen for normal read operations, which
> seems like it shouldn't happen. A write blocking I could see, but the
> read blocking doesn't make sense to me.

Without any repro it's difficult to comment things out of the blue. However,
note that if you are using alternate isolation level, either by
SET TRANSACTION ISOLATION LEVEL or by query/table hints, the snapshot is
not involved. For instance, run this in one query window:

CREATE TABLE hubba (a int NOT NULL PRIMARY KEY)
go
INSERT hubba(a) VALUES (12)
go
BEGIN TRANSACTION
go
INSERT hubba(a) VALUES (2)
go

Then in another window run:

SELECT MAX(a), MIN(a) FROM hubba

This returns (12, 12). Now try_

SELECT MAX(a), MIN(a) FROM hubba WITH (REPEATABLEREAD)

This blocks, because the isolation level is no longer READ COMMITTED.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ahhhhh... Now we are getting somewhere. I think other transactions are
set as serializable, so that would explain it. Thanks for the tip.

Deadlocking Question

I have a ColdFusion web application running on a SQL Server 7.0 back-end.

Within this application, there is are three queries in a row where the second query deadlocks 1-2 times a day, which is too high. The queries do the following:

1. Insert member details from a web form into member table
2. Select ID (key) of what was just inserted into the member table
3. Update a third table with the member ID

As I said earlier, the 2nd query is the one that I see deadlocked in the ColdFusion error logs. I am unable to replicate the problem, so I have not been able to troubleshoot using the procedures described in SQL-BOL (unless I am mis-understanding the documentation).

This sequence runs an average of 150 times per day, but it can be anywhere from 100 to 500 times, so the failure rate is about 1%.

Any ideas on why this is happening and what I can do to prevent it?

Thanks,
CybermudA spid that does only one thing can't deadlock, it isn't possible.

When a spid accesses an object, it normally takes a lock (of some kind) on that object.

When a spid has an exclusive lock on an object, and another spid tries to access that object, the new spid is "blocked". When a spid is blocked, it stops executing until the object that is causing the blocking becomes available again.

When two spids are running (lets call them 69 and 70), spid 69 locks object A, spid 70 locks object B and everything is still happy. Then 69 attempts to lock object B, but it becomes blocked because 70 already has it locked. Then 70 tries to lock object A, which causes it to be blocked and now we have a deadlock! Both spids are blocked, waiting for each other. SQL Server detects this condition, and picks one of the deadlocked spids as the "victim" and automagically kills the victim (allowing the other spid to proceed).

The best way to avoid deadlocks is to keep your locks small. Don't lock objects for long periods of time. When you do have to lock objects, try to always lock them in the same sequence so that blocks rarely become deadlocks.

If you set Trace flag 1204 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_646r.asp), you'll get more diagnostic information about the cause of the deadlock in your SQL errorlog file.

-PatP|||Pat:

Thanks for the reply. I do not understand what you mean when you say

"A spid that does only one thing can't deadlock, it isn't possible."

Are you saying that since there is only one Cold Fusion application, there will only be one spid? I do understand the principles of deadlocking but can't seem to figure out how to apply them to my situation.

Also, is there any performance hit from leaving trace flag 1204 on for an extended period of time?

Thanks,
Cybermud|||If you think about what a deadlock is, an spid with only one object locked CAN'T deadlock. In order for a deadlock to occur, you must already have one object locked, then try to lock another.

Spid usage depends on how your ColdFusion engine is configured, but typically it will support many threads (therefore many spids). R937 would be able to answer this kind of question much better than I can, although he might prefer you to post it in the ColdFusion (http://www.dbforums.com/f223) forum.

While traceflag 1204 used to impose some significant overhead, I don't believe that is the case anymore. I'd go ahead and run it for a while, but watch for any signs of server distress (just in case!).

-PatP|||Pat:

Thanks for the quick reply. I think I am understanding it correctly...that sequence of the three queries can't be causing a deadlock by itself, because its only one spid, right?

That means there is something else going on...and I will be taking this to the ColdFusion forum.

I also plan on leaving flag 1204 on for the night to see if it turns anything up. Thanks again for all your help.

Cybermud

Deadlocking on indexes?

It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
Joel
The order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200804/1
|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.co m...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>
sql

Deadlocking on indexes?

It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
JoelThe order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200804/1|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.com...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>> It seems that a common problem we have is deadlocks occurring when one
>> stored proc is updating a row and another store proc is running a read
>> query against the same table. They'll often get deadlocked on two of the
>> indexes for the table.
>> For example:
>> SP1 is updating a row in TableA, causing it to get an exclusive lock on
>> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2
>> and attempts to get a shared lock on TableAIndex1.
>> Deadlock!
>> Of course, I can fix this by lowering the isolation level in SP2 to "read
>> uncommitted", but I'd rather not.
>> I can normally fix deadlocks by changing the order in which locks are
>> acquired, but I don't know how to influence the order in which locks on
>> indexes are acquired. Is it controlled by the order in which the fields
>> are listed in a select or an update statement? If I standardize the
>> order in which individual fields are listed whenever I perform a select
>> or update from a given table, will that effect thr order in which index
>> locks are acquired?
>> Thoughts?
>> Joel
>

Deadlock question

I have general question about deadlocks. I get the occasional deadlock when
someone attempts to update a row in the data. I think this might be caused
because the update is happening at the exact same time the row in question
is being updated by a trigger on another table - that I have no control
over.
I'm not sure in general terms how to resolve an issue like this. I'm almost
thinking I should retry the insert immediately if it fails - this is the
only reason it ever fails. But I'm sure mentioning that will get me flamed
so I'm open to ideas from the experts.
Any advice is appreciated.
Jason
I can't agree with your reasoning, as it should be possible on a multi-user
system. The following pages from SQL Server 2000 Books Online, should get
you started in the right direction.
Deadlocking
Handling Deadlocks
Minimizing Deadlocks
Detecting and Ending Deadlocks
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jason MacKenzie" <jmackenzie_nospam@.formet.com> wrote in message
news:uEuOcIeMEHA.3556@.TK2MSFTNGP09.phx.gbl...
I have general question about deadlocks. I get the occasional deadlock when
someone attempts to update a row in the data. I think this might be caused
because the update is happening at the exact same time the row in question
is being updated by a trigger on another table - that I have no control
over.
I'm not sure in general terms how to resolve an issue like this. I'm almost
thinking I should retry the insert immediately if it fails - this is the
only reason it ever fails. But I'm sure mentioning that will get me flamed
so I'm open to ideas from the experts.
Any advice is appreciated.
Jason
|||I have no reasoning. But I appreciate your response and will take a look at
those articles.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23nACcUeMEHA.3400@.TK2MSFTNGP09.phx.gbl...
> I can't agree with your reasoning, as it should be possible on a
multi-user
> system. The following pages from SQL Server 2000 Books Online, should get
> you started in the right direction.
> Deadlocking
> Handling Deadlocks
> Minimizing Deadlocks
> Detecting and Ending Deadlocks
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Jason MacKenzie" <jmackenzie_nospam@.formet.com> wrote in message
> news:uEuOcIeMEHA.3556@.TK2MSFTNGP09.phx.gbl...
> I have general question about deadlocks. I get the occasional deadlock
when
> someone attempts to update a row in the data. I think this might be caused
> because the update is happening at the exact same time the row in question
> is being updated by a trigger on another table - that I have no control
> over.
> I'm not sure in general terms how to resolve an issue like this. I'm
almost
> thinking I should retry the insert immediately if it fails - this is the
> only reason it ever fails. But I'm sure mentioning that will get me flamed
> so I'm open to ideas from the experts.
> Any advice is appreciated.
> Jason
>
>

Deadlock question

I have general question about deadlocks. I get the occasional deadlock when
someone attempts to update a row in the data. I think this might be caused
because the update is happening at the exact same time the row in question
is being updated by a trigger on another table - that I have no control
over.
I'm not sure in general terms how to resolve an issue like this. I'm almost
thinking I should retry the insert immediately if it fails - this is the
only reason it ever fails. But I'm sure mentioning that will get me flamed
so I'm open to ideas from the experts.
Any advice is appreciated.
JasonI can't agree with your reasoning, as it should be possible on a multi-user
system. The following pages from SQL Server 2000 Books Online, should get
you started in the right direction.
Deadlocking
Handling Deadlocks
Minimizing Deadlocks
Detecting and Ending Deadlocks
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jason MacKenzie" <jmackenzie_nospam@.formet.com> wrote in message
news:uEuOcIeMEHA.3556@.TK2MSFTNGP09.phx.gbl...
I have general question about deadlocks. I get the occasional deadlock when
someone attempts to update a row in the data. I think this might be caused
because the update is happening at the exact same time the row in question
is being updated by a trigger on another table - that I have no control
over.
I'm not sure in general terms how to resolve an issue like this. I'm almost
thinking I should retry the insert immediately if it fails - this is the
only reason it ever fails. But I'm sure mentioning that will get me flamed
so I'm open to ideas from the experts.
Any advice is appreciated.
Jason|||I have no reasoning. But I appreciate your response and will take a look at
those articles.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23nACcUeMEHA.3400@.TK2MSFTNGP09.phx.gbl...
> I can't agree with your reasoning, as it should be possible on a
multi-user
> system. The following pages from SQL Server 2000 Books Online, should get
> you started in the right direction.
> Deadlocking
> Handling Deadlocks
> Minimizing Deadlocks
> Detecting and Ending Deadlocks
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Jason MacKenzie" <jmackenzie_nospam@.formet.com> wrote in message
> news:uEuOcIeMEHA.3556@.TK2MSFTNGP09.phx.gbl...
> I have general question about deadlocks. I get the occasional deadlock
when
> someone attempts to update a row in the data. I think this might be caused
> because the update is happening at the exact same time the row in question
> is being updated by a trigger on another table - that I have no control
> over.
> I'm not sure in general terms how to resolve an issue like this. I'm
almost
> thinking I should retry the insert immediately if it fails - this is the
> only reason it ever fails. But I'm sure mentioning that will get me flamed
> so I'm open to ideas from the experts.
> Any advice is appreciated.
> Jason
>
>

Deadlock question

I have general question about deadlocks. I get the occasional deadlock when
someone attempts to update a row in the data. I think this might be caused
because the update is happening at the exact same time the row in question
is being updated by a trigger on another table - that I have no control
over.
I'm not sure in general terms how to resolve an issue like this. I'm almost
thinking I should retry the insert immediately if it fails - this is the
only reason it ever fails. But I'm sure mentioning that will get me flamed
so I'm open to ideas from the experts.
Any advice is appreciated.
JasonI can't agree with your reasoning, as it should be possible on a multi-user
system. The following pages from SQL Server 2000 Books Online, should get
you started in the right direction.
Deadlocking
Handling Deadlocks
Minimizing Deadlocks
Detecting and Ending Deadlocks
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jason MacKenzie" <jmackenzie_nospam@.formet.com> wrote in message
news:uEuOcIeMEHA.3556@.TK2MSFTNGP09.phx.gbl...
I have general question about deadlocks. I get the occasional deadlock when
someone attempts to update a row in the data. I think this might be caused
because the update is happening at the exact same time the row in question
is being updated by a trigger on another table - that I have no control
over.
I'm not sure in general terms how to resolve an issue like this. I'm almost
thinking I should retry the insert immediately if it fails - this is the
only reason it ever fails. But I'm sure mentioning that will get me flamed
so I'm open to ideas from the experts.
Any advice is appreciated.
Jason|||I have no reasoning. But I appreciate your response and will take a look at
those articles.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23nACcUeMEHA.3400@.TK2MSFTNGP09.phx.gbl...
> I can't agree with your reasoning, as it should be possible on a
multi-user
> system. The following pages from SQL Server 2000 Books Online, should get
> you started in the right direction.
> Deadlocking
> Handling Deadlocks
> Minimizing Deadlocks
> Detecting and Ending Deadlocks
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Jason MacKenzie" <jmackenzie_nospam@.formet.com> wrote in message
> news:uEuOcIeMEHA.3556@.TK2MSFTNGP09.phx.gbl...
> I have general question about deadlocks. I get the occasional deadlock
when
> someone attempts to update a row in the data. I think this might be caused
> because the update is happening at the exact same time the row in question
> is being updated by a trigger on another table - that I have no control
> over.
> I'm not sure in general terms how to resolve an issue like this. I'm
almost
> thinking I should retry the insert immediately if it fails - this is the
> only reason it ever fails. But I'm sure mentioning that will get me flamed
> so I'm open to ideas from the experts.
> Any advice is appreciated.
> Jason
>
>sql

Deadlock Problem

What's the best way to avoid a deadlock in the following situation:
One query deletes a row from a table i.e.,
delete MyTable
where Id = 1
Another query wants to update the same row in 'MyTable' at the same
time that the first query wants to delete the record, i.e.,
update MyTable
set SomeField = 1
where id = 1
The first query above is called from one process, the second query
above is called from a different process.
If the update fails to update the row because the row has been
deleted this is ok. So I want the delete to take priority.
The 2 processes are processing up to 30 transactions per second.
How can I guarantee that I won't get a deadlock?
Hi,
See the command SET DEADLOCK_PRIORITY in books online.
Thanks
Hari
MCDBA
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.c om...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock?
|||If there is only one table being updated it should not deadlock, it will
only block. Both the update and delete will lock the row while it is
updating or deleting the row and will only temporarily block the other. If
the update is being blocked by the delete it will simply not find the row to
delete once the delete is finished. As long as you don't update 2 or more
tables in reverse order you will most likely only block and not deadlock.
Andrew J. Kelly SQL MVP
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.c om...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock?
sql

Deadlock Problem

What's the best way to avoid a deadlock in the following situation:
One query deletes a row from a table i.e.,
delete MyTable
where Id = 1
Another query wants to update the same row in 'MyTable' at the same
time that the first query wants to delete the record, i.e.,
update MyTable
set SomeField = 1
where id = 1
The first query above is called from one process, the second query
above is called from a different process.
If the update fails to update the row because the row has been
deleted this is ok. So I want the delete to take priority.
The 2 processes are processing up to 30 transactions per second.
How can I guarantee that I won't get a deadlock'Hi,
See the command SET DEADLOCK_PRIORITY in books online.
Thanks
Hari
MCDBA
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.com...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock'|||If there is only one table being updated it should not deadlock, it will
only block. Both the update and delete will lock the row while it is
updating or deleting the row and will only temporarily block the other. If
the update is being blocked by the delete it will simply not find the row to
delete once the delete is finished. As long as you don't update 2 or more
tables in reverse order you will most likely only block and not deadlock.
--
Andrew J. Kelly SQL MVP
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.com...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock'sql

deadlock on parent-child relationship

I have a program that inserts a row to a parent table and before it
commits then calls another program to insert rows to the child table.
This is causing a deadlock. When I looked at it, the
first program has an X lock on the primary key of the parent table and
the second program is trying to get a share lock on the index of the
parent table ?
Why is this happening ? How can I avoid it ?
Thanks
RogerMake sure the order of the tables in the from clause is the same in both
queries and consider using the UPDLOCK table hint.
Read more here:
http://msdn.microsoft.com/library/d... />
a_8i93.asp
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
ML
http://milambda.blogspot.com/|||You can't avoid it unless both updates occur on the same connection, or
unless you bind the second connection to the first. Look up sp_bindsession
in BOL. Exclusive locks are held on an inserted row until it is committed,
so no other transaction can see the row until it's committed (unless you use
WITH(NOLOCK), which should be avoided whenever possible).
I prefer to dump an update that contains related information into temp
tables so that they can be committed using set-based operations within a
stored procedure, but that can have performance and scalability implications
depending on whether tempdb is on it's own disk subsystem and on whether
there's enough memory so that the contents of the temp tables aren't
migrated out to disk. Set-based operations minimize lock duration, index
maintenance and transaction logging, so it's a trade-off. Without testing,
it cannot be determined which method provides the best performance and
scalability for a particular update scenario. However, I prefer to keep
transaction processing within stored procedures because I've found that
troubleshooting and repairing blocking and deadlock problems is less
expensive if all transactions are contained in procedures. It's a lot
easier to add a SELECT WITH(UPDLOCK) to a stored procedure than to alter,
recompile, and redeploy a client program.
"Roger" <wonderinguys@.gmail.com> wrote in message
news:1138809010.484456.142920@.z14g2000cwz.googlegroups.com...
>I have a program that inserts a row to a parent table and before it
> commits then calls another program to insert rows to the child table.
> This is causing a deadlock. When I looked at it, the
> first program has an X lock on the primary key of the parent table and
> the second program is trying to get a share lock on the index of the
> parent table ?
> Why is this happening ? How can I avoid it ?
> Thanks
> Roger
>|||the program that inserts the child table is in a new spid...a different
one from the parent program. Why is that ? i am from DB2 running on
mainframe where this never happens. So need some help with this.|||On 2 Feb 2006 12:21:33 -0800, Roger wrote:

>the program that inserts the child table is in a new spid...a different
>one from the parent program. Why is that ? i am from DB2 running on
>mainframe where this never happens. So need some help with this.
Hi Roger,
That's the cause of your deadlock, then.
This surely doesn't happen automatically. In fact, you have to work
pretty hard to get a subprocedure to run in a different spid in SQL
Server. (Doing it from the client is easier, but still takes some
effort).
Can you post (snippets of) your code?
Hugo Kornelis, SQL Server MVP

DBNull Error

Am getting errors on this syntax:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.myColumn = DBNull.Value

Value of type 'System.DBNull' cannot be converted to type 'String'

Any ideas? Just want to set the myColumn to NULL.

Thanks

Nevermind.

Row.myColumn = Nothing

DBNULL

I found a bug, if a textbox of a record row has dbnull value, all of following rows will hide the textbox, even they have non dbnull value. My current workaround is use ISNULL(col,'') AS col in query.

Is this by design or a bug?

If this is reproducible, it is a bug. I have not seen it, though. Which version and build of Reporting Services are you using?|||I am using the reportviewer in vs2005 beta2 to view local report.|||

We are not seeing this in current builds. I believe that it is resolved.