product deadlock scenario

Hi,
I am facing dead lock issues in my production application. But same not
getting in my dev enviornment. Can you pls guide me like how to product a
dead lock scenario in my dev env.
thanks in advance.
BhaskarSure.
1. Open two Query Analyzer windows - Pubs sample database
2. In the first type BEGIN TRAN then an UPDATE to the authors table
3. In the second type BEGIN TRAN then an update to the titles table
4. From the second window now type SELECT * FROM authors
5. From the first window now type SELECT * FROM titles
You now have a deadlock. In this case SQL Server will chose one of the
connections as a deadlock victim. It's transaction will be rolled back and
the winners transction will commit.
6. Close both windows and do not commit any uncommitted transactions when
prompted.
HTH
Jerry
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:ED085BB9-6CE7-4111-B61B-832B494312D3@.microsoft.com...
> Hi,
> I am facing dead lock issues in my production application. But same not
> getting in my dev enviornment. Can you pls guide me like how to product a
> dead lock scenario in my dev env.
> thanks in advance.
> Bhaskar|||my intension is to create a dead lock sceanrio in the dev enviorment.
"Bhaskar" wrote:

> Hi,
> I am facing dead lock issues in my production application. But same not
> getting in my dev enviornment. Can you pls guide me like how to product a
> dead lock scenario in my dev env.
> thanks in advance.
> Bhaskar|||Ok...what I wrote will cause a deadlock to occur. I think you want to
re-create the same deadlock scenario in your dev env that you are now
getting in your prod env. That I can't do.
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:9A950577-D037-44A2-9107-770E557285DF@.microsoft.com...
> my intension is to create a dead lock sceanrio in the dev enviorment.
>
> "Bhaskar" wrote:
>|||Example:
Two diff connections. Try executing the batch iin conn 1 and quickly switch
to conn 2 and execute the batch.
-- conn 1
use northwind
begin transaction
update dbo.orders
set orderdate = getdate()
waitfor delay '00:00:25'
select * from dbo.[order details]
rollback transaction
-- conn 2
use northwind
begin transaction
update dbo.[order details]
set unitprice = 1
select * from dbo.orders
rollback transaction
go
As you can notice, they are accessing the resources in diff order.
AMB
"Bhaskar" wrote:

> Hi,
> I am facing dead lock issues in my production application. But same not
> getting in my dev enviornment. Can you pls guide me like how to product a
> dead lock scenario in my dev env.
> thanks in advance.
> Bhaskar|||Here are a couple of articles on tracing deadlocks:
http://www.sqlservercentral.com/col...ngdeadlocks.asp
http://support.microsoft.com/defaul...kb;en-us;832524
You can control how select queries acquire shared locks by specifying "set
transaction isolation level read uncommitted" or the "nolock" hint. This can
be especially useful if you have an application where multiple users are
performing fairly long queries against the same range of records:
http://msdn.microsoft.com/library/d... />
t_74bw.asp
http://msdn.microsoft.com/library/d... />
a_1hf7.asp
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:ED085BB9-6CE7-4111-B61B-832B494312D3@.microsoft.com...
> Hi,
> I am facing dead lock issues in my production application. But same not
> getting in my dev enviornment. Can you pls guide me like how to product a
> dead lock scenario in my dev env.
> thanks in advance.
> Bhaskar

process which lock himself??

Hi,
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
Jerome
Have you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/defaul...v_04222005.asp
Paul
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>
|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>
|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>
|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>

process which lock himself??

Hi,
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
JeromeHave you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_04222005.asp
Paul
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> At a guess you are locking another process by the same user and not
>> locking the process that is doing the shrink, in that respect the locked
>> process is no different to any other!
>> If you posted your SQL Server version and the output of your sp_who2 it
>> would have been more informative.
>> John
>>
>> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
>> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>>
>|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> At a guess you are locking another process by the same user and not
>> locking the process that is doing the shrink, in that respect the locked
>> process is no different to any other!
>> If you posted your SQL Server version and the output of your sp_who2 it
>> would have been more informative.
>> John
>>
>> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
>> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>>
>

process which lock himself??

Hi,
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
JeromeHave you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/defau...
v_04222005.asp
Paul
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>