If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.
> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.
|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID =
> 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>
Showing posts with label common. Show all posts
Showing posts with label common. Show all posts
Tuesday, March 27, 2012
Deadlocks & Transaction Isolation Level
If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID => 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID => 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>
Deadlocks & Transaction Isolation Level
If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID =
> 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID =
> 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>
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
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
>
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
>
Subscribe to:
Posts (Atom)