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 flushsrv_master_thread log flush and writes: 3428150----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 86616, signal count 85375Mutex spin waits 34309, rounds 1034394, OS waits 1556RW-shared spins 82325, rounds 2469556, OS waits 78033RW-excl spins 668, rounds 210810, OS waits 6658Spin rounds per wait: 30.15 mutex, 30.00 RW-shared, 315.58 RW-excl------------TRANSACTIONS------------Trx id counter 4F5748Purge done for trx's n:o < 4F4DEB undo n:o < 0History list length 2567LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 20185, OS thread handle 0x4b138940, query id 24606241 localhost rootSHOW ENGINE INNODB STATUS---TRANSACTION 4F5721, not startedMySQL thread id 20226, OS thread handle 0x402c6940, query id 24606002 172.16.1.69 meeting---TRANSACTION 4F5747, not startedMySQL thread id 20227, OS thread handle 0x40a78940, query id 24606240 172.16.1.68 meeting---TRANSACTION 4F56ED, not startedMySQL thread id 20225, OS thread handle 0x4b1ba940, query id 24605679 172.16.1.68 meeting---TRANSACTION 4F56CB, not startedMySQL thread id 20224, OS thread handle 0x40dc3940, query id 24605440 172.16.1.69 meeting---TRANSACTION 4F5746, not startedMySQL thread id 20222, OS thread handle 0x4b27d940, query id 24606238 172.16.1.68 meeting---TRANSACTION 4F56CC, not startedMySQL 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: 0Pending flushes (fsync) log: 0; buffer pool: 0939003 OS file reads, 2968487 OS file writes, 1608789 OS fsyncs0.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 mergesmerged operations: insert 36306, delete mark 40, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 276671, node heap has 451 buffer(s)0.00 hash searches/s, 0.11 non-hash searches/s---LOG---Log sequence number 823344922Log flushed up to   823344922Last checkpoint at  8233449220 pending log writes, 0 pending chkp writes1430467 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137363456; in additional pool allocated 0Dictionary memory allocated 344470Buffer pool size   8191Free buffers       0Database pages     7740Old database pages 2837Modified db pages  0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2857286, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 938988, created 14210, written 14424590.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 7740, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDBMain thread process no. 5632, id 1255045440, state: sleepingNumber of rows inserted 1017272, updated 485496, deleted 20023, read 1807499995360.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