Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have?) and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec0x5A2E5578)
Value:0x52fa6780 Cost0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have?) and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec0x5A2E5578)
Value:0x52fa6780 Cost0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robertsql

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec:(0x5A2E5578)
Value:0x52fa6780 Cost:(0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.
> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.
> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Dbo access does not work.

I have a domain user account, DOM\User1, who I have granted dbo rights to
DatabaseA, which is on a server who is a member of the domain DOM as well.
User1 can add, remove, alter tables and stored procedures etc, but when
User1 attempts to update, select, insert or delete a row from any table in
DatabaseA, even if it is a table User1 just created, the user is given a
Select/Update/Insert/Delete Permission Denied error, depending on the task.
The only way to get past the problem is to give DOM\User1 system admin rights
on the server.
If I create a Sql Server user, UserSql1, and give that user dbo access to
DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
fine for UserSql1. Its only the domain accounts that do not work correctly.
Any ideas on this?
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallenI apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen|||Hi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
--
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Seems to me like you have done some DENY in the database, or possibly added to db_denydatareader and
db_denydatawriter to the db_owner role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uPjPeOAlGHA.408@.TK2MSFTNGP03.phx.gbl...
> Raymond Lewallen wrote:
>> I have a domain user account, DOM\User1, who I have granted dbo rights to DatabaseA, which is on
>> a server who is a member of the domain DOM as well. User1 can add, remove, alter tables and
>> stored procedures etc, but when User1 attempts to update, select, insert or delete a row from any
>> table in DatabaseA, even if it is a table User1 just created, the user is given a
>> Select/Update/Insert/Delete Permission Denied error, depending on the task.
>> The only way to get past the problem is to give DOM\User1 system admin rights on the server.
>> If I create a Sql Server user, UserSql1, and give that user dbo access to DatabaseA and attempt
>> to Select/Update/Insert/Delete, then it works just fine for UserSql1. Its only the domain
>> accounts that do not work correctly.
>> Any ideas on this?
>> Raymond Lewallen
>> http://www.codebetter.com/blogs/raymond.lewallen
> Have you explicitly DENIED access to any particular domain groups? Does DOM\User1 belong to one
> of those groups?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?docsql wrote:
> On our dev server the app developers have been granted dbo access to
> their individual databases. They do not have sa rights on the Dev
> SQL Server. The problem is that when the app developers create new
> objects, they are the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects
> are qualified with the owner name in the code. For example
> user1.table.
> But when we rollout the changes in production, since the dba creates
> the objects the owner is the dbo and the application stops working. How
> can i reoslve this problem without giving sa access to the
> developer on the dev db servers?
Yes, by always fully qualifying object names:
Create Table dbo.MyTable(...)
David Gugick
Quest Software

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?
Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>