List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:May 24 2011 1:55pm
Subject:Re: After long semaphore waits MySQL becomes unresponsive.
View as plain text  
> > I may be wrong here, but I tend to interpret this as '140054029002496' is
> > trying to get an exclusive lock on 0x78733f8, on which it already has an
> > exclusive lock, and hence is deadlocked in some manner.  Am I right
> > there? How can this happen?
> 
> I'm not too hot on the internals, but yes, that seems likely.
> 
> > I certainly cannot reproduce a query which causes this,
> 
> You'd need at least two :-p

2 simultaneous queries with the same thread-id? Is that possible? 

> > and I had to kill -9
> > the process, so nothing no running/long queries were written to the
> > slow-query log. (On a side note: not even root / superuser could connect
> > to the MySQL
> 
> check the max_user_connections setting, and set it a couple of notches
> lower than the max_connections one. It basically says "only this much
> non-super users may connect" and leaves the rest for super privileged
> users - which should only be admins, not applications.

I always thought (based on http://dev.mysql.com/doc/refman/5.5/en/too-many-
connections.html) I'd get 1 super-connection without question, and I didn't 
get any 'too many connections' errors, but timeouts on connecting. But let's 
say that in this case (MySQL needing a kill -9) all bets about proper 
connecting are sort of off ;) ). Nevertheless, according to the manual, 
max_user_connections configures "The maximum number of simultaneous 
connections permitted to any given MySQL user account.", which would mean (and 
a test shows) it is per-user name, and it would only work if we have only 1 
non-admin/non-super-user, while in reality, we have several users with 
specific privileges. Not an incredible lot, but about 20 nonetheless, and the 
only way I see your suggestion working is by allowing all users a 
max_user_connections of  ((max_connections/number of users) - 1), which isn't 
really what we want (it severely limits the maximum for a specific user far 
below the max-connections actually possible).
 
> > instance, so there was no way to check which queries were actually
> > running) If not, what should I look for in trying to determine the
> > cause? (Added some extra monitor output below sig in case it's needed).
> 
> Well... Your innodb status, if you can connect :-)

Ack, I assume this means the 'SHOW ENGINE INNODB STATUS;' at the moment of the 
problem, rather then just the output I get now?

> Can't be bothered to write down the reasoning, but the simple way to avoid
> deadlocks is to always, in all processes, lock all tables in the same
> order - alphabetically, for instance. that way deadlock gets pre-empted
> before it can occur.

Hmm, we're never using explicit (table) locks, only implicit by the innodb-
transaction, ans usually even then the transaction is just the single 
statement (autocommit is usually on save for a few instances). Does order in 
joins matter in terms of setting them alphabetically? That would mean checking 
& rewriting a heck of a lot of codebase... Also, taking for instance the 
latest detected deadlock from the innodb status, I don't know how to actually 
prevent it on an SQL-query level:
------------------------
LATEST DETECTED DEADLOCK
------------------------
110524  1:30:47
*** (1) TRANSACTION:
TRANSACTION CE26925, ACTIVE 0 sec, process no 11546, OS thread id 
139920988698368 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 3112, 43 row lock(s), undo log entries 
37
MySQL thread id 1022959, query id 5537985 192.168.20.7 cronscripts Updating
UPDATE connectie SET tracker=2, smsNotice=1 WHERE tracker<>0 and id IN 
(5384,6276,5043,6969,5313,6155,6516,2017,6514,2711,9252,2848,5226,7178,7227,7228,5627,5224,48331,5250,33201,4650,5618,9141,9140,7226,5590,5810,22011,8312,5634,5591,9242,9259,50341,30151,5442,3743,38341,6864,6274,1065,1081,7214,5332,5654,6141,1613,47433,4183,8042,7971,5143,6859,50001,6131,5388,6097,7176,7317,9204,19051,2280,6085,8656,3575,7983,9248,2846,3365,5822,5725,37741,9230,6320,9227,20191,6988,5126,30691,5374,5378,7250,4458,6026,8107,51651,3904,39982,7126,5318,7181,7940,8327,4251,4319,9041,4447,7394,5314,6439,5978,5979,4662,38861,17121,6403,6004,6088,6987,6019,6078,6759,5813,6964,5570,6322,9671,7415,7969,6775,21881,38331,6151,6713,7944,6606,6245,6767,6726,6980,31131,6479,36411,22001,47033,7407,6821,7234,9027,7945,7398,7421,9118,12791,6903,7229,18071,7395,7169,7422,7269,35601,14821,8877,8927,8928,15971,8302,35291,8924,8733,17891,34201,8639,9501,8630,8965,16021
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 987 page no 53 n bits 328 index `PRIMARY` of table 
`tname`.`connectie` trx id CE26925 lock_mode X locks rec but not gap waiting
Record lock, heap no 47 PHYSICAL RECORD: n_fields 12; compact format; info 
bits 0
 0: len 4; hex 800016b2; asc     ;;
 1: len 6; hex 00000c9a84b4; asc       ;;
 2: len 7; hex 2e000009722a52; asc .   r*R;;
 3: len 4; hex 80000010; asc     ;;
 4: len 9; hex 70697a7a6174657374; asc rtttest;;
 5: len 9; hex 70697a7a6174657374; asc rtttest;;
 6: len 3; hex 333839; asc 389;;
 7: len 4; hex 80000003; asc     ;;
 8: len 4; hex 80000000; asc     ;;
 9: len 4; hex 80000000; asc     ;;
 10: len 4; hex 80000000; asc     ;;
 11: len 11; hex 76657273696f6e20322e30; asc version 2.0;;

*** (2) TRANSACTION:
TRANSACTION CE26923, ACTIVE 0 sec, process no 11546, OS thread id 
139921021904640 starting index read, thread declared inside InnoDB 362
mysql tables in use 2, locked 2
81 lock struct(s), heap size 14776, 2663 row lock(s)
MySQL thread id 1022784, query id 5537983 46.144.148.174 utr-fax Sending data
UPDATE
                                        `order` o,
                                        connectie c
                                SET
                                        o.datum=o.datum,
                                        o.bcDeviceId=11,
                                        o.bcStatus= IF(o.bcStatus = 9, 10, 8),
                                        o.bcInBehandeling='2011-05-24 
01:30:47'
                                WHERE
                                        o.order_statusOrder_status_id=2
                                AND o.connectieId=c.id
                                AND (c.bellen=2 OR c.bellen=3)
                                AND o.bcDeviceId=0
                                AND ( o.bcStatus=0 OR o.bcStatus=9 )
                                AND     o.bevestigingsId=0
                                AND     o.datum >= '2011-05-23 01:30:47'
                                AND o.datum < '2011-05-24 01:30:37'
*** (2) HOLDS THE LOCK(S):
<SNIP LIST OF LOCKS>

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 987 page no 48 n bits 328 index `PRIMARY` of table 
`tname`.`connectie` trx id CE26923 lock mode S locks rec but not gap waiting
Record lock, heap no 241 PHYSICAL RECORD: n_fields 12; compact format; info 
bits 0
 0: len 4; hex 8000146a; asc    j;;
 1: len 6; hex 00000ce26925; asc     i%;;
 2: len 7; hex 5c00001ba11c2a; asc \     *;;
 3: len 4; hex 80000010; asc     ;;
 4: len 9; hex 70697a7a6174657374; asc rtttest;;
 5: len 9; hex 70697a7a6174657374; asc rtttest;;
 6: len 3; hex 333638; asc 368;;
 7: len 4; hex 80000002; asc     ;;
 8: len 4; hex 80000000; asc     ;;
 9: len 4; hex 80000000; asc     ;;
 10: len 4; hex 80000001; asc     ;;
 11: len 11; hex 76657273696f6e20322e30; asc version 2.0;;

*** WE ROLL BACK TRANSACTION (1)

Thanks,
-- 
Rik Wasmus




Thread
After long semaphore waits MySQL becomes unresponsive.rik24 May
  • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
    • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus24 May
      • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
        • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus24 May
          • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
            • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus25 May
              • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman25 May
                • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus25 May
                  • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman25 May