MySQL Fourth article ~ Something about the lock

A brief introduction: Met was asked a parallel update lock problem, let me tangled for a while, later consulted the expert, finally figured.

Two experiments:

CREATE TABLE ' test1 ' (
' ID ' varchar ($) Not NULL COMMENT ' id ',
' NAME ' varchar (+) DEFAULT ',
' STATUS ' varchar DEFAULT NULL,
' PID ' varchar ($) DEFAULT NULL COMMENT ' parent id ',
PRIMARY KEY (' ID '),
KEY ' idx_pid ' (' PID ')
) Engine=innodb DEFAULT Charset=utf8
Insert INTO ' test1 ' (' ID ', ' NAME ', ' STATUS ', ' PID ') VALUES (' 1 ', ' 1 ', ' A ', ' 111 ');
Insert INTO ' test1 ' (' ID ', ' NAME ', ' STATUS ', ' PID ') VALUES (' 2 ', ' 3 ', ' B ', ' 222 ');
Insert INTO ' test1 ' (' ID ', ' NAME ', ' STATUS ', ' PID ') VALUES (' 3 ', ' 3 ', ' D ', ' 333 ');
Insert INTO ' test1 ' (' ID ', ' NAME ', ' STATUS ', ' PID ') VALUES (' 4 ', ' 4 ', ' E ', ' 222 ');
Insert INTO ' test1 ' (' ID ', ' NAME ', ' STATUS ', ' PID ') VALUES (' 5 ', ' 5 ', ' F ', ' 222 ');
Insert INTO ' test1 ' (' ID ', ' NAME ', ' STATUS ', ' PID ') VALUES (' 6 ', ' 6 ', ' C ', ' 111 ');
Transaction 1
Begin
Update test1 set name= ' 2131 ' where pid=111;

Transaction 2
Begin
Update test1 set name= ' 21311 ' where id=3;

Result transaction 2 will get stuck when executing transaction 1 (PID is a non-unique index, ID is the primary key)

Three analysis:

The following points are obtained through analysis

1 Database Schema: RR

2 locking of the database for non-unique indexes: 1 find the primary key index to this record lock record lock for this record (update and delete for the specified record), 2 for the range of the value of the non-uniqueness index itself, Gap lock (for the specified range of inserts)

Four doubts:

According to the theoretical analysis, the lock record of transaction 1 only the two records of Id=1 and id=6 and the associated gap lock, should not block the update of transaction 2 id=3. Tangled for a long time, and asked a lot of people

Five settlements:

Finally got the advice of M Goethevin brother, found that the type of UPDATE statement is varchar, and my two transaction conditions are not quoted. , resulting in implicit conversions (or careless mischief), without blocking after the condition is quoted. Solve.

Six supplements
1 implicit conversions can invalidate an index, remember

2 about the scope of non-uniqueness index locking Be sure to judge well

MySQL Fourth article ~ Something about the lock