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