The atomicity in ACID compliant is either we get all or nothing of the transaction, which means partial transaction is merely unacceptable. In this case, we do not get the transaction atomicity offered by InnoDB. The InnoDB documentation clearly says “InnoDB rolls back only the last statement on a transaction timeout by default”. This explains why transaction T2 was partially committed! This means, by following the default setting, MySQL is not going to fail and rollback the whole transaction, nor retrying again the timed out statement and just process the next statements until it reaches COMMIT or ROLLBACK. Unless you set innodb_rollback_on_timeout=1 (default is 0 – disabled), automatic rollback is not going to happen for InnoDB lock wait timeout error. This is true for deadlock, but not for InnoDB lock wait timeout. One might think that if any error encounters within a transaction, all statements in the transaction would automatically get rolled back, or if a transaction is successfully committed, the whole statements were executed atomically. Mysql> SELECT * FROM table2 WHERE id = 1 Īfter T2 was successfully committed, one would expect to get the same output “ T2 is updating the row” for both table1 and table2 but the results show that only table2 was updated. However, the end result after step #6 might be surprising if we did not retry the timed out statement at step #4: mysql> SELECT * FROM table1 WHERE id = 1 (Hangs for a while and eventually returns an error “Lock wait timeout exceeded try restarting transaction”)
SQL LOCK REQUEST TIME OUT PERIOD EXCEEDED UPDATE
UPDATE table1 SET data = ‘T2 is updating the row’ WHERE id = 1
UPDATE table2 SET data = ‘T2 is updating the row’ WHERE id = 1 UPDATE table1 SET data = ‘T1 is updating the row’ WHERE id = 1 We executed our transactions in two different sessions in the following order: Mysql> INSERT INTO table2 SET data = 'data #2' The second table (table2): mysql> CREATE TABLE table2 LIKE table1 Mysql> INSERT INTO table1 SET data = 'data #1' The first table (table1): mysql> CREATE TABLE table1 ( id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(50))
Consider the following two tables in database mydb: mysql> CREATE SCHEMA mydb
Let’s play around with a simple example to better understand the effect. Even if innodb_rollback_on_timeout is enabled, when a statement fails in a transaction, ROLLBACK is still a more expensive operation than COMMIT.The failed statement is not being rolled back by default.InnoDB lock wait timeout can cause two major implications: The Effects of a InnoDB Lock Wait Timeout The offensive transaction is waiting for row lock to be released by another transaction.The offensive transaction is not fast enough to commit or rollback the transaction within innodb_lock_wait_timeout duration.The above simply means the transaction has reached the innodb_lock_wait_timeout while waiting to obtain an exclusive lock which defaults to 50 seconds. One of the most popular InnoDB’s errors is InnoDB lock wait timeout exceeded, for example: SQLSTATE: General error: 1205 Lock wait timeout exceeded try restarting transaction
0 kommentar(er)
