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
Showing posts with label priority. Show all posts
Showing posts with label priority. Show all posts
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,
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
Thursday, March 8, 2012
DEADLOCK
Is there any possibility to force a deadlock priority
higher than normal? what I want is to keep my process
running and not to be chosen as deadlock victim, no matter
what. Put different, I want to know if there is a way to
overwrite the default deadlock-handling method.
Set DEADLOCK_PRIORITY is not to clearly explained in Books
Online from this point of view.
thank youGabriela,
Sorry, there is not. (Have you emailed sqlwish?)
DEADLOCK_PRIORITY is used to make a process _more_ likely to be the victim.
You can use that on other processes to try to preserve your process, but it
is (IMHO) a bit of a Red Queen's Race to try to make every other transaction
have a lower priority than yours.
When this has been a serious problem, we have had to write the code in a
process to be restartable. E.g. Keep state information in a table, in case
of a being victimized by a deadlock, the client restarts the process which
reads the state and skips to that point to continue processing.
Russell Fields
"Gabriela" <gnanau@.cstonecanada.com> wrote in message
news:0d3201c394b9$76e81e10$a301280a@.phx.gbl...
> Is there any possibility to force a deadlock priority
> higher than normal? what I want is to keep my process
> running and not to be chosen as deadlock victim, no matter
> what. Put different, I want to know if there is a way to
> overwrite the default deadlock-handling method.
> Set DEADLOCK_PRIORITY is not to clearly explained in Books
> Online from this point of view.
> thank you|||I was affraid that this would be the answer.Thank you
anyway
>--Original Message--
>Gabriela,
>Sorry, there is not. (Have you emailed sqlwish?)
>DEADLOCK_PRIORITY is used to make a process _more_ likely
to be the victim.
>You can use that on other processes to try to preserve
your process, but it
>is (IMHO) a bit of a Red Queen's Race to try to make
every other transaction
>have a lower priority than yours.
>When this has been a serious problem, we have had to
write the code in a
>process to be restartable. E.g. Keep state information in
a table, in case
>of a being victimized by a deadlock, the client restarts
the process which
>reads the state and skips to that point to continue
processing.
>Russell Fields
>"Gabriela" <gnanau@.cstonecanada.com> wrote in message
>news:0d3201c394b9$76e81e10$a301280a@.phx.gbl...
>> Is there any possibility to force a deadlock priority
>> higher than normal? what I want is to keep my process
>> running and not to be chosen as deadlock victim, no
matter
>> what. Put different, I want to know if there is a way to
>> overwrite the default deadlock-handling method.
>> Set DEADLOCK_PRIORITY is not to clearly explained in
Books
>> Online from this point of view.
>> thank you
>
>.
>
higher than normal? what I want is to keep my process
running and not to be chosen as deadlock victim, no matter
what. Put different, I want to know if there is a way to
overwrite the default deadlock-handling method.
Set DEADLOCK_PRIORITY is not to clearly explained in Books
Online from this point of view.
thank youGabriela,
Sorry, there is not. (Have you emailed sqlwish?)
DEADLOCK_PRIORITY is used to make a process _more_ likely to be the victim.
You can use that on other processes to try to preserve your process, but it
is (IMHO) a bit of a Red Queen's Race to try to make every other transaction
have a lower priority than yours.
When this has been a serious problem, we have had to write the code in a
process to be restartable. E.g. Keep state information in a table, in case
of a being victimized by a deadlock, the client restarts the process which
reads the state and skips to that point to continue processing.
Russell Fields
"Gabriela" <gnanau@.cstonecanada.com> wrote in message
news:0d3201c394b9$76e81e10$a301280a@.phx.gbl...
> Is there any possibility to force a deadlock priority
> higher than normal? what I want is to keep my process
> running and not to be chosen as deadlock victim, no matter
> what. Put different, I want to know if there is a way to
> overwrite the default deadlock-handling method.
> Set DEADLOCK_PRIORITY is not to clearly explained in Books
> Online from this point of view.
> thank you|||I was affraid that this would be the answer.Thank you
anyway
>--Original Message--
>Gabriela,
>Sorry, there is not. (Have you emailed sqlwish?)
>DEADLOCK_PRIORITY is used to make a process _more_ likely
to be the victim.
>You can use that on other processes to try to preserve
your process, but it
>is (IMHO) a bit of a Red Queen's Race to try to make
every other transaction
>have a lower priority than yours.
>When this has been a serious problem, we have had to
write the code in a
>process to be restartable. E.g. Keep state information in
a table, in case
>of a being victimized by a deadlock, the client restarts
the process which
>reads the state and skips to that point to continue
processing.
>Russell Fields
>"Gabriela" <gnanau@.cstonecanada.com> wrote in message
>news:0d3201c394b9$76e81e10$a301280a@.phx.gbl...
>> Is there any possibility to force a deadlock priority
>> higher than normal? what I want is to keep my process
>> running and not to be chosen as deadlock victim, no
matter
>> what. Put different, I want to know if there is a way to
>> overwrite the default deadlock-handling method.
>> Set DEADLOCK_PRIORITY is not to clearly explained in
Books
>> Online from this point of view.
>> thank you
>
>.
>
Subscribe to:
Posts (Atom)