Provides mysql database tutorials, commands, installation, and other technical summaries:
A friend said that a simple query caused the Transaction timed out: deadline problem. it is suspected that the database table is locked,
1. Deadline:
--- The error occurred in META-INF/ibatis/ITEM_sqlmap.xml.
--- The error occurred while executing query.
--- Check the select I. ID, I. SHOP_ID, I. ITEM_GROUP_ID, I. ITEM_GROUP_NAME, I. ITEM_NAME, I. LIST_PRICE, I. PL_PRICE, I. PROTECTION_PRICE, I. MALL, I. STAT, I. LOGISTICS, I. TYPE, I. ITEM_CATEGORY_ID, I. BRAND_ID, I. BRAND, I. FAVOUR_NUM, I. IS_SUBSCRIBE, I. VOLUME, I. WEIGHT, I. INVENTORY, I. RELEASE_DATE, I. OFF_REASON, I. IS_DEL, I. CREATED_DATE, I. UPDATED_DATE, I. SIMPLE_DESCRIPTION, I. VIRTUAL_BEGIN_DATE, I. VIRTUAL_E ND_DATE, I. SEQ_NUM, IPC. PICTURE_PATH from item as I inner join ITEM_PICTURE as ipc on I. ITEM_GROUP_ID = IPC. ITEM_GROUP_ID where I. ID =? And ipc. TYPE = '1' and ipc. IS_DEL = '0 '.
--- Check the SQL Statement (preparation failed ).
--- Cause: org. springframework. transaction. TransactionTimedOutException: Transaction timed out: deadline was Tue Nov 25 13:33:07 CST 2014
At com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.exe cuteQueryWithCallback (MappedStatement. java: 204)
At com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.exe cuteQueryForObject (MappedStatement. java: 120)
At com. ibatis. sqlmap. engine. impl. SqlMapExecutorDelegate. queryForObject (SqlMapExecutorDelegate. java: 518)
At com. ibatis. sqlmap. engine. impl. SqlMapExecutorDelegate. queryForObject (SqlMapExecutorDelegate. java: 493)
At com. ibatis. sqlmap. engine. impl. SqlMapSessionImpl. queryForObject (SqlMapSessionImpl. java: 106)
At org. springframework. orm. ibatis. SqlMapClientTemplate $1. doInSqlMapClient (SqlMapClientTemplate. java: 273)
At org.springframework.orm.ibatis.SqlMapClientTemplate.exe cute (SqlMapClientTemplate. java: 203)
... 41 more
Caused by: org. springframework. transaction. TransactionTimedOutException: Transaction timed out: deadline was Tue Nov 25 13:33:07 CST 2014
At org. springframework. transaction. support. ResourceHolderSupport. checkTransactionTimeout (ResourceHolderSupport. java: 141)
At org. springframework. transaction. support. ResourceHolderSupport. getTimeToLiveInMillis (ResourceHolderSupport. java: 130)
At org. springframework. transaction. support. ResourceHolderSupport. getTimeToLiveInSeconds (ResourceHolderSupport. java: 114)
At org. springframework. jdbc. datasource. DataSourceUtils. applyTimeout (DataSourceUtils. java: 275)
At org. springframework. jdbc. datasource. DataSourceUtils. applyTransactionTimeout (DataSourceUtils. java: 257)
At org. springframework. jdbc. datasource. TransactionAwareDataSourceProxy $ transactionawarwarvocationhandler. invoke (TransactionAwareDataSourceProxy. java: 244)
At com. sun. proxy. $ Proxy324.prepareStatement (Unknown Source)
At com.ibatis.sqlmap.engine.exe cution. SqlExecutor. prepareStatement (SqlExecutor. java: 497)
At com.ibatis.sqlmap.engine.execution.SqlExecutor.exe cuteQuery (SqlExecutor. java: 175)
At com. ibatis. sqlmap. engine. mapping. statement. MappedStatement. sqlExecuteQuery (MappedStatement. java: 221)
At com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.exe cuteQueryWithCallback (MappedStatement. java: 189)
... 47 more
2. check the Innodb storage engine status and table lock status
Show eninge innodb status; no deadlock information and other exception information; the INNODB_LOCKS and INNODB_LOCK_WAITS tables in the query system are NULL. only the INNODB_TRX table has Records and is in the long running status, the reason is that the transaction is not committed or rolled back.
Mysql> SELECT * FROM 'innodb _ trx ';
+ ---------- + ----------- + Hour + ---------------- + ------------ + hour + ----------- + hour + ------------------ + hour + ----------------- + hour ---------------------------- + --------------------------- + ------------------ + ---------------------------- +
| Trx_id | trx_state | trx_started | percent | trx_weight | percent | trx_query | trx_operation_state | percent | trx_tables_locked | trx_lock_structs | percent | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+ ---------- + ----------- + Hour + ---------------- + ------------ + hour + ----------- + hour + ------------------ + hour + ----------------- + hour ---------------------------- + --------------------------- + ------------------ + ---------------------------- +
| 19183390 | RUNNING | 15:39:30 | NULL | 9 | 940341 | NULL | 0 | 0 | 5 | 1248 | 3 | 4 | 0 | read committed | 1 | 1 | NULL | 0 | 9762 | 0 | 0 |
| 19183153 | RUNNING | 15:36:41 | NULL | 0 | 940206 | NULL | 0 | 0 | 0 | 376 | 0 | 0 | 0 | read committed | 1 | 1 | NULL | 0 | 9411 | 0 | 0 |
| 19183139 | RUNNING | 15:36:28 | NULL | 0 | 940238 | NULL | 0 | 0 | 0 | 376 | 0 | 0 | 0 | read committed | 1 | 1 | NULL | 0 | 9937 | 0 | 0 |
+ ---------- + ----------- + Hour + ---------------- + ------------ + hour + ----------- + hour + ------------------ + hour + ----------------- + hour ---------------------------- + --------------------------- + ------------------ + ---------------------------- +
3 rows in set (0.00 sec)
Mysql> SELECT * FROM 'innodb _ locks ';
Empty set (0.00 sec)
Mysql> SELECT * FROM 'innodb _ LOCK_WAITS ';
Empty set (0.00 sec)
Mysql>
Check carefully. we can see that there is no table lock, no row lock, no lock wait, and only the transaction RUNNING has not been committed or rolled back. Temporary solution: kill the threads in these transactions.
3. reproduce the problem and view the transaction table records
Mysql> SELECT * FROM 'innodb _ trx ';
+ ---------- + ----------- + Hour + ---------------- + ------------ + hour + ----------- + hour + ------------------ + hour + ----------------- + hour ---------------------------- + --------------------------- + ------------------ + ---------------------------- +
| Trx_id | trx_state | trx_started | percent | trx_weight | percent | trx_query | trx_operation_state | percent | trx_tables_locked | trx_lock_structs | percent | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+ ---------- + ----------- + Hour + ---------------- + ------------ + hour + ----------- + hour + ------------------ + hour + ----------------- + hour ---------------------------- + --------------------------- + ------------------ + ---------------------------- +
| 19196180 | RUNNING | 17:41:10 | NULL | 12 | 942663 | NULL | 0 | 0 | 4 | 1248 | 2 | 8 | 0 | read committed | 1 | 1 | NULL | 0 | 9810 | 0 | 0 |
+ ---------- + ----------- + Hour + ---------------- + ------------ + hour + ----------- + hour + ------------------ + hour + ----------------- + hour ---------------------------- + --------------------------- + ------------------ + ---------------------------- +
1 rows in set (0.00 sec)
Mysql>
4. analyze the data in Slow log and binlog
Go to the slow log and check the slow record of the 942663 thread ID. no,
Check the DML record of the 942663 thread ID in the binlog, which has the following two records:
#141125 17:41:10 server id 230 end_log_pos 118147 CRC32 0x6f2402a1 Query thread_id = 942663 exec_time = 0 error_code = 0
Set timestamp = 1416908470 /*! */;
BEGIN
/*! */;
# At 118147
#141125 17:41:10 server id 230 end_log_pos 118231 CRC32 0x0219bed2 Table_map: 'Business _ db'. 'shop _ CASH_COUPON_USER_REF 'Ed Ed to number 178
# At 118231
#141125 17:41:10 server id 230 end_log_pos 118298 CRC32 0xc6665994 Write_rows: table id 178 flags: STMT_END_F
### Insert into 'business _ db'. 'shop _ CASH_COUPON_USER_REF'
### SET
###@ 1 = 4859
###@ 2 = 284
###@ 3 = 2425
###@ 4 = '0'
###@ 5 = '2017-11-25 17:41:10'
###@ 6 = NULL
# At 118298
#141125 17:41:10 server id 230 end_log_pos 118411 CRC32 0x93f6d105 Table_map: 'Business _ db'. 'cash _ TICKET 'mapped to number 727
# At 118411
#141125 17:41:10 server id 230 end_log_pos 118703 CRC32 0xe4b314ad Update_rows: table id 727 flags: STMT_END_F
### UPDATE 'business _ db'. 'cash _ TICKET'
### WHERE
###@ 1 = 19956
###@ 2 = 1416886592
###@ 3 = NULL
###@ 4 = NULL
###@ 5 = 1
###@ 6 = '2017-12-31 00:00:00'
###@ 7 = '2017-11-25 00:00:00'
###@ 8 = NULL
###@ 9 = NULL
###@ 10 = NULL
###@ 11 = NULL
###@ 12 = NULL
###@ 13 = NULL
###@ 14 = NULL
###@ 15 = NULL
###@ 16 = '5c2483b3033b30c6b948d6a971c87f1d'
###@ 17 = 'cash-TICKET-1'
###@ 18 = '0'
###@ 19 = 000000050.000000000
###@ 20 = NULL
###@ 21 = 284
###@ 22 = '0'
### SET
###@ 1 = 19956
###@ 2 = 1416908470
###@ 3 = 'Pl. 1qaz2wsx'
###@ 4 = NULL
###@ 5 = 1
###@ 6 = '2017-12-31 00:00:00'
###@ 7 = '2017-11-25 00:00:00'
###@ 8 = NULL
###@ 9 = 2425
###@ 10 = 'Pl. 1qaz2wsx'
###@ 11 = '1qaz2wsx @ 163.com'
###@ 12 = '123'
###@ 13 = NULL
###@ 14 = NULL
###@ 15 = NULL
###@ 16 = '5c2483b3033b30c6b948d6a971c87f1d'
###@ 17 = 'cash-TICKET-1'
###@ 18 = '0'
###@ 19 = 000000050.000000000
###@ 20 = 1416908470
###@ 21 = 284
###@ 22 = '0'
# At 118703
#141125 17:41:10 server id 230 end_log_pos 118734 CRC32 0x6949012e Xid = 16199116
COMMIT /*! */;
We can see that the successful DML operation is consistent with the RUNNING transaction time. Therefore, we can use the INSERT statement and UPDATE statement of the binlog to find the method of a business module of the application, the exception handling module fails to COMMIT and ROLLBACK in time.
After the ROLLBACK operation is added, the test is performed N times. no error message is returned, but the SELECT * FROM 'innodb _ trx' command is executed, and no record is returned, this indicates that all transactions are committed or rolled back in time.
5. Additional problems encountered during the period
Cause: java. SQL. SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. innoDB is limited to row-logging when transaction isolation level is read committed or read uncommitted .; nested exception is com. ibatis. common. jdbc. exception. nestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the ITEM. updateByInventory-InlineParameterMap.
--- Check the statement (update failed ).
--- Cause: java. SQL. SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. innoDB is limited to row-logging when transaction isolation level is read committed or read uncommitted.
Solution: SET BINLOG_FORMAT to MIXED, set global BINLOG_FORMAT = MIXED;
6. Summary
This problem seems to have been solved, but more details may not be sorted out. why is there no ROLLBACK or COMMIT after the transaction is started, and an error will be reported when the SELECT statement is executed in the future? DML Formation
Exclusive lock X, and the query is shared lock S, X and S are mutually exclusive (for Innodb Lock Please refer to: http://blog.itpub.net/26230597/viewspace-1315111/), so there is a problem, as to the underlying analysis of the source layer, if you have any questions, please share your ideas and analysis process. thank you.
Bytes ----------------------------------------------------------------------------------------------------------------
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
Original blog address: http://blog.itpub.net/26230597/viewspace-1346680/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------
