Query on wait_timeout

--20cf3054a8911e94bd04a5bd54a7
Content-Type: text/plain; charset=ISO-8859-1

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

--20cf3054a8911e94bd04a5bd54a7--

Re: Query on wait_timeout

--20cf302efad81d287404a5be4adf
Content-Type: text/plain; charset=ISO-8859-1

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 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

--20cf302efad81d287404a5be4adf--

Re: Query on wait_timeout

--------------050907010300090901010807
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 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
>>
>>
>
>
>
>


--------------050907010300090901010807--

Re: Query on wait_timeout

--20cf300fb2d36253b304a5ce0b4d
Content-Type: text/plain; charset=ISO-8859-1

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

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

-- 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
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 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
>> 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

--20cf300fb2d36253b304a5ce0b4d--

Re: Query on wait_timeout

--------------030104010601040801020803
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
> > 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
> > 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


--------------030104010601040801020803--

RE: Query on wait_timeout

--_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
üzenet bizalmas. Ha nem ön az akinek sz=E1nva volt=2C akkor k=E9rjük=
=2C hogy
jelentse azt nekünk vissza. Semmif=E9le tov=E1bb=EDt=E1sa vagy m=E1solat=
=E1nak
k=E9sz=EDt=E9se nem megengedett. Ez az üzenet csak ismeret cser=E9t szol=
g=E1l =E9s
semmif=E9le jogi alkalmazhat=F3s=E1ga sincs. Mivel az electronikus üzene=
tek
könnyen megv=E1ltoztathat=F3ak=2C ez=E9rt minket semmi felelös=E9g nem =
terhelhet
ezen üzenet 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@orkash.com
> To: mysql@lists.mysql.com
> 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_--

Re: Query on wait_timeout

--90e6ba21231135da8c04a5da29c7
Content-Type: text/plain; charset=ISO-8859-1

Just a little side note,
The table engine needs to be InnoDB or the transaction will not have effect,
The behavior may differs also according to the isolation level,
That will apply a lock on all records because no where is specified, which
its not very common.

Regards,

Claudio
On Jun 16, 2011 8:05 AM, "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
>
> 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
>
> -- 3) Run in second connection
> BEGIN TRAN
> UPDATE tempdb.dbo.bar SET col1 = 1
> 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 >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
>>> 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

--90e6ba21231135da8c04a5da29c7--