|
Author |
Message |
Karaman
|
Posted: Fri Jun 18 14:28:36 CDT 2004 |
Top |
SQL Server Developer >> Deadlock...
This is a multi-part message in MIME format.
------=_NextPart_000_01A3_01C4553F.85622980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
About a year ago, an interviewer asked me how to create a deadlock =
situation in SQL Server 2000. My brilliant response started with =
"ummmmm.....what?"
I know how to identify, resolve, define, etc. I know how they are =
created, but this cat wanted me to tell him how to create one as if in a =
test lab.
Bebugging me for a year, so I thought I would ask....
Thanks!
--=20
Kevin Hill
President=20
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
------=_NextPart_000_01A3_01C4553F.85622980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>About a year ago, an interviewer asked =
me how to=20
create a deadlock situation in SQL Server 2000. My brilliant =
response=20
started with "ummmmm.....what?"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I know how to identify, resolve, =
define, etc. =20
I know how they are created, but this cat wanted me to tell him how to =
create=20
one as if in a test lab.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Bebugging me for a year, so I thought I =
would=20
ask....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks!</FONT></DIV><FONT face=3DArial =
size=3D2>
<DIV><BR>-- <BR>Kevin Hill<BR>President <BR>3NF Consulting</DIV>
<DIV> </DIV>
<DIV><A=20
href=3D" http://www.hide-link.com/ ;>www.3nf-inc.com/NewsGroups=
.htm</A><BR><A=20
href=3D" http://www.hide-link.com/ ;>www.DallasDBAs.com/forum</A> - =
new DB=20
forum for Dallas/Ft. Worth area DBAs.<BR></FONT></DIV></BODY></HTML>
------=_NextPart_000_01A3_01C4553F.85622980--
SQL Server186
|
|
|
|
|
Wayne
|
Posted: Fri Jun 18 14:28:36 CDT 2004 |
Top |
SQL Server Developer >> Deadlock...
This is a multi-part message in MIME format.
------=_NextPart_000_0161_01C45548.EC7EB670
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Open 2 connections in QA..
Conn 1
Begin Tran
update titles set price =3D price where title_id =3D 'bu1032'
Conn 2
begin tran
update titles set price =3D price where title_id =3D (A different id)
update titles set price =3D price where title_id =3D 'bu1032'
Then on Conn1
update titles set price =3D price where title_id =3D (THe Conn2 =
title id)
You should now get a deadlock...
--=20
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, C****te, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
About a year ago, an interviewer asked me how to create a deadlock =
situation in SQL Server 2000. My brilliant response started with =
"ummmmm.....what?"
I know how to identify, resolve, define, etc. I know how they are =
created, but this cat wanted me to tell him how to create one as if in a =
test lab.
Bebugging me for a year, so I thought I would ask....
Thanks!
--=20
Kevin Hill
President=20
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =
DBAs.
------=_NextPart_000_0161_01C45548.EC7EB670
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Open 2 connections in QA..</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Conn 1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> Begin Tran</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> update titles set price =
=3D price where=20
title_id =3D 'bu1032'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Conn 2</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> begin tran</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> update titles set price =
=3D price where=20
title_id =3D (A different id)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>update titles set price =3D price where =
title_id =3D=20
'bu1032'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Then on Conn1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> update =
titles set=20
price =3D price where title_id =3D (THe Conn2 title id</FONT><FONT =
face=3DArial=20
size=3D2>)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>You should now get a =
deadlock...</FONT></DIV>
<DIV><BR>-- <BR>Wayne Snyder, MCDBA, SQL Server MVP<BR>Mariner, =
C****te,=20
NC<BR><A =
href=3D" http://www.hide-link.com/ ">www.mariner-usa.com</A><BR>(Please=20
respond only to the newsgroups.)</DIV>
<DIV> </DIV>
<DIV>I support the Professional Association of SQL Server (PASS) and=20
it's<BR>community of SQL Server professionals.<BR><A=20
href=3D" http://www.hide-link.com/ ">www.sqlpass.org</A><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Kevin3NF" <<A=20
=
oSPAM3NF-inc.com</A>>=20
=
@TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>About a year ago, an interviewer =
asked me how to=20
create a deadlock situation in SQL Server 2000. My brilliant =
response=20
started with "ummmmm.....what?"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I know how to identify, resolve, =
define,=20
etc. I know how they are created, but this cat wanted me to tell =
him how=20
to create one as if in a test lab.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Bebugging me for a year, so I thought =
I would=20
ask....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks!</FONT></DIV><FONT =
face=3DArial size=3D2>
<DIV><BR>-- <BR>Kevin Hill<BR>President <BR>3NF Consulting</DIV>
<DIV> </DIV>
<DIV><A=20
=
href=3D" http://www.hide-link.com/ ;>www.3nf-inc.com/NewsGroups=
.htm</A><BR><A=20
href=3D" http://www.hide-link.com/ ;>www.DallasDBAs.com/forum</A> =
- new DB=20
forum for Dallas/Ft. Worth area=20
DBAs.<BR></FONT></DIV></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0161_01C45548.EC7EB670--
|
|
|
|
|
Kevin3NF
|
Posted: Fri Jun 18 17:00:01 CDT 2004 |
Top |
SQL Server Developer >> Deadlock...
This is a multi-part message in MIME format.
------=_NextPart_000_000D_01C45555.B17EE100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
OK...duh....leave the transactions uncommitted with an exclusive lock =
and then try to hit the other one....got it.
Thanks Wayne!
--=20
Kevin Hill
President=20
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
Open 2 connections in QA..
Conn 1
Begin Tran
update titles set price =3D price where title_id =3D 'bu1032'
Conn 2
begin tran
update titles set price =3D price where title_id =3D (A different =
id)
update titles set price =3D price where title_id =3D 'bu1032'
Then on Conn1
update titles set price =3D price where title_id =3D (THe Conn2 =
title id)
You should now get a deadlock...
--=20
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, C****te, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
About a year ago, an interviewer asked me how to create a deadlock =
situation in SQL Server 2000. My brilliant response started with =
"ummmmm.....what?"
I know how to identify, resolve, define, etc. I know how they are =
created, but this cat wanted me to tell him how to create one as if in a =
test lab.
Bebugging me for a year, so I thought I would ask....
Thanks!
--=20
Kevin Hill
President=20
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =
DBAs.
------=_NextPart_000_000D_01C45555.B17EE100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>OK...duh....leave the transactions =
uncommitted with=20
an exclusive lock and then try to hit the other one....got =
it.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks Wayne!</FONT></DIV>
<DIV><BR>-- <BR>Kevin Hill<BR>President <BR>3NF Consulting</DIV>
<DIV> </DIV>
<DIV><A=20
href=3D" http://www.hide-link.com/ ;>www.3nf-inc.com/NewsGroups=
.htm</A><BR><A=20
href=3D" http://www.hide-link.com/ ;>www.DallasDBAs.com/forum</A> - =
new DB=20
forum for Dallas/Ft. Worth area DBAs.<BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Wayne Snyder" <<A=20
=
ariner-usa.com</A>>=20
=
@TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>Open 2 connections in =
QA..</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Conn 1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> Begin Tran</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> update titles set price =
=3D price=20
where title_id =3D 'bu1032'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Conn 2</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> begin tran</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> update titles set price =
=3D price=20
where title_id =3D (A different id)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>update titles set price =3D price =
where title_id =3D=20
'bu1032'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Then on Conn1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> update =
titles set=20
price =3D price where title_id =3D (THe Conn2 title id</FONT><FONT =
face=3DArial=20
size=3D2>)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>You should now get a =
deadlock...</FONT></DIV>
<DIV><BR>-- <BR>Wayne Snyder, MCDBA, SQL Server MVP<BR>Mariner, =
C****te,=20
NC<BR><A =
href=3D" http://www.hide-link.com/ ">www.mariner-usa.com</A><BR>(Please=20
respond only to the newsgroups.)</DIV>
<DIV> </DIV>
<DIV>I support the Professional Association of SQL Server (PASS) and=20
it's<BR>community of SQL Server professionals.<BR><A=20
href=3D" http://www.hide-link.com/ ">www.sqlpass.org</A><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Kevin3NF" <<A=20
=
oSPAM3NF-inc.com</A>>=20
=
@TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>About a year ago, an interviewer =
asked me how=20
to create a deadlock situation in SQL Server 2000. My =
brilliant=20
response started with "ummmmm.....what?"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I know how to identify, resolve, =
define,=20
etc. I know how they are created, but this cat wanted me to =
tell him=20
how to create one as if in a test lab.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Bebugging me for a year, so I =
thought I would=20
ask....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks!</FONT></DIV><FONT =
face=3DArial size=3D2>
<DIV><BR>-- <BR>Kevin Hill<BR>President <BR>3NF Consulting</DIV>
<DIV> </DIV>
<DIV><A=20
=
href=3D" http://www.hide-link.com/ ;>www.3nf-inc.com/NewsGroups=
.htm</A><BR><A=20
=
href=3D" http://www.hide-link.com/ ;>www.DallasDBAs.com/forum</A> - =
new DB=20
forum for Dallas/Ft. Worth area=20
DBAs.<BR></FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_000D_01C45555.B17EE100--
|
|
|
|
|
|
|