TRAN (or TRANSACTION) will apply an implicit lock on the table which remains until the TRAN is commit or rollback subsequent DML statements on that same table will be forced to wait UNTIL the TRAN is commit or rollback
when the statement for the second update on the same table comes along there is a deadlock a the first TRAN has an update lock on that same table...the second statement will not execute UNTIL the first statement TRAN is either commit or rollback releasing the initial lock on that table Viel Gluck, Martin ______________________________________________ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Thu, 16 Jun 2011 15:21:38 +0530 > From: adarsh.sha...@orkash.com > To: mysql@lists.mysql.com > Subject: Re: Query on wait_timeout > > Thanks Suresh but I find it very difficult to implement it :- > > Suresh Kuna wrote: > > > > Try this out:-) > > > > Below are the steps to generate a deadlock so that the behaviour of a > > 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 : > > For running below commands , I have to create database dbo or it is > 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 = 1 > > > Don;t understand how to implement it . Is BEGIN TRAN or BEGIN > TRANSACTION command > > > > -- 3) Run in second connection > > BEGIN TRAN > > UPDATE tempdb.dbo.bar SET col1 = 1 > > > Can U explain the below and commands : > > > > UPDATE tempdb.dbo.foo SET col1 = 1 > > > > > -- 4) Run in first connection > > UPDATE tempdb.dbo.bar SET col1 = 1 > > > > Connection two will be chosen as the deadlock victim > > > > > > > > On Thu, Jun 16, 2011 at 10:53 AM, Adarsh Sharma > > <adarsh.sha...@orkash.com <mailto:adarsh.sha...@orkash.com>> wrote: > > > > How we can create a deadlock manually to test this problem. > > > > Thanks > > > > > > Suresh Kuna wrote: > > > > Good question Yogesh, I can say the best solution is > > > > Create a deadlock and test it, you will come to know more > > about it. > > > > On Wed, Jun 15, 2011 at 3:38 PM, Yogesh Kore > > <yogeshk...@gmail.com <mailto:yogeshk...@gmail.com>> wrote: > > > > > > > > Hi, > > > > 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, > > Yogesh > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > Thanks > > Suresh Kuna > > MySQL DBA >
