RE: Deadlock due lockwait. How can I tell mysql to wait longer?
... WHERE id IN (...) -- This will (I think) sort the IN list. Therefore, if two queries have the same (or overlapping) IN values, there cannot be a deadlock. (I am assuming nothing else being touched.) If, on the other hand, you try to get a list of rows by other means, and the order of the rows is not as predictable, then you can get deadlocks. Example: BEGIN ... WHERE id = 123 ... WHERE id = 456 ... WHERE id = 5 ... COMMIT In one case, I could shuffle the statements into numerical order to eliminate a deadlock that was happening about 1% of the time. > -Original Message- > From: Claudio Nanni [mailto:claudio.na...@gmail.com] > Sent: Monday, May 14, 2012 12:29 PM > To: Andrés Tello > Cc: Baron Schwartz; MySql > Subject: Re: Deadlock due lockwait. How can I tell mysql to wait > longer? > > Andrés, > > with pleasure. > > Imagine a website that is used to search, just for example, hotel rooms > for booking. > > It is possible that a programmer would: > > 1) issue a select that returns the IDs the rooms matching the criteria > 2) do a loop in the code scanning each ID of the resultset and for each > ID issue the SELECT to get the details of the Hotel and/or Room > (probably and, > using a join) e.g. SELECT.WHERE roomid=123 > > What happens is that if your search criteria return, for example, 200 > rooms, you will issue 200 selects to get the details, those selects are > 'twin' selects, that is they are identical with different parameters, > this in my experience is one main cause of contention, keep in mind > that a while loop in php (for example) is extremely fast. > > A better approach, always in my experience, is to: > > 1) issue a select that returns the IDs the rooms matching the criteria > 2) issue 1 select to get all the results by using something like: > SELECT ,,,WHERE roomid in (1,2,6,123,239,599,...) > 3) loop in the resultset and get the details of hotel/room > > May be at first look it just look a choice of style, but just imagine > this: > > you have an application which search part (aforementioned) has 1000 > hits per second, with the first approach (which I call auto inflicted > Denial of Service :) ) you will have 1 + 1000x(200-1000) = ~200k-1M > queries with the second you will have 1+ 1000 queries, no more need to > scale out :) > > I hope I was enough clear, if not do not hesitate to ask, and please > anyone correct me if I am wrong. > > Claudio > > > > > > > 2012/5/14 Andrés Tello > > > > > Claudio, would you please extend the example to the use of in? > > > > > > On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni > wrote: > > > >> In my experience if you have a poor designed code that run the same > >> query for hundreds or thousands of times in a very short timespan > >> (like some programmers do in for-loop instead of using a IN for > >> example) you can put mysql on its knees, in some cases it may be the > >> practical implementation of some lock mechanisms are particularly > >> challenged by this ultra high data 'locality' which bring to very > >> high contention on a few hotspots at different levels (mutexes, > >> indexes, pages). > >> > >> > >> Just reflections :) > >> > >> Claudio > >> > >> 2012/5/14 Baron Schwartz > >> > >> > Argh. I meant to send this to the list but it doesn't have the > >> > reply-to set as I expect... > >> > > >> > On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz > >> wrote: > >> > > Johan, > >> > > > >> > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman < > >> vegiv...@tuxera.be> > >> > wrote: > >> > >> What I fail to understand, Baron, is how there can be a > deadlock > >> here - > >> > both transactions seem to be hanging on a single-table, single-row > >> update > >> > statement. Shouldn't the oldest transaction already have acquired > >> > the > >> lock > >> > by the time the youngest came around; and shouldn't the youngest > >> > simply wait until the eldest finished it's update? > >> > > > >> > > Take a look at the output again: > >> > > > >> > > 8< === > >> > > > >> > > *** (1) TRANSACTION: > >> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read mysql > >> > > tables in use 1, locke
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Andrés, with pleasure. Imagine a website that is used to search, just for example, hotel rooms for booking. It is possible that a programmer would: 1) issue a select that returns the IDs the rooms matching the criteria 2) do a loop in the code scanning each ID of the resultset and for each ID issue the SELECT to get the details of the Hotel and/or Room (probably and, using a join) e.g. SELECT.WHERE roomid=123 What happens is that if your search criteria return, for example, 200 rooms, you will issue 200 selects to get the details, those selects are 'twin' selects, that is they are identical with different parameters, this in my experience is one main cause of contention, keep in mind that a while loop in php (for example) is extremely fast. A better approach, always in my experience, is to: 1) issue a select that returns the IDs the rooms matching the criteria 2) issue 1 select to get all the results by using something like: SELECT ,,,WHERE roomid in (1,2,6,123,239,599,...) 3) loop in the resultset and get the details of hotel/room May be at first look it just look a choice of style, but just imagine this: you have an application which search part (aforementioned) has 1000 hits per second, with the first approach (which I call auto inflicted Denial of Service :) ) you will have 1 + 1000x(200-1000) = ~200k-1M queries with the second you will have 1+ 1000 queries, no more need to scale out :) I hope I was enough clear, if not do not hesitate to ask, and please anyone correct me if I am wrong. Claudio 2012/5/14 Andrés Tello > > Claudio, would you please extend the example to the use of in? > > > On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni > wrote: > >> In my experience if you have a poor designed code that run the same query >> for hundreds or thousands of times in a very short timespan (like some >> programmers do in for-loop instead of using a IN for example) you can put >> mysql on its knees, in some cases it may be the practical implementation >> of >> some lock mechanisms are particularly challenged by this ultra high data >> 'locality' which bring to very high contention on a few hotspots at >> different levels (mutexes, indexes, pages). >> >> >> Just reflections :) >> >> Claudio >> >> 2012/5/14 Baron Schwartz >> >> > Argh. I meant to send this to the list but it doesn't have the >> > reply-to set as I expect... >> > >> > On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz >> wrote: >> > > Johan, >> > > >> > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman < >> vegiv...@tuxera.be> >> > wrote: >> > >> What I fail to understand, Baron, is how there can be a deadlock >> here - >> > both transactions seem to be hanging on a single-table, single-row >> update >> > statement. Shouldn't the oldest transaction already have acquired the >> lock >> > by the time the youngest came around; and shouldn't the youngest simply >> > wait until the eldest finished it's update? >> > > >> > > Take a look at the output again: >> > > >> > > 8< === >> > > >> > > *** (1) TRANSACTION: >> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read >> > > mysql tables in use 1, locked 1 >> > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s) >> > > update `account` set `balance`= 0.00 + '-6.07' where >> accountid='3235296' >> > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED: >> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of >> > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but >> not >> > > gap waiting >> > > >> > > *** (2) TRANSACTION: >> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread >> declared >> > > inside InnoDB 500 >> > > mysql tables in use 1, locked 1 >> > > 14 lock struct(s), heap size 3112, 27 row lock(s) >> > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id >> 32378480 >> > > 90.0.0.51 mario Updating >> > > update `account` set `balance`= 0.00 + '-1.37' where >> accountid='3235296' >> > > >> > > *** (2) HOLDS THE LOCK(S): >> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of >> > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but >> not >> > > gap >> > > >> > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED: >> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of >> > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but >> not >> > > gap waiting >> > > >> > > *** WE ROLL BACK TRANSACTION (1) >> > > >> > > >> > > 8< === >> > > >> > > Here is how to interpret that: Transaction 1 has locked 27 rows (not >> > > just a single row!) and is waiting for an exclusive lock on some row. >> > > Transaction 2 holds a shared lock on that same row and is trying to >> > > upgraded its shared lock to an exclusive lock. >> > > >> > > Both transactions have locked 27 rows, so this is not a single-row, >> > > single
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Claudio, would you please extend the example to the use of in? On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni wrote: > In my experience if you have a poor designed code that run the same query > for hundreds or thousands of times in a very short timespan (like some > programmers do in for-loop instead of using a IN for example) you can put > mysql on its knees, in some cases it may be the practical implementation of > some lock mechanisms are particularly challenged by this ultra high data > 'locality' which bring to very high contention on a few hotspots at > different levels (mutexes, indexes, pages). > > > Just reflections :) > > Claudio > > 2012/5/14 Baron Schwartz > > > Argh. I meant to send this to the list but it doesn't have the > > reply-to set as I expect... > > > > On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz > wrote: > > > Johan, > > > > > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman > > > wrote: > > >> What I fail to understand, Baron, is how there can be a deadlock here > - > > both transactions seem to be hanging on a single-table, single-row update > > statement. Shouldn't the oldest transaction already have acquired the > lock > > by the time the youngest came around; and shouldn't the youngest simply > > wait until the eldest finished it's update? > > > > > > Take a look at the output again: > > > > > > 8< === > > > > > > *** (1) TRANSACTION: > > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read > > > mysql tables in use 1, locked 1 > > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s) > > > update `account` set `balance`= 0.00 + '-6.07' where > accountid='3235296' > > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED: > > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but > not > > > gap waiting > > > > > > *** (2) TRANSACTION: > > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread > declared > > > inside InnoDB 500 > > > mysql tables in use 1, locked 1 > > > 14 lock struct(s), heap size 3112, 27 row lock(s) > > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id > 32378480 > > > 90.0.0.51 mario Updating > > > update `account` set `balance`= 0.00 + '-1.37' where > accountid='3235296' > > > > > > *** (2) HOLDS THE LOCK(S): > > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but > not > > > gap > > > > > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED: > > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but > not > > > gap waiting > > > > > > *** WE ROLL BACK TRANSACTION (1) > > > > > > > > > 8< === > > > > > > Here is how to interpret that: Transaction 1 has locked 27 rows (not > > > just a single row!) and is waiting for an exclusive lock on some row. > > > Transaction 2 holds a shared lock on that same row and is trying to > > > upgraded its shared lock to an exclusive lock. > > > > > > Both transactions have locked 27 rows, so this is not a single-row, > > > single-table problem. It may be the case that it is a single-statement > > > problem, but in that case the statement needs to be optimized somehow > > > so that it does not access too many rows. But there is not enough > > > information to really diagnose what is going on. > > > > > > > > -- > > Baron Schwartz > > Author, High Performance MySQL > > http://www.xaprb.com/ > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql > > > > > > > -- > Claudio >
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
In my experience if you have a poor designed code that run the same query for hundreds or thousands of times in a very short timespan (like some programmers do in for-loop instead of using a IN for example) you can put mysql on its knees, in some cases it may be the practical implementation of some lock mechanisms are particularly challenged by this ultra high data 'locality' which bring to very high contention on a few hotspots at different levels (mutexes, indexes, pages). Just reflections :) Claudio 2012/5/14 Baron Schwartz > Argh. I meant to send this to the list but it doesn't have the > reply-to set as I expect... > > On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz wrote: > > Johan, > > > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman > wrote: > >> What I fail to understand, Baron, is how there can be a deadlock here - > both transactions seem to be hanging on a single-table, single-row update > statement. Shouldn't the oldest transaction already have acquired the lock > by the time the youngest came around; and shouldn't the youngest simply > wait until the eldest finished it's update? > > > > Take a look at the output again: > > > > 8< === > > > > *** (1) TRANSACTION: > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read > > mysql tables in use 1, locked 1 > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s) > > update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296' > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED: > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not > > gap waiting > > > > *** (2) TRANSACTION: > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared > > inside InnoDB 500 > > mysql tables in use 1, locked 1 > > 14 lock struct(s), heap size 3112, 27 row lock(s) > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480 > > 90.0.0.51 mario Updating > > update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296' > > > > *** (2) HOLDS THE LOCK(S): > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not > > gap > > > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED: > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not > > gap waiting > > > > *** WE ROLL BACK TRANSACTION (1) > > > > > > 8< === > > > > Here is how to interpret that: Transaction 1 has locked 27 rows (not > > just a single row!) and is waiting for an exclusive lock on some row. > > Transaction 2 holds a shared lock on that same row and is trying to > > upgraded its shared lock to an exclusive lock. > > > > Both transactions have locked 27 rows, so this is not a single-row, > > single-table problem. It may be the case that it is a single-statement > > problem, but in that case the statement needs to be optimized somehow > > so that it does not access too many rows. But there is not enough > > information to really diagnose what is going on. > > > > -- > Baron Schwartz > Author, High Performance MySQL > http://www.xaprb.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Claudio
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Argh. I meant to send this to the list but it doesn't have the reply-to set as I expect... On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz wrote: > Johan, > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman wrote: >> What I fail to understand, Baron, is how there can be a deadlock here - both >> transactions seem to be hanging on a single-table, single-row update >> statement. Shouldn't the oldest transaction already have acquired the lock >> by the time the youngest came around; and shouldn't the youngest simply wait >> until the eldest finished it's update? > > Take a look at the output again: > > 8< === > > *** (1) TRANSACTION: > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read > mysql tables in use 1, locked 1 > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s) > update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296' > *** (1) WAITING FOR THIS LOCK TO BE GRANTED: > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not > gap waiting > > *** (2) TRANSACTION: > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared > inside InnoDB 500 > mysql tables in use 1, locked 1 > 14 lock struct(s), heap size 3112, 27 row lock(s) > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480 > 90.0.0.51 mario Updating > update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296' > > *** (2) HOLDS THE LOCK(S): > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not > gap > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED: > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not > gap waiting > > *** WE ROLL BACK TRANSACTION (1) > > > 8< === > > Here is how to interpret that: Transaction 1 has locked 27 rows (not > just a single row!) and is waiting for an exclusive lock on some row. > Transaction 2 holds a shared lock on that same row and is trying to > upgraded its shared lock to an exclusive lock. > > Both transactions have locked 27 rows, so this is not a single-row, > single-table problem. It may be the case that it is a single-statement > problem, but in that case the statement needs to be optimized somehow > so that it does not access too many rows. But there is not enough > information to really diagnose what is going on. -- Baron Schwartz Author, High Performance MySQL http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Deadlock due lockwait. How can I tell mysql to wait longer?
Going on a limb here...: I believe I have occurred similar issue (i.e. two transactions go into an indefinite wait).Though, very infrequent occurrence. My only explanation at that time was that there is some "loophole" when the deletes/inserts had some impact also on the table indexes. In our case, the deletes/inserts statements were invoked by a stored procedure. David. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, May 14, 2012 9:28 AM To: Baron Schwartz Cc: MySql Subject: Re: Deadlock due lockwait. How can I tell mysql to wait longer? - Original Message - > From: "Baron Schwartz" > > Because it can be resolved by rolling back just one of them. Why > destroy ALL the work people are trying to accomplish, if you could > just throw away some of it? What I fail to understand, Baron, is how there can be a deadlock here - both transactions seem to be hanging on a single-table, single-row update statement. Shouldn't the oldest transaction already have acquired the lock by the time the youngest came around; and shouldn't the youngest simply wait until the eldest finished it's update? Or is this a problem with the consistent view that I'm not seeing? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
- Original Message - > From: "Baron Schwartz" > > Because it can be resolved by rolling back just one of them. Why > destroy ALL the work people are trying to accomplish, if you could > just throw away some of it? What I fail to understand, Baron, is how there can be a deadlock here - both transactions seem to be hanging on a single-table, single-row update statement. Shouldn't the oldest transaction already have acquired the lock by the time the youngest came around; and shouldn't the youngest simply wait until the eldest finished it's update? Or is this a problem with the consistent view that I'm not seeing? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
humm, I see.. and if is encapusulated with it's own begin-commit inside a bigger transacion, only that small part get rolled back... If I get this straigth... On Fri, May 11, 2012 at 2:32 PM, Baron Schwartz wrote: > Andres, > > On Fri, May 11, 2012 at 1:48 PM, Andrés Tello > wrote: > > Ok, so I had a deadlock... > > > > But then, why a deadlock doesn't rollback all the transaccion? > > Because it can be resolved by rolling back just one of them. Why > destroy ALL the work people are trying to accomplish, if you could > just throw away some of it? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Andres, On Fri, May 11, 2012 at 1:48 PM, Andrés Tello wrote: > Ok, so I had a deadlock... > > But then, why a deadlock doesn't rollback all the transaccion? Because it can be resolved by rolling back just one of them. Why destroy ALL the work people are trying to accomplish, if you could just throw away some of it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
The genral log is the log that "logs" everything?
humm... dunno if I can.. as always... stuuupid production server with no
testing instance available...
And it happens very seldom, but force us to do a
select (sum) from the movements table instead just a select balance from
account...
On Fri, May 11, 2012 at 1:53 PM, Claudio Nanni wrote:
> Andrés,
>
> may be you can enable the general log, recreate the deadlock,
> and attach the general log?
>
> If I had to reason as InnoDB, what I see is two updates statements that
> arrive and want to update the same record,
> I would be confused exactly as InnoDB is because I would not know which
> update is the 'good' one,
> I'd close my eyes and kill one.
> This is a deadlock.
>
> Claudio
>
> 2012/5/11 Andrés Tello
>
>> Yup, but a far I understand...
>> I made a
>>
>> select balance for update where accountid=3235296 lock in shared mode;
>>
>> over the same accountid , so the second transacion just would need to
>> wait to the first transaccion to finish...
>>
>> That is why I'm confuse if I have a Deadlock o a wait lock...
>>
>> That is why I'm
>>
>> On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni
>> wrote:
>>
>>> Hello Andrés
>>>
>>> did you notice that both transactions are trying to update records with
>>> same *accountid='3235296' *
>>> and that they lock the same index page? *space id 5806 page no 69100 n
>>> bits 176 index*
>>>
>>> Cheers
>>>
>>> Claudio
>>>
>>> 2012/5/11 Andrés Tello
>>>
Ok, so I had a deadlock...
But then, why a deadlock doesn't rollback all the transaccion?
On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz
wrote:
> Deadlocks and lock wait timeouts are independent of one another. A
> deadlock happens when there is a cycle in the waits-for graph. Your
> transactions are *active* for 132 and 33 seconds, but the deadlock
> happens at the instant the conflict is detected, not after waiting. A
> deadlock cannot be resolved by waiting, by definition. Hence the name,
> deadlock. The only way to resolve it is to choose a victim.
>
> On Fri, May 11, 2012 at 3:06 AM, Andrés Tello
> wrote:
> > Ok... I have one of those pesky error, in an application not
handling
> > deadlocks or lockwaits.
> >
> > The database object can't be modified to support
deadlock/lockwatis...
> > I can only change database parameteres
> >
> > Database info: Server version: 5.5.22-log Source distribution
> >
> >
> > from show engine innodb status;
> > {abstract}
> > *** (1) TRANSACTION:
> > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> > mysql tables in use 1, locked 1
> > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> > update `account` set `balance`= 0.00 + '-6.07' where
accountid='3235296'
> > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
of
> > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec
but not
> > gap waiting
> >
> > *** (2) TRANSACTION:
> > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
declared
> > inside InnoDB 500
> > mysql tables in use 1, locked 1
> > 14 lock struct(s), heap size 3112, 27 row lock(s)
> > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
32378480
> > 90.0.0.51 mario Updating
> > update `account` set `balance`= 0.00 + '-1.37' where
accountid='3235296'
> >
> > *** (2) HOLDS THE LOCK(S):
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
of
> > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec
but not
> > gap
> >
> > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
of
> > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec
but not
> > gap waiting
> >
> > *** WE ROLL BACK TRANSACTION (1)
> >
> > The issue is that I had a lock for over 132 seconds and the other
was
> > waiting for 33 seconds, so I get a lockwait.
> >
> > accountid is locked by a
> > select balance from account where accountid='3235296' lock in
shared mode
> > How can I tell mysql to wait longer? I know the process which is
doing
> the
> > deadlock, is a long balance process... I know that it takes time,
> sometives
> > over 15 minutes, but they always resolve...
> >
> > How Can I tell mysql to wait for the lock as needed? like for over
12
> > minutes?
> >
> > TIA
>
>
>
> --
> Baron Schwartz
> Author, High Performance MySQL
> http://www.xaprb.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.my
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Andrés,
may be you can enable the general log, recreate the deadlock,
and attach the general log?
If I had to reason as InnoDB, what I see is two updates statements that
arrive and want to update the same record,
I would be confused exactly as InnoDB is because I would not know which
update is the 'good' one,
I'd close my eyes and kill one.
This is a deadlock.
Claudio
2012/5/11 Andrés Tello
> Yup, but a far I understand...
> I made a
>
> select balance for update where accountid=3235296 lock in shared mode;
>
> over the same accountid , so the second transacion just would need to
> wait to the first transaccion to finish...
>
> That is why I'm confuse if I have a Deadlock o a wait lock...
>
> That is why I'm
>
> On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni wrote:
>
>> Hello Andrés
>>
>> did you notice that both transactions are trying to update records with
>> same *accountid='3235296' *
>> and that they lock the same index page? *space id 5806 page no 69100 n
>> bits 176 index*
>>
>> Cheers
>>
>> Claudio
>>
>> 2012/5/11 Andrés Tello
>>
>>> Ok, so I had a deadlock...
>>>
>>> But then, why a deadlock doesn't rollback all the transaccion?
>>>
>>>
>>> On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz wrote:
>>>
>>> > Deadlocks and lock wait timeouts are independent of one another. A
>>> > deadlock happens when there is a cycle in the waits-for graph. Your
>>> > transactions are *active* for 132 and 33 seconds, but the deadlock
>>> > happens at the instant the conflict is detected, not after waiting. A
>>> > deadlock cannot be resolved by waiting, by definition. Hence the name,
>>> > deadlock. The only way to resolve it is to choose a victim.
>>> >
>>> > On Fri, May 11, 2012 at 3:06 AM, Andrés Tello
>>> > wrote:
>>> > > Ok... I have one of those pesky error, in an application not handling
>>> > > deadlocks or lockwaits.
>>> > >
>>> > > The database object can't be modified to support
>>> deadlock/lockwatis...
>>> > > I can only change database parameteres
>>> > >
>>> > > Database info: Server version: 5.5.22-log Source distribution
>>> > >
>>> > >
>>> > > from show engine innodb status;
>>> > > {abstract}
>>> > > *** (1) TRANSACTION:
>>> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
>>> > > mysql tables in use 1, locked 1
>>> > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
>>> > > update `account` set `balance`= 0.00 + '-6.07' where
>>> accountid='3235296'
>>> > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
>>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
>>> of
>>> > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec
>>> but not
>>> > > gap waiting
>>> > >
>>> > > *** (2) TRANSACTION:
>>> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
>>> declared
>>> > > inside InnoDB 500
>>> > > mysql tables in use 1, locked 1
>>> > > 14 lock struct(s), heap size 3112, 27 row lock(s)
>>> > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
>>> 32378480
>>> > > 90.0.0.51 mario Updating
>>> > > update `account` set `balance`= 0.00 + '-1.37' where
>>> accountid='3235296'
>>> > >
>>> > > *** (2) HOLDS THE LOCK(S):
>>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
>>> of
>>> > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec
>>> but not
>>> > > gap
>>> > >
>>> > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
>>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
>>> of
>>> > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec
>>> but not
>>> > > gap waiting
>>> > >
>>> > > *** WE ROLL BACK TRANSACTION (1)
>>> > >
>>> > > The issue is that I had a lock for over 132 seconds and the other was
>>> > > waiting for 33 seconds, so I get a lockwait.
>>> > >
>>> > > accountid is locked by a
>>> > > select balance from account where accountid='3235296' lock in shared
>>> mode
>>> > > How can I tell mysql to wait longer? I know the process which is
>>> doing
>>> > the
>>> > > deadlock, is a long balance process... I know that it takes time,
>>> > sometives
>>> > > over 15 minutes, but they always resolve...
>>> > >
>>> > > How Can I tell mysql to wait for the lock as needed? like for over 12
>>> > > minutes?
>>> > >
>>> > > TIA
>>> >
>>> >
>>> >
>>> > --
>>> > Baron Schwartz
>>> > Author, High Performance MySQL
>>> > http://www.xaprb.com/
>>> >
>>> > --
>>> > MySQL General Mailing List
>>> > For list archives: http://lists.mysql.com/mysql
>>> > To unsubscribe:http://lists.mysql.com/mysql
>>> >
>>> >
>>>
>>
>>
>>
>> --
>> Claudio
>>
>
>
--
Claudio
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Yup, but a far I understand...
I made a
select balance for update where accountid=3235296 lock in shared mode;
over the same accountid , so the second transacion just would need to wait
to the first transaccion to finish...
That is why I'm confuse if I have a Deadlock o a wait lock...
That is why I'm
On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni wrote:
> Hello Andrés
>
> did you notice that both transactions are trying to update records with
> same *accountid='3235296' *
> and that they lock the same index page? *space id 5806 page no 69100 n
> bits 176 index*
>
> Cheers
>
> Claudio
>
> 2012/5/11 Andrés Tello
>
>> Ok, so I had a deadlock...
>>
>> But then, why a deadlock doesn't rollback all the transaccion?
>>
>>
>> On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz wrote:
>>
>> > Deadlocks and lock wait timeouts are independent of one another. A
>> > deadlock happens when there is a cycle in the waits-for graph. Your
>> > transactions are *active* for 132 and 33 seconds, but the deadlock
>> > happens at the instant the conflict is detected, not after waiting. A
>> > deadlock cannot be resolved by waiting, by definition. Hence the name,
>> > deadlock. The only way to resolve it is to choose a victim.
>> >
>> > On Fri, May 11, 2012 at 3:06 AM, Andrés Tello
>> > wrote:
>> > > Ok... I have one of those pesky error, in an application not handling
>> > > deadlocks or lockwaits.
>> > >
>> > > The database object can't be modified to support deadlock/lockwatis...
>> > > I can only change database parameteres
>> > >
>> > > Database info: Server version: 5.5.22-log Source distribution
>> > >
>> > >
>> > > from show engine innodb status;
>> > > {abstract}
>> > > *** (1) TRANSACTION:
>> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
>> > > mysql tables in use 1, locked 1
>> > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
>> > > update `account` set `balance`= 0.00 + '-6.07' where
>> accountid='3235296'
>> > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
>> > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but
>> not
>> > > gap waiting
>> > >
>> > > *** (2) TRANSACTION:
>> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
>> declared
>> > > inside InnoDB 500
>> > > mysql tables in use 1, locked 1
>> > > 14 lock struct(s), heap size 3112, 27 row lock(s)
>> > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
>> 32378480
>> > > 90.0.0.51 mario Updating
>> > > update `account` set `balance`= 0.00 + '-1.37' where
>> accountid='3235296'
>> > >
>> > > *** (2) HOLDS THE LOCK(S):
>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
>> > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but
>> not
>> > > gap
>> > >
>> > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
>> > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but
>> not
>> > > gap waiting
>> > >
>> > > *** WE ROLL BACK TRANSACTION (1)
>> > >
>> > > The issue is that I had a lock for over 132 seconds and the other was
>> > > waiting for 33 seconds, so I get a lockwait.
>> > >
>> > > accountid is locked by a
>> > > select balance from account where accountid='3235296' lock in shared
>> mode
>> > > How can I tell mysql to wait longer? I know the process which is doing
>> > the
>> > > deadlock, is a long balance process... I know that it takes time,
>> > sometives
>> > > over 15 minutes, but they always resolve...
>> > >
>> > > How Can I tell mysql to wait for the lock as needed? like for over 12
>> > > minutes?
>> > >
>> > > TIA
>> >
>> >
>> >
>> > --
>> > Baron Schwartz
>> > Author, High Performance MySQL
>> > http://www.xaprb.com/
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:http://lists.mysql.com/mysql
>> >
>> >
>>
>
>
>
> --
> Claudio
>
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Hello Andrés
did you notice that both transactions are trying to update records with
same *accountid='3235296' *
and that they lock the same index page? *space id 5806 page no 69100 n bits
176 index*
Cheers
Claudio
2012/5/11 Andrés Tello
> Ok, so I had a deadlock...
>
> But then, why a deadlock doesn't rollback all the transaccion?
>
>
> On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz wrote:
>
> > Deadlocks and lock wait timeouts are independent of one another. A
> > deadlock happens when there is a cycle in the waits-for graph. Your
> > transactions are *active* for 132 and 33 seconds, but the deadlock
> > happens at the instant the conflict is detected, not after waiting. A
> > deadlock cannot be resolved by waiting, by definition. Hence the name,
> > deadlock. The only way to resolve it is to choose a victim.
> >
> > On Fri, May 11, 2012 at 3:06 AM, Andrés Tello
> > wrote:
> > > Ok... I have one of those pesky error, in an application not handling
> > > deadlocks or lockwaits.
> > >
> > > The database object can't be modified to support deadlock/lockwatis...
> > > I can only change database parameteres
> > >
> > > Database info: Server version: 5.5.22-log Source distribution
> > >
> > >
> > > from show engine innodb status;
> > > {abstract}
> > > *** (1) TRANSACTION:
> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> > > mysql tables in use 1, locked 1
> > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> > > update `account` set `balance`= 0.00 + '-6.07' where
> accountid='3235296'
> > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but
> not
> > > gap waiting
> > >
> > > *** (2) TRANSACTION:
> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
> declared
> > > inside InnoDB 500
> > > mysql tables in use 1, locked 1
> > > 14 lock struct(s), heap size 3112, 27 row lock(s)
> > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
> 32378480
> > > 90.0.0.51 mario Updating
> > > update `account` set `balance`= 0.00 + '-1.37' where
> accountid='3235296'
> > >
> > > *** (2) HOLDS THE LOCK(S):
> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but
> not
> > > gap
> > >
> > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but
> not
> > > gap waiting
> > >
> > > *** WE ROLL BACK TRANSACTION (1)
> > >
> > > The issue is that I had a lock for over 132 seconds and the other was
> > > waiting for 33 seconds, so I get a lockwait.
> > >
> > > accountid is locked by a
> > > select balance from account where accountid='3235296' lock in shared
> mode
> > > How can I tell mysql to wait longer? I know the process which is doing
> > the
> > > deadlock, is a long balance process... I know that it takes time,
> > sometives
> > > over 15 minutes, but they always resolve...
> > >
> > > How Can I tell mysql to wait for the lock as needed? like for over 12
> > > minutes?
> > >
> > > TIA
> >
> >
> >
> > --
> > Baron Schwartz
> > Author, High Performance MySQL
> > http://www.xaprb.com/
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
>
--
Claudio
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Ok, so I had a deadlock...
But then, why a deadlock doesn't rollback all the transaccion?
On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz wrote:
> Deadlocks and lock wait timeouts are independent of one another. A
> deadlock happens when there is a cycle in the waits-for graph. Your
> transactions are *active* for 132 and 33 seconds, but the deadlock
> happens at the instant the conflict is detected, not after waiting. A
> deadlock cannot be resolved by waiting, by definition. Hence the name,
> deadlock. The only way to resolve it is to choose a victim.
>
> On Fri, May 11, 2012 at 3:06 AM, Andrés Tello
> wrote:
> > Ok... I have one of those pesky error, in an application not handling
> > deadlocks or lockwaits.
> >
> > The database object can't be modified to support deadlock/lockwatis...
> > I can only change database parameteres
> >
> > Database info: Server version: 5.5.22-log Source distribution
> >
> >
> > from show engine innodb status;
> > {abstract}
> > *** (1) TRANSACTION:
> > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> > mysql tables in use 1, locked 1
> > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> > update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
> > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
> > gap waiting
> >
> > *** (2) TRANSACTION:
> > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
> > inside InnoDB 500
> > mysql tables in use 1, locked 1
> > 14 lock struct(s), heap size 3112, 27 row lock(s)
> > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480
> > 90.0.0.51 mario Updating
> > update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296'
> >
> > *** (2) HOLDS THE LOCK(S):
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not
> > gap
> >
> > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not
> > gap waiting
> >
> > *** WE ROLL BACK TRANSACTION (1)
> >
> > The issue is that I had a lock for over 132 seconds and the other was
> > waiting for 33 seconds, so I get a lockwait.
> >
> > accountid is locked by a
> > select balance from account where accountid='3235296' lock in shared mode
> > How can I tell mysql to wait longer? I know the process which is doing
> the
> > deadlock, is a long balance process... I know that it takes time,
> sometives
> > over 15 minutes, but they always resolve...
> >
> > How Can I tell mysql to wait for the lock as needed? like for over 12
> > minutes?
> >
> > TIA
>
>
>
> --
> Baron Schwartz
> Author, High Performance MySQL
> http://www.xaprb.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Deadlocks and lock wait timeouts are independent of one another. A
deadlock happens when there is a cycle in the waits-for graph. Your
transactions are *active* for 132 and 33 seconds, but the deadlock
happens at the instant the conflict is detected, not after waiting. A
deadlock cannot be resolved by waiting, by definition. Hence the name,
deadlock. The only way to resolve it is to choose a victim.
On Fri, May 11, 2012 at 3:06 AM, Andrés Tello wrote:
> Ok... I have one of those pesky error, in an application not handling
> deadlocks or lockwaits.
>
> The database object can't be modified to support deadlock/lockwatis...
> I can only change database parameteres
>
> Database info: Server version: 5.5.22-log Source distribution
>
>
> from show engine innodb status;
> {abstract}
> *** (1) TRANSACTION:
> TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> mysql tables in use 1, locked 1
> LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
> *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
> gap waiting
>
> *** (2) TRANSACTION:
> TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
> inside InnoDB 500
> mysql tables in use 1, locked 1
> 14 lock struct(s), heap size 3112, 27 row lock(s)
> MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480
> 90.0.0.51 mario Updating
> update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296'
>
> *** (2) HOLDS THE LOCK(S):
> RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not
> gap
>
> *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not
> gap waiting
>
> *** WE ROLL BACK TRANSACTION (1)
>
> The issue is that I had a lock for over 132 seconds and the other was
> waiting for 33 seconds, so I get a lockwait.
>
> accountid is locked by a
> select balance from account where accountid='3235296' lock in shared mode
> How can I tell mysql to wait longer? I know the process which is doing the
> deadlock, is a long balance process... I know that it takes time, sometives
> over 15 minutes, but they always resolve...
>
> How Can I tell mysql to wait for the lock as needed? like for over 12
> minutes?
>
> TIA
--
Baron Schwartz
Author, High Performance MySQL
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Deadlock due lockwait. How can I tell mysql to wait longer?
Ok... I have one of those pesky error, in an application not handling
deadlocks or lockwaits.
The database object can't be modified to support deadlock/lockwatis...
I can only change database parameteres
Database info: Server version: 5.5.22-log Source distribution
from show engine innodb status;
{abstract}
*** (1) TRANSACTION:
TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
gap waiting
*** (2) TRANSACTION:
TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
inside InnoDB 500
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 27 row lock(s)
MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480
90.0.0.51 mario Updating
update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not
gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not
gap waiting
*** WE ROLL BACK TRANSACTION (1)
The issue is that I had a lock for over 132 seconds and the other was
waiting for 33 seconds, so I get a lockwait.
accountid is locked by a
select balance from account where accountid='3235296' lock in shared mode
How can I tell mysql to wait longer? I know the process which is doing the
deadlock, is a long balance process... I know that it takes time, sometives
over 15 minutes, but they always resolve...
How Can I tell mysql to wait for the lock as needed? like for over 12
minutes?
TIA
