Any ideas on resolving deadlocks on a sql-server 2000 windows 2000
platform?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
http://support.microsoft.com/?id=224453
118552 INFO: Handling Deadlock Conditions
http://support.microsoft.com/?id=118552
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Showing posts with label sql-server. Show all posts
Showing posts with label sql-server. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
deadlock between select (shared) and update (intent exclusive)
I regularly have deadlocks on my sql-server 2000. Using the 1204 trace
I got the following info about the problem:
Wait-for graph
Node:1
PAG: 7:1:251381 CleanCnt:2 Mode: S Flags: 0x2
Grant List 0::
Owner:0x2c959e00 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61
ECID:3
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:72 ECID:0 Ec
0x50871568)
Value:0x76375e60 Cost0/5580)
Node:2
PAG: 7:1:230822 CleanCnt:2 Mode: IX Flags: 0x2
Grant List 3::
Owner:0x4bba49e0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:72
ECID:0
SPID: 72 ECID: 0 Statement Type: INSERT Line #: 1
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
As I understand it, one statement owns an IX-lock and requests another
one while another statement owns a shared-lock and requests another
one. I know, I should always access tables in the same order but it's
too late for this now.
How can I tell the select statement to read the last commited data and
not to lock anything? IMHO we did not give any lock-hints with our
statements so the default lock levels should be used. Does it make
sense that a select blocks an update?Update: it is not a select and an UPDATE but a select count and an
insert.|||
> As I understand it, one statement owns an IX-lock and requests another
> one while another statement owns a shared-lock and requests another
> one. I know, I should always access tables in the same order but it's
> too late for this now.
> How can I tell the select statement to read the last commited data and
> not to lock anything? IMHO we did not give any lock-hints with our
> statements so the default lock levels should be used. Does it make
> sense that a select blocks an update?
>
I do not pretend to understand your locking situation.
And although I thought in the past that a select should Not be partner
in a deadlock. This proved to be wrong.
My situation.
Update transaction (standard isolation), two updates on one single row.
The select was a very simple select which resulted in a single row of a
single table.
The combination could result in a deadlock.
The probable cause of 'my' problem.
Both updates used different where clauses, which resulted in the same row,
but
resulted in different locking situations.
In this situation you can NOT tel to read the last commited data, because
that is
locked at the moment. In SQL-server 2005 you can opt for snapshot isolation,
where the last commited data is read. So with snapshot isolation reads do
not block
write and writes do not block reads.
Be carefull with snapshot isolation because this does not implement
serializability.
Good luck with your situation,
If you have more information please post it here,
If you have more questions please post it here.
ben brugman|||mhuhn.de@.gmail.com,
This feature has been implemented in SQL Server 2005 (Snapshot Isolation).
If you are using 2000 and do not want to change the order in which you
access your tables, consider using a table_hint in your "select" statement,
specifically ROWLOCK based on the info you posted (the lock seems to be at
the page level). See BOL for more info.
AMB
"mhuhn.de@.gmail.com" wrote:
> Update: it is not a select and an UPDATE but a select count and an
> insert.
>|||Thanks for answering. Anyway, 2005 is not an option because our
solution is already used from lots of customers. Do you think a ROWLOCK
makes sense if I do a select count? If the where-clause in the select
count includes the updated row, I'll have the same problem, right?
Furthermore, it will slow down my selects!?|||As I wrote in my other mail :
I do not pretend to understand your locking situation.
But I doubt very much that a ROWLOCK in the select will
solve the problem. The select (without a rowlock) is allready
waiting for another process to finish, this waiting can (I think)
not be solved by using a ROWLOCK, the rowlock will
(probably) prevent the other process on locking on the read
process.
A (row)lock in the update might claim enough resources that
the select is not capable of applying a lock which can stop the
update. So the update can finish after which the select can finish.
ben brugman
<mhuhn.de@.gmail.com> wrote in message
news:1147960900.879319.169740@.j55g2000cwa.googlegroups.com...
> Thanks for answering. Anyway, 2005 is not an option because our
> solution is already used from lots of customers. Do you think a ROWLOCK
> makes sense if I do a select count? If the where-clause in the select
> count includes the updated row, I'll have the same problem, right?
> Furthermore, it will slow down my selects!?
>
I got the following info about the problem:
Wait-for graph
Node:1
PAG: 7:1:251381 CleanCnt:2 Mode: S Flags: 0x2
Grant List 0::
Owner:0x2c959e00 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61
ECID:3
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:72 ECID:0 Ec
0x50871568)Value:0x76375e60 Cost
0/5580)Node:2
PAG: 7:1:230822 CleanCnt:2 Mode: IX Flags: 0x2
Grant List 3::
Owner:0x4bba49e0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:72
ECID:0
SPID: 72 ECID: 0 Statement Type: INSERT Line #: 1
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec
0x2D8EA0C0)Value:0x75df1780 Cost
0/0)Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec
0x2D8EA0C0)Value:0x75df1780 Cost
0/0)As I understand it, one statement owns an IX-lock and requests another
one while another statement owns a shared-lock and requests another
one. I know, I should always access tables in the same order but it's
too late for this now.
How can I tell the select statement to read the last commited data and
not to lock anything? IMHO we did not give any lock-hints with our
statements so the default lock levels should be used. Does it make
sense that a select blocks an update?Update: it is not a select and an UPDATE but a select count and an
insert.|||
> As I understand it, one statement owns an IX-lock and requests another
> one while another statement owns a shared-lock and requests another
> one. I know, I should always access tables in the same order but it's
> too late for this now.
> How can I tell the select statement to read the last commited data and
> not to lock anything? IMHO we did not give any lock-hints with our
> statements so the default lock levels should be used. Does it make
> sense that a select blocks an update?
>
I do not pretend to understand your locking situation.
And although I thought in the past that a select should Not be partner
in a deadlock. This proved to be wrong.
My situation.
Update transaction (standard isolation), two updates on one single row.
The select was a very simple select which resulted in a single row of a
single table.
The combination could result in a deadlock.
The probable cause of 'my' problem.
Both updates used different where clauses, which resulted in the same row,
but
resulted in different locking situations.
In this situation you can NOT tel to read the last commited data, because
that is
locked at the moment. In SQL-server 2005 you can opt for snapshot isolation,
where the last commited data is read. So with snapshot isolation reads do
not block
write and writes do not block reads.
Be carefull with snapshot isolation because this does not implement
serializability.
Good luck with your situation,
If you have more information please post it here,
If you have more questions please post it here.
ben brugman|||mhuhn.de@.gmail.com,
This feature has been implemented in SQL Server 2005 (Snapshot Isolation).
If you are using 2000 and do not want to change the order in which you
access your tables, consider using a table_hint in your "select" statement,
specifically ROWLOCK based on the info you posted (the lock seems to be at
the page level). See BOL for more info.
AMB
"mhuhn.de@.gmail.com" wrote:
> Update: it is not a select and an UPDATE but a select count and an
> insert.
>|||Thanks for answering. Anyway, 2005 is not an option because our
solution is already used from lots of customers. Do you think a ROWLOCK
makes sense if I do a select count? If the where-clause in the select
count includes the updated row, I'll have the same problem, right?
Furthermore, it will slow down my selects!?|||As I wrote in my other mail :
I do not pretend to understand your locking situation.
But I doubt very much that a ROWLOCK in the select will
solve the problem. The select (without a rowlock) is allready
waiting for another process to finish, this waiting can (I think)
not be solved by using a ROWLOCK, the rowlock will
(probably) prevent the other process on locking on the read
process.
A (row)lock in the update might claim enough resources that
the select is not capable of applying a lock which can stop the
update. So the update can finish after which the select can finish.
ben brugman
<mhuhn.de@.gmail.com> wrote in message
news:1147960900.879319.169740@.j55g2000cwa.googlegroups.com...
> Thanks for answering. Anyway, 2005 is not an option because our
> solution is already used from lots of customers. Do you think a ROWLOCK
> makes sense if I do a select count? If the where-clause in the select
> count includes the updated row, I'll have the same problem, right?
> Furthermore, it will slow down my selects!?
>
Thursday, March 8, 2012
Deactivating Admin and Domain-Admins
Hello,
is it possible to deactivate the groups admins and domain-admins in sql server without getting in trouble with the sql-server. For example when the system boots the program should start normally without any problems.
We want do deactivate the accounts because we have some critical information in sql server and dont want to give all admins the possibility to have a look at these data.
We just want to have sa within the role sysadmin.
Regards
Franz
You can drop the BUILTIN\Adminstrators group from the sql logins. Make sure you have the password to sa as you don't want to leave yourself with no sysadmin login.
Remember, if you're using SQL Agent, the service account it runs under does need to be a sysadmin in sql.
HTH!
|||Thank you for the answer.Yes we are using the SQL Agent. If i give the account the role sysadmin, an administrator has the possibility to give it an new password and can then see all information.
Is there really no chance to deactivate the accounts?
We also have an SQL Cluster is there any further account needed?
If i have no chance to deactivate those accounts generally without giving some special acoounts the sysadmin role, is it a method to deactivate the accounts and when i have to reboot or start the services to give them temporarely the sysadmin role?
Regards
Franz
|||
The SQL Agent Service is a sysadmin by design. In a future version we may be able to modify the design to make SQL Agent not have to be a sysadmin but that doesn't help you today.
Removing builtin\administrator is probably the best you can do.
HTH,
-Steven Gott
SDE/T
SQL Server
Subscribe to:
Posts (Atom)