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

Deadlock on SQL SELECT statement

I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.

Deadlock between Distribution Agent and Distribution Agent Cleanup

I am experiencing this problem. Deadlock of these two M$ stored
procedures :
sp_MSget_repl_commands (Executed by the Distribution Agent --pull
subscriber ) and
sp_MSdistribution_cleanup (Executed by the Distribution Agent Cleanup
job)
the offending queries are :

>From sp_MSdistribution_cleanup:
DELETE MSrepl_commands WITH (PAGLOCK) where
publisher_database_id = @.publisher_database_id and
xact_seqno <= @.max_xact_seqno

>From sp_MSget_repl_commands:
select @.max_xact_seqno = max(xact_seqno) from MSrepl_commands
(READPAST)
where
publisher_database_id = @.publisher_database_id and
command_id = 1 and
type <> -2147483611
I searched this and other groups and no convincing answer was posted.
Is there anyone experiencing this problem ? if so what did you do to
"resolve" it (not to decrease its frequency)
Thanks in Advance.
-Noel
Sr. DBA
I've seen this a lot, since they are both hitting the same repl table at the
same time, but I've never seen it fail/deadlock for extended periods of
time. If your agent failing, then succeeding?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
<zerg2k@.yahoo.com> wrote in message
news:1166732530.463614.305580@.i12g2000cwa.googlegr oups.com...
>I am experiencing this problem. Deadlock of these two M$ stored
> procedures :
> sp_MSget_repl_commands (Executed by the Distribution Agent --pull
> subscriber ) and
> sp_MSdistribution_cleanup (Executed by the Distribution Agent Cleanup
> job)
> the offending queries are :
>
> DELETE MSrepl_commands WITH (PAGLOCK) where
> publisher_database_id = @.publisher_database_id and
> xact_seqno <= @.max_xact_seqno
> select @.max_xact_seqno = max(xact_seqno) from MSrepl_commands
> (READPAST)
> where
> publisher_database_id = @.publisher_database_id and
> command_id = 1 and
> type <> -2147483611
> I searched this and other groups and no convincing answer was posted.
> Is there anyone experiencing this problem ? if so what did you do to
> "resolve" it (not to decrease its frequency)
> Thanks in Advance.
> -Noel
> Sr. DBA
>
|||Kevin,
This is not 'extreme' for me but the fact that those deadlocks are
happening makes me nervous in case the activity expands for more
extended periods. This is something that I would like to avoid if at
all possible.
and you are correct it fails, then retrys and if the 'high' activity
period some how subsides a bit it succeeds. I thought those lock hints
were pretty safe to avoid such situations but apparently I was wrong.
Thanks for the feedback.
-Noel
Sr DBA
Kevin3NF wrote:[vbcol=seagreen]
> I've seen this a lot, since they are both hitting the same repl table at the
> same time, but I've never seen it fail/deadlock for extended periods of
> time. If your agent failing, then succeeding?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> <zerg2k@.yahoo.com> wrote in message
> news:1166732530.463614.305580@.i12g2000cwa.googlegr oups.com...

DBO Query

Is there a query that can be executed to check if the currently logged in
user has dbo access to the current database?
I need to know this prior to adding tables.
Thanks in advance.
Hi Isaac,
Yes, use is_member() like in
if is_member('db_owner') = 1
-- do something
You may also want to take a look at is_srvrolemember.
Hope this helps,
Ben Nevarez
"Isaac Alexander" wrote:

> Is there a query that can be executed to check if the currently logged in
> user has dbo access to the current database?
> I need to know this prior to adding tables.
> Thanks in advance.
>
>
|||To get the logged in user a member of the fixed database role, look up
IS_MEMBER() function. If you want to know if a particular user belongs to a
particular database role, try the check:
IF EXISTS ( SELECT *
FROM sysmembers s1
JOIN sysusers s2 ON s2.uid = s1.memberuid
JOIN sysusers s3 ON s3.uid = s1.groupuid AND s3.issqlrole = 1
WHERE s3.name = @.role AND s2.name = @.user )
Anith
|||> if is_member('db_owner') = 1
> -- do something
That worked perfectly. Thanks.

DBO Query

Is there a query that can be executed to check if the currently logged in
user has dbo access to the current database?
I need to know this prior to adding tables.
Thanks in advance.Hi Isaac,
Yes, use is_member() like in
if is_member('db_owner') = 1
-- do something
You may also want to take a look at is_srvrolemember.
Hope this helps,
Ben Nevarez
"Isaac Alexander" wrote:
> Is there a query that can be executed to check if the currently logged in
> user has dbo access to the current database?
> I need to know this prior to adding tables.
> Thanks in advance.
>
>|||To get the logged in user a member of the fixed database role, look up
IS_MEMBER() function. If you want to know if a particular user belongs to a
particular database role, try the check:
IF EXISTS ( SELECT *
FROM sysmembers s1
JOIN sysusers s2 ON s2.uid = s1.memberuid
JOIN sysusers s3 ON s3.uid = s1.groupuid AND s3.issqlrole = 1
WHERE s3.name = @.role AND s2.name = @.user )
--
Anith|||> if is_member('db_owner') = 1
> -- do something
That worked perfectly. Thanks.