From: Martin Gainty Date: June 16 2011 10:38am Subject: RE: Query on wait_timeout List-Archive: http://lists.mysql.com/mysql/225270 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_398a5ed6-2001-46f7-971a-1246cba27646_" --_398a5ed6-2001-46f7-971a-1246cba27646_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable TRAN (or TRANSACTION) will apply an implicit lock on the table which remain= s until the TRAN is commit or rollback subsequent DML statements on that same table will be forced to wait UNTIL t= he TRAN is commit or rollback when the statement for the second update on the same table comes along ther= e is a deadlock a the first TRAN has an update lock on that same table...th= e second statement will not execute UNTIL the first statement TRAN is eithe= r commit or rollback releasing the initial lock on that table Viel Gluck=2C Martin=20 ______________________________________________=20 Jogi =E9s Bizalmass=E1gi kinyilatkoztat=E1s/Verzicht und Vertraulichkeitanm= erkung/Note de d=E9ni et de confidentialit=E9 Ez az =FCzenet bizalmas. Ha nem =F6n az akinek sz=E1nva volt=2C akkor k=E9rj=FCk= =2C hogy jelentse azt nek=FCnk vissza. Semmif=E9le tov=E1bb=EDt=E1sa vagy m=E1solat= =E1nak k=E9sz=EDt=E9se nem megengedett. Ez az =FCzenet csak ismeret cser=E9t szol= g=E1l =E9s semmif=E9le jogi alkalmazhat=F3s=E1ga sincs. Mivel az electronikus =FCzene= tek k=F6nnyen megv=E1ltoztathat=F3ak=2C ez=E9rt minket semmi felel=F6s=E9g nem = terhelhet ezen =FCzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng= er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter= leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l= ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin= dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w= ir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes= pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat= isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e= ou la copie de ceci est interdite. Ce message sert =E0 l'information seule= ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d= onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation= =2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni= . > Date: Thu=2C 16 Jun 2011 15:21:38 +0530 > From: adarsh.sharma@stripped > To: mysql@stripped > Subject: Re: Query on wait_timeout >=20 > Thanks Suresh but I find it very difficult to implement it :- >=20 > Suresh Kuna wrote: > > > > Try this out:-) > > > > Below are the steps to generate a deadlock so that the behaviour of a=20 > > deadlock can be illustrated: > > > > -- 1) Create Objects for Deadlock Example > > USE TEMPDB > > > Is I have to create temdb database as below : > create database tempdb > before running the command : >=20 > For running below commands =2C I have to create database dbo or it is=20 > something else > > > > CREATE TABLE dbo.foo (col1 INT) > > INSERT dbo.foo SELECT 1 > > > > CREATE TABLE dbo.bar (col1 INT) > > INSERT dbo.bar SELECT 1 > > > > -- 2) Run in first connection > > BEGIN TRAN > > UPDATE tempdb.dbo.foo SET col1 =3D 1 > > > Don=3Bt understand how to implement it . Is BEGIN TRAN or BEGIN=20 > TRANSACTION command > > > > -- 3) Run in second connection > > BEGIN TRAN > > UPDATE tempdb.dbo.bar SET col1 =3D 1 > > > Can U explain the below and commands : > > > > UPDATE tempdb.dbo.foo SET col1 =3D 1 > > >=20 > > -- 4) Run in first connection > > UPDATE tempdb.dbo.bar SET col1 =3D 1 > > > > Connection two will be chosen as the deadlock victim > > > > > > > > On Thu=2C Jun 16=2C 2011 at 10:53 AM=2C Adarsh Sharma=20 > > > wrote: > > > > How we can create a deadlock manually to test this problem. > > > > Thanks > > > > > > Suresh Kuna wrote: > > > > Good question Yogesh=2C I can say the best solution is > > > > Create a deadlock and test it=2C you will come to know more > > about it. > > > > On Wed=2C Jun 15=2C 2011 at 3:38 PM=2C Yogesh Kore > > > wrote: > > > > =20 > > > > Hi=2C > > > > Small doubt for wait_timeout. > > > > If my wait_timeout is set for 180 seconds and if any > > deadlock occures and > > both query are waiting to execute. What wil happen in that > > case? > > 1. Do the connection will wait till deadlock is removed or > > 2. Connection will close after 180 seconds as both queries > > are ideal and > > waiting for each other. > > > > Thanks=2C > > Yogesh > > > > =20 > > > > > > > > > > =20 > > > > > > > > > > > > --=20 > > Thanks > > Suresh Kuna > > MySQL DBA >=20 = --_398a5ed6-2001-46f7-971a-1246cba27646_--