MYSQL pseudo-row locks

MYSQL's pseudo-row locks have always assumed that the row-level locks supported by mysql's innodb engine are the same as those supported by oracle and postgresql, and are used to lock data rows. But it is actually different. Different understandings may lead to misunderstandings about the mysql lock mechanism. Innodb row-level locks are actually locked Based on index items. The following is the verification test process 1. data Preparation mysql> use test; database changedmysql> show create table t_kenyon \ G ***************************** 1. row *************************** Table: t_kenyonCreate Table: create table 't_ kenyon' ('id' int (11) default null) ENGINE = InnoDB default charset = utf81 row in set (0.00 sec) mysql> set autocommit = 0; query OK, 0 rows affected (0.00 sec) www.2cto.com mysql> show variables like '% autocommit % '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | autocommit | OFF | + --------------- + ------- + 1 row in set (0.00 sec) mysql> select * from t_kenyon; + ------ + | id | + ------ + | 1 | 123 | 789 | 345 | 78 | 78 | + ------ + 6 rows in set (0.00 sec) the above is the test table t_kenyon. Set the submission method to manual submission. II. process (enable two sessions and set autocommit = off respectively) www.2cto.com 1. session one updatemysql> update t_kenyon set id = 999 where id = 1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_kenyon; + ------ + | id | + ------ + | 999 | 123 | 789 | 345 | 78 | 78 | + ------ + 6 rows in set (0.00 sec) 2. session two updatemysql> show variables like 'autocommit '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | autocommit | OFF | + --------------- + ------- + 1 row in set (0.00 sec) www.2cto.com mysql> select * from t_kenyon; + ------ + | id | + ------ + | 1 | 123 | 789 | 345 | 78 | 78 | + ------ + 6 rows in set (0.00 sec) mysql> update t_kenyon set id = 88888 where id = 345; the value of the second session update is 345, but it is blocked until session1 is rollback or commit, if session1 is not rolled back or submitted, the blocking in session2 is automatically rolled back when the mysql lock time limit is exceeded. this parameter is innodb_lock_wait_timeout. The default value is 50 seconds. The following ERROR 1205 (HY000) occurs ): lock wait timeout exceeded; try restarting transaction to add the index to the Test 3. session one update mysql> create index ind_kenyon on t_kenyon (id); Query OK, 0 rows affected (28.58 sec) Records: 0 Duplicates: 0 Warnings: 0 www.2cto.com mysql> update t_kenyon set id = 999 where id = 1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_kenyon; + ------ + | id | + ------ + | 78 | 78 | 123 | 345 | 789 | 999 | + ------ + 6 rows in set (0.00 sec) 4. session two update mysql> select * from t_kenyon; + ------ + | id | + ------ + | 1 | 78 | 78 | 123 | 345 | 789 | + ------ + 6 rows in set (0.00 sec) www.2cto.com mysql> update t_kenyon set id = 7777 where id = 345; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_kenyon; + ------ + | id | + ------ + | 1 | 78 | 78 | 123 | 789 | 7777 | + ------ + 6 rows in set (0.00 sec) execution Plan mysql> explain select * from t_kenyon where id = 345 \ G ************************* ** 1. row ************************* id: 1 www.2cto.com select_type: SIMPLE table: t_kenyon type: refpossible_keys: ind_kenyon key: ind_kenyon key_len: 5 ref: const rows: 1 Extra: Using where; Using index1 row in set (0.00 sec) after adding an index, different data updates are not blocked, implementing the true meaning of the upstream lock. for Restrictions on Row-Level Lock extension, refer: http://www.bkjia.com/database/201208/145888.html Author kenyon