MySQL transaction isolation level and lock

MySQL transaction isolation level and lock

Table structure

CREATE TABLE record (ID int auto_increment primary key,title varchar (255) not null,shortname varchar (255) Not Null,authorid int not null,createtime datetime does null,state int not null,totalview int default null), insert into record (Title,short Name,authorid,createtime,state,totalview) VALUES (' Hello World ', ' hello-world-0 ', 1, ' 2015-10-11 08:08:08 ', 1,10), ( ' Hello World 111 ', ' hello-world-1 ', 1, ' 2015-10-11 08:08:08 ', 2,10 ', (' Hello World 222 ', ' hello-world-2 ', 2, ' 2015-10-11 08:08:08 ', 3,10), (' Hello World 333 ', ' hello-world-3 ', 3, ' 2015-10-11 08:08:08 ', 4,10), (' Hello World 444 ', ' hello-world-4 ') , 3, ' 2015-10-11 08:08:08 ', 5,10);

First about the isolation level of the transaction

http://my.oschina.net/xinxingegeya/blog/215419

http://my.oschina.net/xinxingegeya/blog/296513

and lock classification, granularity and strategy.

http://my.oschina.net/xinxingegeya/blog/215417

MySQL multi-version control MVCC

http://my.oschina.net/xinxingegeya/blog/208821

Lock at RC Isolation level

Under the Read-committed isolation level, the row locks behave as follows,

Sessiona

Open transaction

Mysql>mysql> SELECT @ @global. tx_isolation;+-----------------------+| @ @global. tx_isolation |+-----------------------+| read-committed |+-----------------------+1 row in Set (0.00 sec) mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql>

Sessionb

Open transaction

Mysql> SELECT @ @global. tx_isolation;+-----------------------+| @ @global. tx_isolation |+-----------------------+| read-committed |+-----------------------+1 row in Set (0.00 sec) mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql>

Sessiona

Update the record of id = 1 in session A, as below,

Mysql> Update record Set title = ' Session a update ' where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched:1 changed:1 warnings:0

The update is successful, then update the same id = 1 record in session B,

Session B

Mysql> Update record Set title = ' Session b Update ' where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

You can see that because session a has not yet been committed, Sessiona holds the row lock for Record id = 1, so when session B is updated there is no corresponding row lock, so the lock wait timeout update fails. You can also see that other records can be updated under the current transaction.

Mysql> Update record Set title = ' Session b Update ' where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transactionmysql> update record set title = ' Session b Update ' where id = 2; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0

The record for update ID = 2 was successful.

Note, however, that we update through the ID field, select the row of data to update with the ID field, and the ID field is a primary key column, what happens if I find an update on a field that doesn't have an index? Let's take a look.

Session AA

Open the transaction,

Mysql> begin; Query OK, 0 rows Affected (0.00 sec)

Session BB

Open the transaction,

Mysql> begin; Query OK, 0 rows Affected (0.00 sec)

Sessoin AA

Mysql> Update record Set title = ' Session a update ' where Authorid = 1; Query OK, 2 rows Affected (0.00 sec) rows Matched:2 changed:2 warnings:0

The update was successful. Next, update the data row for Authorid = 1 in session BB. As stated above, there is no index on the Authorid column, which leads to the lock table, but what is the actual effect?

Session BB

Mysql> Update record Set title = ' Session C Update ' where Authorid = 2; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> update record set title = ' Session C Update ' WHERE Authorid = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

You can see that the record for update Authorid = 2 is not waiting for the lock, that is, the other data row is not locked, while the data row updating Authorid = 1 finds the lock wait timeout (Session AA transaction has not yet been committed).

As we said earlier, when the non-indexed columns are updated, the entire table is locked, what's going on?

Because the MySQL storage engine does not know which rows to lock when updating via Authorid, because there are no indexes on the Authorid, the data rows of the entire table are returned and locked. The MySQL server layer then filters and unlocks non-qualifying rows of data (invoking the unlock operation of the storage engine).

Last commit Session AA's transaction,

Mysql> commit; Query OK, 0 rows Affected (0.00 sec)

Then, in session BB, perform the update, commit,

Mysql> Update record Set title = ' Session C Update ' where Authorid = 1; Query OK, 2 rows Affected (0.00 sec) rows Matched:2 changed:2 warnings:0mysql> commit; Query OK, 0 rows Affected (0.00 sec)

The lock under RR isolation level

Let's take a look at the locks under the RR isolation level, first we index the Authorid column.

Session A

Open transaction

Mysql> begin; Query OK, 0 rows Affected (0.00 sec)

Session B

Open transaction

Mysql> begin; Query OK, 0 rows Affected (0.00 sec)

Session A

Execute the following UPDATE statement

mysql> select * from record;+----+------------------+---------------+----------+------- --------------+-------+-----------+| id | title             | shortname     | authorid | createtime           | state | totalview |+----+--- ---------------+---------------+----------+---------------------+-------+-----------+|  1 |  hello world 000  | hello-world-0 |         1 | 2015-10-11 08:08:08 |     1 |         10 | |   2 | hello world 111  | hello-world-1 |         1 | 2015-10-11 08:08:08 |     2 |        10 | |   3 | hello world 222  | hello-world-2 |         2 | 2015-10-11 08:08:08 |     3  |        10 | |   4 | hello world 333  | hello-world-3 |         3 | 2015-10-11 08:08:08 |     4  |        10 | |   5 | hello world 444  | hello-world-4 |         3 | 2015-10-11 08:08:08 |     5  |        10 | |   6 | session a update | hello-world-0 |        4 | 2015-10-11 08:08:08 |      1 |        10 | |   7 | hello world 666  | hello-world-0 |         5 | 2015-10-11 08:08:08 |     1  |        10 | |   8 | hello world 666  | hello-world-0 |         6 | 2015-10-11 08:08:08 |     1  |        10 |+----+------------------+---------------+----- -----+---------------------+-------+-----------+8 rows in set  (0.00 sec) mysql>  update record set title =  ' Session a update '  WHERE&Nbsp;authorid = 4; query ok, 0 rows affected  (0.00 sec) rows matched: 1  changed :  0  warnings: 0

Session B

Execute the following INSERT statement,

mysql> INSERT into record (Title,shortname,authorid,createtime,state,totalview) VALUES (' Hello World 666 ', ' Hello-world-0 ', 4, ' 2015-10-11 08:08:08 ', 6,10); ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

You can see the lock waiting timeout, look at what locks are waiting,

> select * from innodb_locks**  1. row *********************    lock_id:  11604:64:4:11lock_trx_id: 11604  lock_mode: x,gap  lock_type: record  lock_table:  ' Test '. ' Record '  lock_index: idx_author_id lock_space: 64  lock_ page: 4   lock_rec: 11  lock_data: 5, 7********************  2. row *********************    lock_id: 11603:64:4:11lock_trx_id:  11603  lock_mode: x,gap  lock_type: record lock_table:  ' test '. ' RECORD '  lock_index: idx_author_id lock_space: 64  lock_page: 4    Lock_rec: 11  lock_data: 5, 72 rows in set 

You can see that the Lock_mode entry is x,gap. X represents an exclusive lock, Gap gap lock.

Another example,

mysql> INSERT into record (Title,shortname,authorid,createtime,state,totalview) VALUES (' Hello World 666 ', ' Hello-world-0 ', 3, ' 2015-10-11 08:08:08 ', 6,10); ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

Waiting for the lock,

> select * from innodb_locks******************** 1. Row ********************* lock_id:11604:64:4:10lock_trx_id:11604 lock_mode:x,gap lock_type:record lock_table: ' Te  St '. ' Record ' lock_index:idx_author_id lock_space:64 lock_page:4 lock_rec:10 lock_data:4, 6******************** 2. Row ********************* lock_id:11603:64:4:10lock_trx_id:11603 lock_mode:x lock_type:record lock_table: ' Test ' . ' Record ' lock_index:idx_author_id lock_space:64 lock_page:4 lock_rec:10 lock_data:4, + rows in set

The two are slightly different.

Attention:

Record Lock: Is added to the index record.

Gap Lock: Locks the range between the index records and adds them to the front or back of the last index record

Next-key Lock: The combination of record lock and Gap lock, Gap lock locks the range before the record lock

The gap lock is primarily to prevent Phantom reads, and is used under the Repeated-read (RR) isolation level. Under Read-commited (RC), there is generally no gap lock (with the exception of foreign keys, which is not considered here). Clearance locks are also used for statement based replication

Clearance lock some side effects, if you want to shut down, one is to change the session isolation level to RC, or turn on Innodb_locks_unsafe_for_binlog (default is OFF).

The gap lock appears only on the secondary index, and the unique index and primary key index are no gap locks. The gap lock, either s or X, only blocks the insert operation.

=========end=========

MySQL transaction isolation level and lock