MYSQL pseudo-row locks

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.