Deadlock on UPDATE foo SET bar = ( SELECT ... )
Posted by: Jan Juricek
Date: November 04, 2008 02:24AM
Date: November 04, 2008 02:24AM
Hi all,
could somebody help me to understand following behavior?
I have three queries in this form and order:
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
)
WHERE
const_name = 'total_users';
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
AND
gender = 'f'
)
WHERE
const_name = 'total_female_users';
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
AND
gender = 'm'
)
WHERE
const_name = 'total_male_users';
---
Each is being executed in a PHP script using mysqli, each with it's own query() method.
The table `users` contains about 3169 records, first subselect (all active users) returns about 2829 of them. Second subquery (all active females) returns 1449 records, the third (all active males) 1380.
All three subqueries, when executed manually, work perfectly. The first and third query (UPDATE) works as well.
When I run the second one (update the count of all active female users), I get a deadlock for some reason:
Lock wait timeout exceeded; try restarting transaction
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
AND
gender = 'f'
)
WHERE
const_name = 'total_female_users'
On other machines, where there is lower number of records in the users table, all three queries are running without trouble, but that may not be the cause I guess.
In the SHOW INNODB STATUS (see below) I see some transaction "running" for about two hours now. That transaction is a part of my cron job, which also failed this morning with following error:
Lock wait timeout exceeded; try restarting transaction
UPDATE users SET
buddylist = '0'
WHERE
users_id = '130'
Any advice is greatly appreciated! Thank you for your time.
the INNODB status:
=====================================
081104 9:11:05 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 48 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 523242, signal count 519679
Mutex spin waits 0, rounds 31594562, OS waits 122026
RW-shared spins 996761, OS waits 309709; RW-excl spins 1326452, OS waits 57877
------------------------
LATEST FOREIGN KEY ERROR
------------------------
081103 15:14:40 Transaction:
TRANSACTION 0 26595622, ACTIVE 0 sec, process no 21028, OS thread id 1143179616 inserting, thread declared inside InnoDB 500
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 1604787, query id 63700830 localhost partyplanet update
INSERT INTO
chat_rooms_has_users
(users_id, chat_rooms_id, admin)
VALUES
(2500, 4, 0)
Foreign key constraint fails for table `partyplanet/chat_rooms_has_users`:
,
CONSTRAINT `fk_chat_rooms_has_users_chat_rooms` FOREIGN KEY (`chat_rooms_id`) REFERENCES `chat_rooms` (`chat_rooms_id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `fk_chat_rooms_has_users_chat_rooms` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 00000004; asc ;; 1: len 6; hex 0000001fc77f; asc ;;
But in parent table `partyplanet/chat_rooms`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 8; compact format; info bits 32
0: len 4; hex 00000004; asc ;; 1: len 6; hex 00000195d123; asc #;; 2: len 7; hex 00000000370c45; asc 7 E;; 3: len 2; hex 000d; asc ;; 4: len 10; hex 41706f6b616c79707361; asc Apokalypsa;; 5: len 1; hex 80; asc ;; 6: len 1; hex 80; asc ;; 7: len 20; hex c3ba706c6ec49b206d656761206e656a76c3ad63; asc pln mega nejv c;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
081104 6:56:04
*** (1) TRANSACTION:
TRANSACTION 0 29255814, ACTIVE 0 sec, process no 21028, OS thread id 1142503776 fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 368
MySQL thread id 1683550, query id 71575076 localhost partyplanet Updating
UPDATE users SET
is_new = 0
WHERE
timestamp_registered <= DATE_SUB(NOW(), INTERVAL 1 WEEK)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2515 n bits 112 index `PRIMARY` of table `partyplanet/users` trx id 0 29255814 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000086; asc ;; 1: len 6; hex 000001a008a3; asc ;; 2: len 7; hex 00000000372dbc; asc 7- ;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 6; hex 546f6d696e6f; asc Tomino;; 7: len 6; hex 746f6d696e6f; asc tomino;; 8: len 30; hex 613162306239616164643265393664383332393735376330623265336238; asc a1b0b9aadd2e96d8329757c0b2e3b8;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 18; hex 746f6d6173407669727475616c6d662e637a; asc tomas@virtualmf.cz;; 12: len 0; hex ; asc ;; 13: len 7; hex 546f6dc3a1c5a1; asc Tom ;; 14: len 7; hex 4d616cc3adc599; asc Mal ;; 15: len 1; hex 01; asc ;; 16: len 12; hex 5072c5af63686f7661203338; asc Pr chova 38;; 17: len 2; hex 000d; asc ;; 18: SQL NULL; 19: len 5; hex 3135303030; asc 15000;; 20: len 12; hex c48c7573207069c48d757321; asc us pi us!;; 21: len 30; hex 4d79736cc3ad6d2c20c5be6520736520767964c3a16d20636573746f7520; asc Mysl m, e se vyd m cestou ;...(truncated); 22: len 3; hex 8f7362; asc sb;; 23: len 1; hex 1f; asc ;; 24: len 4; hex 0000000b; asc ;; 25: len 4; hex 00000063; asc c;; 26: len 4; hex 00001041; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ad7fa8; asc H ;; 29: len 4; hex 490f1885; asc I ;; 30: len 1; hex 80; asc ;; 31: len 4; hex 490f237f; asc I # ;; 32: len 2; hex 0d8f; asc ;; 33: len 4; hex 0001373a; asc 7:;; 34: SQL NULL; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 81; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 342f332f312f; asc 4/3/1/;; 44: len 2; hex 0001; asc ;; 45: len 30; hex 3133382c3137322c3137342c3137372c3230342c3231342c3234392c3236; asc 138,172,174,177,204,214,249,26;...(truncated); 46: len 13; hex 3637332c313236322c31343830; asc 673,1262,1480;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: SQL NULL; 52: len 1; hex 80; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 29255813, ACTIVE 0 sec, process no 21028, OS thread id 1141422432 starting index read, thread declared inside InnoDB 4
mysql tables in use 7, locked 7
24 lock struct(s), heap size 6752
MySQL thread id 1683600, query id 71574910 localhost partyplanet Sending data
INSERT INTO sitemap_urls SELECT
null, CONCAT('/report/', viereplist.reports_id, '/', viereplist.report_name_seourl, '/', rephaspho.orderby, '/')
FROM
view_reports_list AS viereplist
LEFT JOIN
reports_has_photos AS rephaspho
USING(reports_id)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2515 n bits 112 index `PRIMARY` of table `partyplanet/users` trx id 0 29255813 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000086; asc ;; 1: len 6; hex 000001a008a3; asc ;; 2: len 7; hex 00000000372dbc; asc 7- ;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 6; hex 546f6d696e6f; asc Tomino;; 7: len 6; hex 746f6d696e6f; asc tomino;; 8: len 30; hex 613162306239616164643265393664383332393735376330623265336238; asc a1b0b9aadd2e96d8329757c0b2e3b8;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 18; hex 746f6d6173407669727475616c6d662e637a; asc tomas@virtualmf.cz;; 12: len 0; hex ; asc ;; 13: len 7; hex 546f6dc3a1c5a1; asc Tom ;; 14: len 7; hex 4d616cc3adc599; asc Mal ;; 15: len 1; hex 01; asc ;; 16: len 12; hex 5072c5af63686f7661203338; asc Pr chova 38;; 17: len 2; hex 000d; asc ;; 18: SQL NULL; 19: len 5; hex 3135303030; asc 15000;; 20: len 12; hex c48c7573207069c48d757321; asc us pi us!;; 21: len 30; hex 4d79736cc3ad6d2c20c5be6520736520767964c3a16d20636573746f7520; asc Mysl m, e se vyd m cestou ;...(truncated); 22: len 3; hex 8f7362; asc sb;; 23: len 1; hex 1f; asc ;; 24: len 4; hex 0000000b; asc ;; 25: len 4; hex 00000063; asc c;; 26: len 4; hex 00001041; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ad7fa8; asc H ;; 29: len 4; hex 490f1885; asc I ;; 30: len 1; hex 80; asc ;; 31: len 4; hex 490f237f; asc I # ;; 32: len 2; hex 0d8f; asc ;; 33: len 4; hex 0001373a; asc 7:;; 34: SQL NULL; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 81; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 342f332f312f; asc 4/3/1/;; 44: len 2; hex 0001; asc ;; 45: len 30; hex 3133382c3137322c3137342c3137372c3230342c3231342c3234392c3236; asc 138,172,174,177,204,214,249,26;...(truncated); 46: len 13; hex 3637332c313236322c31343830; asc 673,1262,1480;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: SQL NULL; 52: len 1; hex 80; asc ;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 00008e; asc ;; 1: len 6; hex 0000019fd6d3; asc ;; 2: len 7; hex 0000000035215a; asc 5!Z;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 9; hex 4875676f5f4a75676f; asc Hugo_Jugo;; 7: len 9; hex 6875676f5f6a75676f; asc hugo_jugo;; 8: len 30; hex 656236306465643263363736336462393061326163343833356639343765; asc eb60ded2c6763db90a2ac4835f947e;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 23; hex 746f6d61732e6a75726963656b40676d61696c2e636f6d; asc tomas.juricek@gmail.com;; 12: len 0; hex ; asc ;; 13: len 7; hex 546f6dc3a1c5a1; asc Tom ;; 14: len 10; hex 4a75c599c3adc48d656b; asc Ju ek;; 15: len 1; hex 01; asc ;; 16: len 20; hex 54c5992e20452e2042656e65c5a1652031353634; asc T . E. Bene e 1564;; 17: len 2; hex 003b; asc ;;; 18: SQL NULL; 19: len 5; hex 3530303132; asc 50012;; 20: SQL NULL; 21: len 30; hex 4f7074696d69736d7573206a65207a70c5af736f62656e206e65646f7374; asc Optimismus je zp soben nedost;...(truncated); 22: len 3; hex 8f7f3a; asc :;; 23: len 1; hex 19; asc ;; 24: len 4; hex 00000002; asc ;; 25: len 4; hex 00000014; asc ;; 26: len 4; hex 00002041; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48b698a4; asc H ;; 29: len 4; hex 490f4a06; asc I J ;; 30: len 1; hex 80; asc ;; 31: len 4; hex 490f4cbb; asc I L ;; 32: len 2; hex 007b; asc {;; 33: len 4; hex 003229af; asc 2) ;; 34: SQL NULL; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 80; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 322f342f312f; asc 2/4/1/;; 44: SQL NULL; 45: len 3; hex 363630; asc 660;; 46: len 1; hex 30; asc 0;; 47: len 30; hex 343630326436393163343232386533356532633037313832613131333837; asc 4602d691c4228e35e2c07182a11387;...(truncated); 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: len 4; hex 4904538a; asc I S ;; 52: len 1; hex 80; asc ;;
Record lock, heap no 29 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000089; asc ;; 1: len 6; hex 0000018e16d4; asc ;; 2: len 7; hex 00000000352ada; asc 5* ;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 14; hex 6368756a2e61726d7374726f6e67; asc chuj.armstrong;; 7: len 14; hex 6368756a2e61726d7374726f6e67; asc chuj.armstrong;; 8: len 30; hex 386131653638316563636465386635626237656265343033666238653832; asc 8a1e681eccde8f5bb7ebe403fb8e82;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 24; hex 6368756a2e61726d7374726f6e674073657a6e616d2e637a; asc chuj.armstrong@seznam.cz;; 12: len 0; hex ; asc ;; 13: len 4; hex 4368756a; asc Chuj;; 14: len 9; hex 41726d7374726f6e67; asc Armstrong;; 15: len 1; hex 01; asc ;; 16: len 20; hex 55204b756e72617469636bc3a9686f206c657361; asc U Kunratick ho lesa;; 17: len 2; hex 000c; asc ;; 18: SQL NULL; 19: len 5; hex 3134383030; asc 14800;; 20: len 18; hex 5a6475c599207476756a206368756a2e2e2e; asc Zdu tvuj chuj...;; 21: len 30; hex 224e6576c49b64c49b6c612c206a616b207679706164c3a12c206a656c69; asc "Nev d la, jak vypad , jeli;...(truncated); 22: len 3; hex 8f5e51; asc ^Q;; 23: len 1; hex 29; asc );; 24: len 4; hex 00000000; asc ;; 25: len 4; hex 00000000; asc ;; 26: len 4; hex 00000000; asc ;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ae9000; asc H ;; 29: len 4; hex 490eb64f; asc I O;; 30: len 1; hex 80; asc ;; 31: SQL NULL; 32: len 2; hex 0072; asc r;; 33: len 4; hex 0002e146; asc F;; 34: len 3; hex 8fb19f; asc ;; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 80; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 372f332f312f; asc 7/3/1/;; 44: SQL NULL; 45: len 19; hex 3133342c3137352c3137362c3139352c343035; asc 134,175,176,195,405;; 46: len 1; hex 30; asc 0;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: SQL NULL; 52: len 1; hex 80; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2515 n bits 112 index `PRIMARY` of table `partyplanet/users` trx id 0 29255813 lock mode S locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000083; asc ;; 1: len 6; hex 0000018e9d86; asc ;; 2: len 7; hex 00000000352f79; asc 5/y;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 6; hex 73616d75656c; asc samuel;; 7: len 6; hex 73616d75656c; asc samuel;; 8: len 30; hex 616638346439316664653136383536366337646331386633313231656132; asc af84d91fde168566c7dc18f3121ea2;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 18; hex 70616e6c756b61734073657a6e616d2e637a; asc panlukas@seznam.cz;; 12: len 0; hex ; asc ;; 13: len 7; hex 42756bc3a1c5a1; asc Buk ;; 14: len 10; hex 42756b616e6f76736b69; asc Bukanovski;; 15: len 1; hex 01; asc ;; 16: len 18; hex 5672c5a16f7669636bc3a1203135362f3233; asc Vr ovick 156/23;; 17: len 2; hex 0012; asc ;; 18: SQL NULL; 19: len 5; hex 3131303030; asc 11000;; 20: len 26; hex 53616d207620746f6d206e656ec3ad206e696b64792073c3a16d; asc Sam v tom nen nikdy s m;; 21: len 30; hex 4a73656d207a6b72c3a1746b61206e61206b72c3a1746b6f206b72c3a16a; asc Jsem zkr tka na kr tko kr j;...(truncated); 22: len 3; hex 8f8125; asc %;; 23: len 1; hex 18; asc ;; 24: len 4; hex 00000006; asc ;; 25: len 4; hex 00000032; asc 2;; 26: len 4; hex cdcc0441; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ad7e04; asc H ~ ;; 29: len 4; hex 48f5ffb4; asc H ;; 30: len 1; hex 80; asc ;; 31: SQL NULL; 32: len 2; hex 00c6; asc ;; 33: len 4; hex 000008c8; asc ;; 34: SQL NULL; 35: len 2; hex 0032; asc 2;; 36: len 1; hex 80; asc ;; 37: len 1; hex 80; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 312f332f312f; asc 1/3/1/;; 44: len 2; hex 0002; asc ;; 45: len 30; hex 3133322c3133342c3133362c3133382c3134332c3137322c3137342c3137; asc 132,134,136,138,143,172,174,17;...(truncated); 46: len 1; hex 30; asc 0;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: len 4; hex 48e08e2c; asc H ,;; 52: len 1; hex 80; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 29276902
Purge done for trx's n:o < 0 29256212 undo n:o < 0 0
History list length 1646
Total number of lock structs in row lock hash table 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 29276901, not started, process no 21028, OS thread id 1141963104
MySQL thread id 1685239, query id 71640312 localhost partyplanet
SHOW innodb status
---TRANSACTION 0 29256209, ACTIVE 8101 sec, process no 21028, OS thread id 1142503776
2 lock struct(s), heap size 368
MySQL thread id 1683550, query id 71578228 localhost partyplanet
Trx read view will not see trx with id >= 0 29256210, sees < 0 29256210
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6624538 OS file reads, 9882772 OS file writes, 7588971 OS fsyncs
2.52 reads/s, 20175 avg bytes/read, 2.10 writes/s, 1.52 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
119205 inserts, 119205 merged recs, 61963 merges
Hash table size 17393, used cells 16440, node heap has 76 buffer(s)
1679.90 hash searches/s, 748.73 non-hash searches/s
---
LOG
---
Log sequence number 1 1911581470
Log flushed up to 1 1911581470
Last checkpoint at 1 1911575177
0 pending log writes, 0 pending chkp writes
7135710 log i/o's done, 1.40 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 23593282; in additional pool allocated 1045504
Buffer pool size 512
Free buffers 0
Database pages 436
Modified db pages 10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 7586217, created 169575, written 3111457
3.10 reads/s, 0.02 creates/s, 0.71 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 21028, id 1140881760, state: sleeping
Number of rows inserted 18806238, updated 3378310, deleted 943516, read 22044293629
0.12 inserts/s, 0.46 updates/s, 0.17 deletes/s, 6685.32 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
could somebody help me to understand following behavior?
I have three queries in this form and order:
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
)
WHERE
const_name = 'total_users';
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
AND
gender = 'f'
)
WHERE
const_name = 'total_female_users';
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
AND
gender = 'm'
)
WHERE
const_name = 'total_male_users';
---
Each is being executed in a PHP script using mysqli, each with it's own query() method.
The table `users` contains about 3169 records, first subselect (all active users) returns about 2829 of them. Second subquery (all active females) returns 1449 records, the third (all active males) 1380.
All three subqueries, when executed manually, work perfectly. The first and third query (UPDATE) works as well.
When I run the second one (update the count of all active female users), I get a deadlock for some reason:
Lock wait timeout exceeded; try restarting transaction
UPDATE cache_constants SET
const_value = (
SELECT
COUNT(users_id)
FROM
users
WHERE
account_status IN ('active', 'banished')
AND
gender = 'f'
)
WHERE
const_name = 'total_female_users'
On other machines, where there is lower number of records in the users table, all three queries are running without trouble, but that may not be the cause I guess.
In the SHOW INNODB STATUS (see below) I see some transaction "running" for about two hours now. That transaction is a part of my cron job, which also failed this morning with following error:
Lock wait timeout exceeded; try restarting transaction
UPDATE users SET
buddylist = '0'
WHERE
users_id = '130'
Any advice is greatly appreciated! Thank you for your time.
the INNODB status:
=====================================
081104 9:11:05 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 48 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 523242, signal count 519679
Mutex spin waits 0, rounds 31594562, OS waits 122026
RW-shared spins 996761, OS waits 309709; RW-excl spins 1326452, OS waits 57877
------------------------
LATEST FOREIGN KEY ERROR
------------------------
081103 15:14:40 Transaction:
TRANSACTION 0 26595622, ACTIVE 0 sec, process no 21028, OS thread id 1143179616 inserting, thread declared inside InnoDB 500
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 1604787, query id 63700830 localhost partyplanet update
INSERT INTO
chat_rooms_has_users
(users_id, chat_rooms_id, admin)
VALUES
(2500, 4, 0)
Foreign key constraint fails for table `partyplanet/chat_rooms_has_users`:
,
CONSTRAINT `fk_chat_rooms_has_users_chat_rooms` FOREIGN KEY (`chat_rooms_id`) REFERENCES `chat_rooms` (`chat_rooms_id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `fk_chat_rooms_has_users_chat_rooms` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 00000004; asc ;; 1: len 6; hex 0000001fc77f; asc ;;
But in parent table `partyplanet/chat_rooms`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 8; compact format; info bits 32
0: len 4; hex 00000004; asc ;; 1: len 6; hex 00000195d123; asc #;; 2: len 7; hex 00000000370c45; asc 7 E;; 3: len 2; hex 000d; asc ;; 4: len 10; hex 41706f6b616c79707361; asc Apokalypsa;; 5: len 1; hex 80; asc ;; 6: len 1; hex 80; asc ;; 7: len 20; hex c3ba706c6ec49b206d656761206e656a76c3ad63; asc pln mega nejv c;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
081104 6:56:04
*** (1) TRANSACTION:
TRANSACTION 0 29255814, ACTIVE 0 sec, process no 21028, OS thread id 1142503776 fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 368
MySQL thread id 1683550, query id 71575076 localhost partyplanet Updating
UPDATE users SET
is_new = 0
WHERE
timestamp_registered <= DATE_SUB(NOW(), INTERVAL 1 WEEK)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2515 n bits 112 index `PRIMARY` of table `partyplanet/users` trx id 0 29255814 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000086; asc ;; 1: len 6; hex 000001a008a3; asc ;; 2: len 7; hex 00000000372dbc; asc 7- ;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 6; hex 546f6d696e6f; asc Tomino;; 7: len 6; hex 746f6d696e6f; asc tomino;; 8: len 30; hex 613162306239616164643265393664383332393735376330623265336238; asc a1b0b9aadd2e96d8329757c0b2e3b8;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 18; hex 746f6d6173407669727475616c6d662e637a; asc tomas@virtualmf.cz;; 12: len 0; hex ; asc ;; 13: len 7; hex 546f6dc3a1c5a1; asc Tom ;; 14: len 7; hex 4d616cc3adc599; asc Mal ;; 15: len 1; hex 01; asc ;; 16: len 12; hex 5072c5af63686f7661203338; asc Pr chova 38;; 17: len 2; hex 000d; asc ;; 18: SQL NULL; 19: len 5; hex 3135303030; asc 15000;; 20: len 12; hex c48c7573207069c48d757321; asc us pi us!;; 21: len 30; hex 4d79736cc3ad6d2c20c5be6520736520767964c3a16d20636573746f7520; asc Mysl m, e se vyd m cestou ;...(truncated); 22: len 3; hex 8f7362; asc sb;; 23: len 1; hex 1f; asc ;; 24: len 4; hex 0000000b; asc ;; 25: len 4; hex 00000063; asc c;; 26: len 4; hex 00001041; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ad7fa8; asc H ;; 29: len 4; hex 490f1885; asc I ;; 30: len 1; hex 80; asc ;; 31: len 4; hex 490f237f; asc I # ;; 32: len 2; hex 0d8f; asc ;; 33: len 4; hex 0001373a; asc 7:;; 34: SQL NULL; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 81; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 342f332f312f; asc 4/3/1/;; 44: len 2; hex 0001; asc ;; 45: len 30; hex 3133382c3137322c3137342c3137372c3230342c3231342c3234392c3236; asc 138,172,174,177,204,214,249,26;...(truncated); 46: len 13; hex 3637332c313236322c31343830; asc 673,1262,1480;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: SQL NULL; 52: len 1; hex 80; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 29255813, ACTIVE 0 sec, process no 21028, OS thread id 1141422432 starting index read, thread declared inside InnoDB 4
mysql tables in use 7, locked 7
24 lock struct(s), heap size 6752
MySQL thread id 1683600, query id 71574910 localhost partyplanet Sending data
INSERT INTO sitemap_urls SELECT
null, CONCAT('/report/', viereplist.reports_id, '/', viereplist.report_name_seourl, '/', rephaspho.orderby, '/')
FROM
view_reports_list AS viereplist
LEFT JOIN
reports_has_photos AS rephaspho
USING(reports_id)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2515 n bits 112 index `PRIMARY` of table `partyplanet/users` trx id 0 29255813 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000086; asc ;; 1: len 6; hex 000001a008a3; asc ;; 2: len 7; hex 00000000372dbc; asc 7- ;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 6; hex 546f6d696e6f; asc Tomino;; 7: len 6; hex 746f6d696e6f; asc tomino;; 8: len 30; hex 613162306239616164643265393664383332393735376330623265336238; asc a1b0b9aadd2e96d8329757c0b2e3b8;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 18; hex 746f6d6173407669727475616c6d662e637a; asc tomas@virtualmf.cz;; 12: len 0; hex ; asc ;; 13: len 7; hex 546f6dc3a1c5a1; asc Tom ;; 14: len 7; hex 4d616cc3adc599; asc Mal ;; 15: len 1; hex 01; asc ;; 16: len 12; hex 5072c5af63686f7661203338; asc Pr chova 38;; 17: len 2; hex 000d; asc ;; 18: SQL NULL; 19: len 5; hex 3135303030; asc 15000;; 20: len 12; hex c48c7573207069c48d757321; asc us pi us!;; 21: len 30; hex 4d79736cc3ad6d2c20c5be6520736520767964c3a16d20636573746f7520; asc Mysl m, e se vyd m cestou ;...(truncated); 22: len 3; hex 8f7362; asc sb;; 23: len 1; hex 1f; asc ;; 24: len 4; hex 0000000b; asc ;; 25: len 4; hex 00000063; asc c;; 26: len 4; hex 00001041; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ad7fa8; asc H ;; 29: len 4; hex 490f1885; asc I ;; 30: len 1; hex 80; asc ;; 31: len 4; hex 490f237f; asc I # ;; 32: len 2; hex 0d8f; asc ;; 33: len 4; hex 0001373a; asc 7:;; 34: SQL NULL; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 81; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 342f332f312f; asc 4/3/1/;; 44: len 2; hex 0001; asc ;; 45: len 30; hex 3133382c3137322c3137342c3137372c3230342c3231342c3234392c3236; asc 138,172,174,177,204,214,249,26;...(truncated); 46: len 13; hex 3637332c313236322c31343830; asc 673,1262,1480;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: SQL NULL; 52: len 1; hex 80; asc ;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 00008e; asc ;; 1: len 6; hex 0000019fd6d3; asc ;; 2: len 7; hex 0000000035215a; asc 5!Z;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 9; hex 4875676f5f4a75676f; asc Hugo_Jugo;; 7: len 9; hex 6875676f5f6a75676f; asc hugo_jugo;; 8: len 30; hex 656236306465643263363736336462393061326163343833356639343765; asc eb60ded2c6763db90a2ac4835f947e;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 23; hex 746f6d61732e6a75726963656b40676d61696c2e636f6d; asc tomas.juricek@gmail.com;; 12: len 0; hex ; asc ;; 13: len 7; hex 546f6dc3a1c5a1; asc Tom ;; 14: len 10; hex 4a75c599c3adc48d656b; asc Ju ek;; 15: len 1; hex 01; asc ;; 16: len 20; hex 54c5992e20452e2042656e65c5a1652031353634; asc T . E. Bene e 1564;; 17: len 2; hex 003b; asc ;;; 18: SQL NULL; 19: len 5; hex 3530303132; asc 50012;; 20: SQL NULL; 21: len 30; hex 4f7074696d69736d7573206a65207a70c5af736f62656e206e65646f7374; asc Optimismus je zp soben nedost;...(truncated); 22: len 3; hex 8f7f3a; asc :;; 23: len 1; hex 19; asc ;; 24: len 4; hex 00000002; asc ;; 25: len 4; hex 00000014; asc ;; 26: len 4; hex 00002041; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48b698a4; asc H ;; 29: len 4; hex 490f4a06; asc I J ;; 30: len 1; hex 80; asc ;; 31: len 4; hex 490f4cbb; asc I L ;; 32: len 2; hex 007b; asc {;; 33: len 4; hex 003229af; asc 2) ;; 34: SQL NULL; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 80; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 322f342f312f; asc 2/4/1/;; 44: SQL NULL; 45: len 3; hex 363630; asc 660;; 46: len 1; hex 30; asc 0;; 47: len 30; hex 343630326436393163343232386533356532633037313832613131333837; asc 4602d691c4228e35e2c07182a11387;...(truncated); 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: len 4; hex 4904538a; asc I S ;; 52: len 1; hex 80; asc ;;
Record lock, heap no 29 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000089; asc ;; 1: len 6; hex 0000018e16d4; asc ;; 2: len 7; hex 00000000352ada; asc 5* ;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 14; hex 6368756a2e61726d7374726f6e67; asc chuj.armstrong;; 7: len 14; hex 6368756a2e61726d7374726f6e67; asc chuj.armstrong;; 8: len 30; hex 386131653638316563636465386635626237656265343033666238653832; asc 8a1e681eccde8f5bb7ebe403fb8e82;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 24; hex 6368756a2e61726d7374726f6e674073657a6e616d2e637a; asc chuj.armstrong@seznam.cz;; 12: len 0; hex ; asc ;; 13: len 4; hex 4368756a; asc Chuj;; 14: len 9; hex 41726d7374726f6e67; asc Armstrong;; 15: len 1; hex 01; asc ;; 16: len 20; hex 55204b756e72617469636bc3a9686f206c657361; asc U Kunratick ho lesa;; 17: len 2; hex 000c; asc ;; 18: SQL NULL; 19: len 5; hex 3134383030; asc 14800;; 20: len 18; hex 5a6475c599207476756a206368756a2e2e2e; asc Zdu tvuj chuj...;; 21: len 30; hex 224e6576c49b64c49b6c612c206a616b207679706164c3a12c206a656c69; asc "Nev d la, jak vypad , jeli;...(truncated); 22: len 3; hex 8f5e51; asc ^Q;; 23: len 1; hex 29; asc );; 24: len 4; hex 00000000; asc ;; 25: len 4; hex 00000000; asc ;; 26: len 4; hex 00000000; asc ;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ae9000; asc H ;; 29: len 4; hex 490eb64f; asc I O;; 30: len 1; hex 80; asc ;; 31: SQL NULL; 32: len 2; hex 0072; asc r;; 33: len 4; hex 0002e146; asc F;; 34: len 3; hex 8fb19f; asc ;; 35: len 2; hex 0000; asc ;; 36: len 1; hex 80; asc ;; 37: len 1; hex 80; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 372f332f312f; asc 7/3/1/;; 44: SQL NULL; 45: len 19; hex 3133342c3137352c3137362c3139352c343035; asc 134,175,176,195,405;; 46: len 1; hex 30; asc 0;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: SQL NULL; 52: len 1; hex 80; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2515 n bits 112 index `PRIMARY` of table `partyplanet/users` trx id 0 29255813 lock mode S locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 53; compact format; info bits 0
0: len 3; hex 000083; asc ;; 1: len 6; hex 0000018e9d86; asc ;; 2: len 7; hex 00000000352f79; asc 5/y;; 3: SQL NULL; 4: len 1; hex 02; asc ;; 5: SQL NULL; 6: len 6; hex 73616d75656c; asc samuel;; 7: len 6; hex 73616d75656c; asc samuel;; 8: len 30; hex 616638346439316664653136383536366337646331386633313231656132; asc af84d91fde168566c7dc18f3121ea2;...(truncated); 9: SQL NULL; 10: len 1; hex 80; asc ;; 11: len 18; hex 70616e6c756b61734073657a6e616d2e637a; asc panlukas@seznam.cz;; 12: len 0; hex ; asc ;; 13: len 7; hex 42756bc3a1c5a1; asc Buk ;; 14: len 10; hex 42756b616e6f76736b69; asc Bukanovski;; 15: len 1; hex 01; asc ;; 16: len 18; hex 5672c5a16f7669636bc3a1203135362f3233; asc Vr ovick 156/23;; 17: len 2; hex 0012; asc ;; 18: SQL NULL; 19: len 5; hex 3131303030; asc 11000;; 20: len 26; hex 53616d207620746f6d206e656ec3ad206e696b64792073c3a16d; asc Sam v tom nen nikdy s m;; 21: len 30; hex 4a73656d207a6b72c3a1746b61206e61206b72c3a1746b6f206b72c3a16a; asc Jsem zkr tka na kr tko kr j;...(truncated); 22: len 3; hex 8f8125; asc %;; 23: len 1; hex 18; asc ;; 24: len 4; hex 00000006; asc ;; 25: len 4; hex 00000032; asc 2;; 26: len 4; hex cdcc0441; asc A;; 27: len 4; hex 00000000; asc ;; 28: len 4; hex 48ad7e04; asc H ~ ;; 29: len 4; hex 48f5ffb4; asc H ;; 30: len 1; hex 80; asc ;; 31: SQL NULL; 32: len 2; hex 00c6; asc ;; 33: len 4; hex 000008c8; asc ;; 34: SQL NULL; 35: len 2; hex 0032; asc 2;; 36: len 1; hex 80; asc ;; 37: len 1; hex 80; asc ;; 38: SQL NULL; 39: len 1; hex 80; asc ;; 40: len 1; hex 80; asc ;; 41: len 1; hex 81; asc ;; 42: len 1; hex 80; asc ;; 43: len 6; hex 312f332f312f; asc 1/3/1/;; 44: len 2; hex 0002; asc ;; 45: len 30; hex 3133322c3133342c3133362c3133382c3134332c3137322c3137342c3137; asc 132,134,136,138,143,172,174,17;...(truncated); 46: len 1; hex 30; asc 0;; 47: SQL NULL; 48: SQL NULL; 49: SQL NULL; 50: SQL NULL; 51: len 4; hex 48e08e2c; asc H ,;; 52: len 1; hex 80; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 29276902
Purge done for trx's n:o < 0 29256212 undo n:o < 0 0
History list length 1646
Total number of lock structs in row lock hash table 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 29276901, not started, process no 21028, OS thread id 1141963104
MySQL thread id 1685239, query id 71640312 localhost partyplanet
SHOW innodb status
---TRANSACTION 0 29256209, ACTIVE 8101 sec, process no 21028, OS thread id 1142503776
2 lock struct(s), heap size 368
MySQL thread id 1683550, query id 71578228 localhost partyplanet
Trx read view will not see trx with id >= 0 29256210, sees < 0 29256210
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6624538 OS file reads, 9882772 OS file writes, 7588971 OS fsyncs
2.52 reads/s, 20175 avg bytes/read, 2.10 writes/s, 1.52 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
119205 inserts, 119205 merged recs, 61963 merges
Hash table size 17393, used cells 16440, node heap has 76 buffer(s)
1679.90 hash searches/s, 748.73 non-hash searches/s
---
LOG
---
Log sequence number 1 1911581470
Log flushed up to 1 1911581470
Last checkpoint at 1 1911575177
0 pending log writes, 0 pending chkp writes
7135710 log i/o's done, 1.40 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 23593282; in additional pool allocated 1045504
Buffer pool size 512
Free buffers 0
Database pages 436
Modified db pages 10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 7586217, created 169575, written 3111457
3.10 reads/s, 0.02 creates/s, 0.71 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 21028, id 1140881760, state: sleeping
Number of rows inserted 18806238, updated 3378310, deleted 943516, read 22044293629
0.12 inserts/s, 0.46 updates/s, 0.17 deletes/s, 6685.32 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.