Re: Query on wait_timeout
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 adarsh.sha...@orkash.comwrote: 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 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
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
RE: Query on wait_timeout
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
Re: Query on wait_timeout
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 sureshkumar...@gmail.com 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 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 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
Query on wait_timeout
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
Re: Query on wait_timeout
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 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
Re: Query on wait_timeout
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 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
Quick query on 'wait_timeout' parameter.
Hi, I have many Mysql connection threads sleeping which is taking a very good amount of memory so I am reducing 'wait_timeout' parameter from default 8 Hr to 1Hr. Will it have any side effects on My web application. I am concerned because I don't know why the default was kept so high as to 8Hr. Any help will be appreciated .. Thanks and Regards, Shailesh Agrawal -- For sale, parachute, used once, never opened, small stain.
