> > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
