List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:June 16 2011 10:38am
Subject:RE: Query on wait_timeout
View as plain text  
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.sharma@stripped
> To: mysql@stripped
> 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.sharma@stripped <mailto:adarsh.sharma@stripped>> 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
> >         <yogeshkore@stripped <mailto:yogeshkore@stripped>> 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
> 
 		 	   		  
Thread
Query on wait_timeoutYogesh Kore15 Jun
  • Re: Query on wait_timeoutSuresh Kuna15 Jun
    • Re: Query on wait_timeoutAdarsh Sharma16 Jun
      • Re: Query on wait_timeoutSuresh Kuna16 Jun
        • Re: Query on wait_timeoutAdarsh Sharma16 Jun
          • RE: Query on wait_timeoutMartin Gainty16 Jun
        • Re: Query on wait_timeoutClaudio Nanni16 Jun