mysql not in 超时_mysql因为锁等待导致导入用户失败(Lock wait timeout exceeded; try restarting transaction) | 学步园...

Lock wait timeout exceeded; try restarting transaction

错误信息：

2012-06-20 10:49:10,310 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1205, SQLState: 41000

2012-06-20 10:49:10,310 ERROR [org.hibernate.util.JDBCExceptionReporter] - Lock wait timeout exceeded; try restarting transaction

2012-06-20 10:49:10,316 DEBUG [com.wondertek.meeting.action.user.UserImportAction] - 导入失败,具体原因如下：Hibernate operation: could not insert:

[com.wondertek.meeting.model.User]; SQL [insert into user (mobile, password, name, state, create_time, operators) values (?, ?, ?, ?, ?, ?)];

Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

2012-06-20 10:49:10,316 DEBUG [com.wondertek.meeting.action.user.UserImportAction] - 导入用户共花费53秒

2012-06-20 10:49:10,317 DEBUG [org.apache.struts2.dispatcher.ServletDispatcherResult] - Forwarding to location /pages/admin/pri/import/user_import_result.jsp

2012-06-20 10:49:10,318 INFO [org.apache.struts2.interceptor.FileUploadInterceptor] - Removing file upload /wd/tomcat6/work/Catalina/localhost/meeting/upload__66a31c3a_137ea7c3ad2__8000_00002222.tmp

查看状态：

1.查看information_shcema的表信息来查看。

2. mysql> SHOW ENGINE INNODB STATUS\G;

如：

mysql> SHOW ENGINE INNODB STATUS\G;

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

120620 13:19:55 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 54 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 3426981 1_second, 3426972 sleeps, 341143 10_second, 15934 background, 15784 flush

srv_master_thread log flush and writes: 3428150

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 86616, signal count 85375

Mutex spin waits 34309, rounds 1034394, OS waits 1556

RW-shared spins 82325, rounds 2469556, OS waits 78033

RW-excl spins 668, rounds 210810, OS waits 6658

Spin rounds per wait: 30.15 mutex, 30.00 RW-shared, 315.58 RW-excl

------------

TRANSACTIONS

------------

Trx id counter 4F5748

Purge done for trx's n:o < 4F4DEB undo n:o < 0

History list length 2567

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 20185, OS thread handle 0x4b138940, query id 24606241 localhost root

SHOW ENGINE INNODB STATUS

---TRANSACTION 4F5721, not started

MySQL thread id 20226, OS thread handle 0x402c6940, query id 24606002 172.16.1.69 meeting

---TRANSACTION 4F5747, not started

MySQL thread id 20227, OS thread handle 0x40a78940, query id 24606240 172.16.1.68 meeting

---TRANSACTION 4F56ED, not started

MySQL thread id 20225, OS thread handle 0x4b1ba940, query id 24605679 172.16.1.68 meeting

---TRANSACTION 4F56CB, not started

MySQL thread id 20224, OS thread handle 0x40dc3940, query id 24605440 172.16.1.69 meeting

---TRANSACTION 4F5746, not started

MySQL thread id 20222, OS thread handle 0x4b27d940, query id 24606238 172.16.1.68 meeting

---TRANSACTION 4F56CC, not started

MySQL thread id 20221, OS thread handle 0x4af71940, query id 24605998 172.16.1.69 meeting

--------

FILE I/O

--------

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (write thread)

I/O thread 7 state: waiting for completed aio requests (write thread)

I/O thread 8 state: waiting for completed aio requests (write thread)

I/O thread 9 state: waiting for completed aio requests (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

939003 OS file reads, 2968487 OS file writes, 1608789 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 3, seg size 5, 28540 merges

merged operations:

insert 36306, delete mark 40, delete 0

discarded operations:

insert 0, delete mark 0, delete 0

Hash table size 276671, node heap has 451 buffer(s)

0.00 hash searches/s, 0.11 non-hash searches/s

---

LOG

---

Log sequence number 823344922

Log flushed up to 823344922

Last checkpoint at 823344922

0 pending log writes, 0 pending chkp writes

1430467 log i/o's done, 0.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 137363456; in additional pool allocated 0

Dictionary memory allocated 344470

Buffer pool size 8191

Free buffers 0

Database pages 7740

Old database pages 2837

Modified db pages 0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 2857286, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 938988, created 14210, written 1442459

0.00 reads/s, 0.00 creates/s, 0.00 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: 7740, unzip_LRU len: 0

I/O sum[0]: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. 5632, id 1255045440, state: sleeping

Number of rows inserted 1017272, updated 485496, deleted 20023, read 180749999536

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 46844.30 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set (0.00 sec)

ERROR:

No query specified