公司有一个新站点，做测试总报死锁的错误 我对数据库还很陌生 在网上查了一下得出下面几组信息但是看不太明白。所以请高手帮帮忙。大家帮忙看什么原因。
mysql> show global status like "table_locks%";
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Table_locks_immediate | 14899011 |
| Table_locks_waited | 2221 |
+-----------------------+----------+
2 rows in set (0.00 sec)
mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
130801 12:53:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 40079 1_second, 40048 sleeps, 3791 10_second, 2683 background, 2683 flush
srv_master_thread log flush and writes: 42289
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 92939, signal count 455591
Mutex spin waits 2780451, rounds 6387116, OS waits 35631
RW-shared spins 268916, rounds 2237463, OS waits 24732
RW-excl spins 210585, rounds 2099959, OS waits 29258
Spin rounds per wait: 2.30 mutex, 8.32 RW-shared, 9.97 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
130801 12:44:50
*** (1) TRANSACTION:
TRANSACTION 19110DB, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 8
MySQL thread id 358431, OS thread handle 0x7fea30391700, query id 15423861 192.168.1.13 caihe_main update
INSERT INTO domain_access (nid, gid, realm) VALUES ('12877', '0', 'domain_site'), ('12877', '1', 'domain_id')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 41433 n bits 168 index "PRIMARY" of table "caihe_main_test"."domain_access" trx id 19110DB lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** (2) TRANSACTION:
TRANSACTION 1911053, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
12 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 8
MySQL thread id 358429, OS thread handle 0x7fea3028d700, query id 15423402 192.168.1.13 caihe_main update
INSERT INTO domain_access (nid, gid, realm) VALUES ('12875', '0', 'domain_site'), ('12875', '1', 'domain_id')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 41433 n bits 168 index "PRIMARY" of table "caihe_main_test"."domain_access" trx id 1911053 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 41433 n bits 168 index "PRIMARY" of table "caihe_main_test"."domain_access" trx id 1911053 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 191A334
Purge done for trx's n:o < 191A334 undo n:o < 0
History list length 2758
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 191A066, not started
MySQL thread id 359546, OS thread handle 0x7fea30454700, query id 15480087 192.168.1.109 caihe_main
---TRANSACTION 0, not started
MySQL thread id 352759, OS thread handle 0x7fea406d7700, query id 15481747 localhost kys
show engine innodb status
--------
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 (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1053214 OS file reads, 994732 OS file writes, 512817 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.25 writes/s, 0.37 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 50, seg size 52, 52055 merges
merged operations:
insert 452244, delete 2033527, delete 3652
discarded operations:
insert 0, delete 0, delete 0
Hash table size 34649, node heap has 10 buffer(s)
0.00 hash searches/s, 0.50 non-hash searches/s
---
LOG
---
Log sequence number 2519771915
Log flushed up to 2519771915
Last checkpoint at 2519771915
0 pending log writes, 0 pending chkp writes
488573 log i/o's done, 0.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17170432; in additional pool allocated 0
Dictionary memory allocated 15116248
Buffer pool size 1023
Free buffers 0
Database pages 1013
Old database pages 353
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2357540, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1136760, created 25078, written 792309
0.00 reads/s, 0.00 creates/s, 1.37 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1013, unzip_LRU len: 0
I/O sum[243]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2841, id 140643829528320, state: sleeping
Number of rows inserted 557640, updated 230514, deleted 1046823, read 305659299
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================1 row in set (0.00 sec)ERROR:
No query specified
mysql> show global status like "table_locks%";
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Table_locks_immediate | 14899011 |
| Table_locks_waited | 2221 |
+-----------------------+----------+
2 rows in set (0.00 sec)
mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
130801 12:53:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 40079 1_second, 40048 sleeps, 3791 10_second, 2683 background, 2683 flush
srv_master_thread log flush and writes: 42289
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 92939, signal count 455591
Mutex spin waits 2780451, rounds 6387116, OS waits 35631
RW-shared spins 268916, rounds 2237463, OS waits 24732
RW-excl spins 210585, rounds 2099959, OS waits 29258
Spin rounds per wait: 2.30 mutex, 8.32 RW-shared, 9.97 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
130801 12:44:50
*** (1) TRANSACTION:
TRANSACTION 19110DB, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 8
MySQL thread id 358431, OS thread handle 0x7fea30391700, query id 15423861 192.168.1.13 caihe_main update
INSERT INTO domain_access (nid, gid, realm) VALUES ('12877', '0', 'domain_site'), ('12877', '1', 'domain_id')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 41433 n bits 168 index "PRIMARY" of table "caihe_main_test"."domain_access" trx id 19110DB lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** (2) TRANSACTION:
TRANSACTION 1911053, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
12 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 8
MySQL thread id 358429, OS thread handle 0x7fea3028d700, query id 15423402 192.168.1.13 caihe_main update
INSERT INTO domain_access (nid, gid, realm) VALUES ('12875', '0', 'domain_site'), ('12875', '1', 'domain_id')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 41433 n bits 168 index "PRIMARY" of table "caihe_main_test"."domain_access" trx id 1911053 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 41433 n bits 168 index "PRIMARY" of table "caihe_main_test"."domain_access" trx id 1911053 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 191A334
Purge done for trx's n:o < 191A334 undo n:o < 0
History list length 2758
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 191A066, not started
MySQL thread id 359546, OS thread handle 0x7fea30454700, query id 15480087 192.168.1.109 caihe_main
---TRANSACTION 0, not started
MySQL thread id 352759, OS thread handle 0x7fea406d7700, query id 15481747 localhost kys
show engine innodb status
--------
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 (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1053214 OS file reads, 994732 OS file writes, 512817 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.25 writes/s, 0.37 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 50, seg size 52, 52055 merges
merged operations:
insert 452244, delete 2033527, delete 3652
discarded operations:
insert 0, delete 0, delete 0
Hash table size 34649, node heap has 10 buffer(s)
0.00 hash searches/s, 0.50 non-hash searches/s
---
LOG
---
Log sequence number 2519771915
Log flushed up to 2519771915
Last checkpoint at 2519771915
0 pending log writes, 0 pending chkp writes
488573 log i/o's done, 0.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17170432; in additional pool allocated 0
Dictionary memory allocated 15116248
Buffer pool size 1023
Free buffers 0
Database pages 1013
Old database pages 353
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2357540, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1136760, created 25078, written 792309
0.00 reads/s, 0.00 creates/s, 1.37 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1013, unzip_LRU len: 0
I/O sum[243]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2841, id 140643829528320, state: sleeping
Number of rows inserted 557640, updated 230514, deleted 1046823, read 305659299
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================1 row in set (0.00 sec)ERROR:
No query specified
把这种语句改成两条insert语句