​

 SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

Failure to perform a rollback after committing a transaction triggers an exception during PHP debugging results in a mysql deadlock that prevents subsequent requests from updating data

Question why

Update table set A =1 where id=1; Update table set A =2 where id=1; update table set A =2 where id=1;

In this case, USER B waits for user A to release the lock until the timeout period expires. If the timeout period is exceeded, an error is reported

Problem environment:

1. Insert and update the same data successively in the same transaction; 2. Multiple servers operate the same database; 3, instantaneous occurrence of high concurrency;

SQL > rollback rollback rollback rollback rollback rollback

For example, if the parameter fails to pass the check, an error message is returned, causing the rollback to fail

For example, if an error occurs in the following code after the update operation is performed, rollback is not performed. In this case, the previous point in the return operation should be rolled back, or an error message is returned after an exception is thrown

$this->startTrans(); try { $user = new User(); $user->where('id',$userId)->update(['realname'=>$parentName]); $existId = $this->where('class_id',$classId)->where('student_number',$number)->find(); If ($existId) return [' data '= >', 'code' = > 300, 'MSG' = > 'student id repeat]; $this->commit(); } catch (Throwable $e) { $this->rollback(); return ['data' => '', 'code' => 20102, 'msg' => $e->getMessage()]; }Copy the code

Exception information

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

​

To solve

1. Kill the thread to commit transaction data.

select * from information_schema.innodb_trx
Copy the code

​

Trx_mysql_thread_id is the process

kill 1544
Copy the code

Related troubleshooting statements

Show full processList ## The thread currently connected

Select * from information_schema.innodb_trx ## all transactions currently running

Select * from information_schema.innodb_locks ##

Select * from information_schema. Innodb_lock_waits #

Field details

desc information_schema.innodb_locks;

Field Type Null Key Default Remark lock_id vARCHar (81) NO Lock ID lock_trx_id vARCHar (18) NO Transaction ID with the lock lock_mode varchar(32) NO Lock mode lock_type varchar(32) NO Lock type lock_TABLE varchar(1024) NO Locked table lock_index varchar(1024) YES NULL Locked index lock_space Bigint (21) unsigned YES NULL Locked tablespace id LOCK_page BIGINT (21) unsigned YES NULL Locked page id lock_rec BIGINT (21) unsigned YES NULL Locked record ID lock_data vARCHar (8192) YES NULL Locked data desc information_schema. Innodb_lock_waits

Field Type Null Key Default Remark Requesting_trx_id VARCHar (18) NO Transaction ID requested_lock_id vARCHAR (81) NO Lock ID Blocking_trx_id vARCHar (18) NO Transaction ID blocking_lock_id varchar(81) NO Lock ID desc information_schema.innodb_trx;

Field Type Null Key Default Extra Remark TRx_ID VARCHAR (18) NO Transaction ID TRx_State VARCHAR (13) NO Transaction status: Trx_started dateTime NO 0000-00-00 00:00:00; Trx_requested_lock_id vARCHar (81) YES NULL Innodb_LOCKS. Lock_id TRx_WAIT_started dateTime YES NULL Wait time trx_weight Bigint (21) unsigned NO 0 # trx_mysQL_thread_ID BIGINT (21) unsigned NO 0 TRANSACTION thread ID trx_Query VARCHAR (1024) YES NULL SQL statement Trx_operation_state VARCHAR (64) YES NULL Current operation status of a transaction TRx_TABLES_IN_USE BIGINT (21) Unsigned NO 0 Number of tables used in a transaction Trx_tables_locked BIGINT (21) Unsigned NO 0 Number of locks that a transaction has trx_LOCK_structs BIGINT (21) Unsigned NO 0 # trx_LOCK_memorY_bytes Trx_rows_locked BIGINT (21) Unsigned NO 0 Number of rows locked trx_ROws_modified BIGint (21) Unsigned NO 0 Number of rows changed for a transaction trx_concurrency_tickets BIGINT (21) Unsigned NO 0 Number of concurrent transaction tickets trx_isolation_level VARCHAR (16) NO Transaction isolation level Trx_unique_checks int(1) NO 0 Whether the checks are unique trx_foreign_KEY_checks int(1) NO 0 Whether the checks are foreign key trx_last_foreign_KEY_error vARCHar (256) Trx_adaptive_hash_latched int(1) NO 0 # trx_adaptive_hash_timeout bigint(21) unsigned NO 0 #3

​