MySQL InnoDB Storage engine lock mechanism Experiment

MySQL InnoDB Storage engine lock mechanism experiment MySQL's InnoDB Storage engine uses row-level locks, and the default transaction isolation level is Repeatable read, unlike Oracle's default transaction isolation level commit read. What is the specific performance of the MySQL InnoDB Storage engine lock mechanism? The experiment is as follows: first CREATE a test TABLE: create table 'test _ innodb_lock '('A' int (11) default null,' B 'varchar (16) DEFAULT NULL, KEY 'test _ innodb_lock_a_IDX '('A') ENGINE = InnoDB and then insert some data into this table for use. The final table data is as follows: + ------ + | a | B | + ------ + | 1 | a | 1 | x | 1 | y | 2 | B | 2 | w | | 2 | z | 3 | c | 4 | d | 5 | e | 8 | ff | 8 | f | 10 | g | + ------ + first, let's look at the row-Level Lock situation: experiment 1: open two MySQL clients and run mysql> set a on Client 1. Utocommit = 0; Query OK, 0 rows affected (0.00 sec) modify the transaction commit method of client 1 to manual commit; execute: mysql> set autocommit = 0; Query OK on Client 2, 0 rows affected (0.00 sec) also changes the transaction commit method of client 2 to manual commit; execute in client 1: mysql> update test_innodb_lock set B = 'xxx' where a = 1 and B = 'y'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0. Use both index field a and non-index field B to update a piece of data. Run mysql> update test_innodb_lock set B = 'xxx' where a = 1 and on Client 2. B = 'X'; use both index field a (and the index value is the same as the value of client 1) and non-index field to update another data. The result shows that the update Statement of client 2 is blocked, the execution can continue only after Client 1 is submitted or rolled back. Note: although the final update data of two transactions is not the same piece of data, it may be locked because both SQL statements use the same index value (a = 1 ), row-level locks are upgraded to page-level locks. Experiment 2: Execute mysql> rollback on Client 1, Query OK, 0 rows affected (0.00 sec) to roll back experiment 1, and execute mysql> rollback on Client 2; query OK, 0 rows affected (0.00 sec) rollback operation of Experiment 1; executed on Client 1: mysql> update test_innodb_lock set B = 'xxx' where a = 1 and B = 'a'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0. Use both index field a and non-index field B to update a piece of data. Run mysql> update test_innodb_lock set B = 'xxx' where a = 2 and B = 'B' on Client 2 '; query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 use index field a at the same time (the index value is different from the index value of the 1SQL Statement on the client) non-index field B updates a piece of data. The update goes smoothly and the execution is not blocked. It means that the data is updated based on the index and non-index field, when the index condition values in the SQL statements of the two transactions are different, the update will continue smoothly. Experiment 3: Run mysql> rollback on Client 1, Query OK, 0 rows affected (0.00 sec) to roll back experiment 1, and execute mysql> rollback on Client 2; query OK, 0 rows affected (0.00 sec) rollback Experiment 1; execute mysql> update test_innodb_lock set B = 'xxx' where B = 'D' on Client 1 '; query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 update a unique data record through a non-index field, and execute in client 2: mysql> update test_innodb_lock set B = 'xxx' where B = 'E'; use a non-index field to update another unique data record. The update statement is blocked. It indicates that when a transaction updates data based on a non-index field, InnoDB locks the entire table, and the row-Level Lock increases to the table-Level Lock. Experiment 4: Execute mysql> rollback on Client 1, Query OK, 0 rows affected (0.00 sec), Roll Back experiment 3, and execute mysql> rollback on Client 2; query OK, 0 rows affected (0.00 sec) rollback experiment 3; execute mysql> update test_innodb_lock set B = 'xxx' where a = 4 on Client 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 only use index update data records on Client 2 run: mysql> update test_innodb_lock set B = 'xxx' where a = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 only use indexes to update data records, and the index value is the same as the index value of client 1 (a = 4, the update Statement of client 2 is blocked. It indicates that the row-Level Lock of this phenomenon is consistent with the row-Level Lock we understand, that is, the row-Level Lock actually only locks a record. Experiment 5: Execute mysql> rollback on Client 1. Query OK, 0 rows affected (0.00 sec) rollback Experiment 4. Execute mysql> rollback on Client 2; query OK, 0 rows affected (0.00 sec) rollback Experiment 4; execute in client 1: mysql> update test_innodb_lock set B = 'xxx' where a = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 only use index update data records on Client 2 run: mysql> update test_innodb_lock set B = 'xxx' where B = 'G'; only use non-index fields to update data records. The update Statement of client 2 is blocked because upda of client 2 Because the te statement does not use an index, you need to add an exclusive lock to the data table. However, the exclusive lock exists on the record a = 4, and the update Statement of index client 2 can only be blocked. The preceding experiment shows that: 1. In some cases, InnoDB row-level locks are automatically upgraded to page-level locks and table-level locks. In this case, the database Write Performance drops sharply, and there may be a lot of deadlocks (the deadlock situation can be easily imitated. Here is not an example); 2. Real row-level locks, only when all transactions are indexed for data retrieval. Next we will continue the experiment related to the Gap lock: Experiment 6: Execute mysql> rollback on Client 1; Query OK, 0 rows affected (0.00 sec) rollback Experiment 5; on client 2, execute mysql> rollback; Query OK, 0 rows affected (0.00 sec) to roll back Experiment 5. On client 1, execute: mysql> update test_innodb_lock set B = 'xxx' where a = 8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0. Update the data record through the index. The index value is 8. Run mysql> insert into test_innodb_lock (a, B) values (8, 'xxx') on Client 2 '); insert a data entry into the data table and the index column of the data. And the index value of the SQL statement of client 1 is 8. In this case, the insert statement is blocked. Run mysql> rollback; Query OK on Client 1, 0 rows affected (0.00 sec) mysql> update test_innodb_lock set B = 'xxx' where a = 8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0: update data records through the index, the index value is 8; execute: mysql> rollback on Client 2; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_innodb_lock (a, B) values (5, 'xxx'); insert a piece of data into the data table, the index column value of the inserted data is smaller than the index value of the SQL statement of client 1, but greater than or equal to the index value of the retrieval index (a = 8) of the maximum size of the existing data record 5, at this time, inse The rt statement is blocked. Run mysql> rollback; Query OK on Client 1, 0 rows affected (0.00 sec) mysql> update test_innodb_lock set B = 'xxx' where a = 8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0: update data records through the index, the index value is 8; execute: mysql> rollback on Client 2; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_innodb_lock (a, B) values (9, 'xxx'); insert a piece of data into the data table, the index column value of the inserted data is greater than the index value of the SQL statement of client 1, but smaller than the index value 10 of the index (a = 8) of the existing data record. The statement is blocked. Run mysql> rollback; Query OK on Client 1, 0 rows affected (0.00 sec) mysql> update test_innodb_lock set B = 'xxx' where a = 8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0: update data records through the index, the index value is 8; execute: mysql> rollback on Client 2; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_innodb_lock (a, B) values (10, 'xxx'); insert a piece of data into the data table, the index column value of the inserted data is greater than the index value of the SQL statement of client 1 and greater than or equal to 10 of the index value of the index of the retrieved index (a = 8) at least in the existing data record, at this time, I The nsert statement is successfully executed. The actions in the above series indicate that when a transaction updates data through an index, it will lock closely adjacent index records before and after the index, including index values that do not exist at all, the locked range is left-closed and right-open, that is, [x, y), where x is smaller than the maximum value of the SQL statement index value in the transaction, y is the minimum value greater than the index value of the SQL statement in the transaction. In this example, the index value of the SQL statement in the transaction is 8, and the lock interval of the index is [5, 10 ), therefore, when another transaction performs the insert operation, index records with an index value greater than or equal to 5 and smaller than 10 will be blocked. Note that when the index value of the update transaction is the maximum value in an existing record, the insert operations of other transactions will be blocked for all records greater than the index value. This is the specific manifestation of InnoDB gap locks. Therefore, InnoDB's gap lock avoids some phantom reads, but not all because it locks a range rather than the entire table. Experiment 7: Run mysql> rollback on Client 1. Query OK, 0 rows affected (0.00 sec) rollback Experiment 6. Run mysql> rollback on Client 2. Query OK, 0 rows affected (0.00 sec) rollback Experiment 6 is executed on Client 1: mysql> update test_innodb_lock set B = 'xxx' where B = 'a'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 update a record through a non-index field; execute mysql> insert into test_innodb_lock (a, B) on Client 2) values (10, 'xxx'); inserts a completely unrelated data, and the insert statement is blocked. It indicates that when transaction 1 passes the non-index Field If you update a data record, the entire table will be locked, and even the insert operation will be blocked. The above experiment shows that: 1. InnoDB's gap lock can avoid phantom reading of data, but it only avoids phantom reading. When a transaction updates data through indexes, in addition, the left-closed and right-open intervals before and after the index value of the previous transaction cannot insert data in parallel and must wait for the commit or rollback of the previous transaction; 2. When an index field is not used to update a transaction, InnoDB's gap lock completely avoids phantom read because it locks the entire table, the insert operation is blocked before the current transaction is committed or rolled back. Note: 1. Replace the update operation with the delete operation for the above experiments to achieve the same effect. 2. If you modify the default transaction isolation level of InnoDB, change it from Repeatable read To Read committed, the above phenomena will not occur, so such a lock mechanism only occurs at the transaction isolation level of Repeatable read, or this is an implementation method at the InnoDB Repeatable read transaction isolation level.