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 correctly. Show all posts
Showing posts with label correctly. 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.
>
>
Thursday, March 22, 2012
Deadlock priority
Hi,
Does anybody know how I can check if the statement SET
DEADLOCK_PRIORITY LOW has been correctly executed?
I have 2 sessions and one of them gets always the deadlock and I want
to get the deadlock on the other one. So on the other one I execute
the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
one still gets the deadlock.
Thanks,
EsterEster
It should work as it is described in the BOL
But , you might want ot check a design of the app in order to prevent
DEADLOCKs happening.
http://www.sql-server-performance.com/deadlocks.asp
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Ester,
SQL Server will kill the transaction which costs less to terminate. In other
words, the deadlock_priority option does not guarantee that your second
session always gets terminated, it only lowers the cost. You should use
careful database and procedure design together with transaction isolation
levels / locking hints instead, to avoid getting the deadlock situation at
all.
Jon Jahren
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Hi,
thank your input.
After further testing I learned I described the situation too short.
First I must said unfortunately that I cannot change the functions
that are causing the deadlock. I understood that that is the normal
way, but I must live with this situation.
In the application I use I cannot execute sql statements directly so I
put the SET DEADLOCK_PRIORITY LOW in an update trigger of a table.
After your input I tested this directly in the query analyser
and came to the following conclusions:
-when I execute the statement SET DEADLOCK_PRIORITY LOW directly in
the second sessions, this session gets the deadlock as is expected
-but when I call the statement through the update trigger (in an
seperated batch), the statement has no effect.
Can you give me information if my findings are correct and maybe about
another way to get what I want?
Thanks,
Ester
e_gro@.hotmail.com (Ester) wrote in message news:<327f52e1.0411150147.90f5eae@.posting.google.
com>...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
Does anybody know how I can check if the statement SET
DEADLOCK_PRIORITY LOW has been correctly executed?
I have 2 sessions and one of them gets always the deadlock and I want
to get the deadlock on the other one. So on the other one I execute
the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
one still gets the deadlock.
Thanks,
EsterEster
It should work as it is described in the BOL
But , you might want ot check a design of the app in order to prevent
DEADLOCKs happening.
http://www.sql-server-performance.com/deadlocks.asp
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Ester,
SQL Server will kill the transaction which costs less to terminate. In other
words, the deadlock_priority option does not guarantee that your second
session always gets terminated, it only lowers the cost. You should use
careful database and procedure design together with transaction isolation
levels / locking hints instead, to avoid getting the deadlock situation at
all.
Jon Jahren
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Hi,
thank your input.
After further testing I learned I described the situation too short.
First I must said unfortunately that I cannot change the functions
that are causing the deadlock. I understood that that is the normal
way, but I must live with this situation.
In the application I use I cannot execute sql statements directly so I
put the SET DEADLOCK_PRIORITY LOW in an update trigger of a table.
After your input I tested this directly in the query analyser
and came to the following conclusions:
-when I execute the statement SET DEADLOCK_PRIORITY LOW directly in
the second sessions, this session gets the deadlock as is expected
-but when I call the statement through the update trigger (in an
seperated batch), the statement has no effect.
Can you give me information if my findings are correct and maybe about
another way to get what I want?
Thanks,
Ester
e_gro@.hotmail.com (Ester) wrote in message news:<327f52e1.0411150147.90f5eae@.posting.google.
com>...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
Deadlock priority
Hi,
Does anybody know how I can check if the statement SET
DEADLOCK_PRIORITY LOW has been correctly executed?
I have 2 sessions and one of them gets always the deadlock and I want
to get the deadlock on the other one. So on the other one I execute
the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
one still gets the deadlock.
Thanks,
Ester
Ester
It should work as it is described in the BOL
But , you might want ot check a design of the app in order to prevent
DEADLOCKs happening.
http://www.sql-server-performance.com/deadlocks.asp
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.co m...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
|||Ester,
SQL Server will kill the transaction which costs less to terminate. In other
words, the deadlock_priority option does not guarantee that your second
session always gets terminated, it only lowers the cost. You should use
careful database and procedure design together with transaction isolation
levels / locking hints instead, to avoid getting the deadlock situation at
all.
Jon Jahren
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.co m...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
|||Hi,
thank your input.
After further testing I learned I described the situation too short.
First I must said unfortunately that I cannot change the functions
that are causing the deadlock. I understood that that is the normal
way, but I must live with this situation.
In the application I use I cannot execute sql statements directly so I
put the SET DEADLOCK_PRIORITY LOW in an update trigger of a table.
After your input I tested this directly in the query analyser
and came to the following conclusions:
-when I execute the statement SET DEADLOCK_PRIORITY LOW directly in
the second sessions, this session gets the deadlock as is expected
-but when I call the statement through the update trigger (in an
seperated batch), the statement has no effect.
Can you give me information if my findings are correct and maybe about
another way to get what I want?
Thanks,
Ester
e_gro@.hotmail.com (Ester) wrote in message news:<327f52e1.0411150147.90f5eae@.posting.google.c om>...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
Does anybody know how I can check if the statement SET
DEADLOCK_PRIORITY LOW has been correctly executed?
I have 2 sessions and one of them gets always the deadlock and I want
to get the deadlock on the other one. So on the other one I execute
the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
one still gets the deadlock.
Thanks,
Ester
Ester
It should work as it is described in the BOL
But , you might want ot check a design of the app in order to prevent
DEADLOCKs happening.
http://www.sql-server-performance.com/deadlocks.asp
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.co m...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
|||Ester,
SQL Server will kill the transaction which costs less to terminate. In other
words, the deadlock_priority option does not guarantee that your second
session always gets terminated, it only lowers the cost. You should use
careful database and procedure design together with transaction isolation
levels / locking hints instead, to avoid getting the deadlock situation at
all.
Jon Jahren
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.co m...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
|||Hi,
thank your input.
After further testing I learned I described the situation too short.
First I must said unfortunately that I cannot change the functions
that are causing the deadlock. I understood that that is the normal
way, but I must live with this situation.
In the application I use I cannot execute sql statements directly so I
put the SET DEADLOCK_PRIORITY LOW in an update trigger of a table.
After your input I tested this directly in the query analyser
and came to the following conclusions:
-when I execute the statement SET DEADLOCK_PRIORITY LOW directly in
the second sessions, this session gets the deadlock as is expected
-but when I call the statement through the update trigger (in an
seperated batch), the statement has no effect.
Can you give me information if my findings are correct and maybe about
another way to get what I want?
Thanks,
Ester
e_gro@.hotmail.com (Ester) wrote in message news:<327f52e1.0411150147.90f5eae@.posting.google.c om>...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester
Deadlock priority
Hi,
Does anybody know how I can check if the statement SET
DEADLOCK_PRIORITY LOW has been correctly executed?
I have 2 sessions and one of them gets always the deadlock and I want
to get the deadlock on the other one. So on the other one I execute
the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
one still gets the deadlock.
Thanks,
EsterEster
It should work as it is described in the BOL
But , you might want ot check a design of the app in order to prevent
DEADLOCKs happening.
http://www.sql-server-performance.com/deadlocks.asp
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Ester,
SQL Server will kill the transaction which costs less to terminate. In other
words, the deadlock_priority option does not guarantee that your second
session always gets terminated, it only lowers the cost. You should use
careful database and procedure design together with transaction isolation
levels / locking hints instead, to avoid getting the deadlock situation at
all.
Jon Jahren
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Hi,
thank your input.
After further testing I learned I described the situation too short.
First I must said unfortunately that I cannot change the functions
that are causing the deadlock. I understood that that is the normal
way, but I must live with this situation.
In the application I use I cannot execute sql statements directly so I
put the SET DEADLOCK_PRIORITY LOW in an update trigger of a table.
After your input I tested this directly in the query analyser
and came to the following conclusions:
-when I execute the statement SET DEADLOCK_PRIORITY LOW directly in
the second sessions, this session gets the deadlock as is expected
-but when I call the statement through the update trigger (in an
seperated batch), the statement has no effect.
Can you give me information if my findings are correct and maybe about
another way to get what I want?
Thanks,
Ester
e_gro@.hotmail.com (Ester) wrote in message news:<327f52e1.0411150147.90f5eae@.posting.google.com>...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Estersql
Does anybody know how I can check if the statement SET
DEADLOCK_PRIORITY LOW has been correctly executed?
I have 2 sessions and one of them gets always the deadlock and I want
to get the deadlock on the other one. So on the other one I execute
the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
one still gets the deadlock.
Thanks,
EsterEster
It should work as it is described in the BOL
But , you might want ot check a design of the app in order to prevent
DEADLOCKs happening.
http://www.sql-server-performance.com/deadlocks.asp
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Ester,
SQL Server will kill the transaction which costs less to terminate. In other
words, the deadlock_priority option does not guarantee that your second
session always gets terminated, it only lowers the cost. You should use
careful database and procedure design together with transaction isolation
levels / locking hints instead, to avoid getting the deadlock situation at
all.
Jon Jahren
"Ester" <e_gro@.hotmail.com> wrote in message
news:327f52e1.0411150147.90f5eae@.posting.google.com...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Ester|||Hi,
thank your input.
After further testing I learned I described the situation too short.
First I must said unfortunately that I cannot change the functions
that are causing the deadlock. I understood that that is the normal
way, but I must live with this situation.
In the application I use I cannot execute sql statements directly so I
put the SET DEADLOCK_PRIORITY LOW in an update trigger of a table.
After your input I tested this directly in the query analyser
and came to the following conclusions:
-when I execute the statement SET DEADLOCK_PRIORITY LOW directly in
the second sessions, this session gets the deadlock as is expected
-but when I call the statement through the update trigger (in an
seperated batch), the statement has no effect.
Can you give me information if my findings are correct and maybe about
another way to get what I want?
Thanks,
Ester
e_gro@.hotmail.com (Ester) wrote in message news:<327f52e1.0411150147.90f5eae@.posting.google.com>...
> Hi,
> Does anybody know how I can check if the statement SET
> DEADLOCK_PRIORITY LOW has been correctly executed?
> I have 2 sessions and one of them gets always the deadlock and I want
> to get the deadlock on the other one. So on the other one I execute
> the statement SET DEADLOCK_PRIORITY LOW. But unfortunately, the first
> one still gets the deadlock.
> Thanks,
> Estersql
Subscribe to:
Posts (Atom)