Line lock for MySQL

Overview

The rows in MySQL are index-based and row locks are locked on the index, so if a field is not indexed, it cannot be locked.

This paper is mainly to implement the verification process.

1. Using the InnoDB engine to build a table

Mysql> Create TableInnoDB ( -Idint,     -Namevarchar( -),     -Cityvarchar( -)     -) Engine InnoDBdefaultCharSet UTF8 - ; Query OK,0Rows Affected (0.01Sec

2. Do not build indexes for testing

Inserting test data

Mysql> Insert  intoInnoDBValues(1,'name1','city1'); Query OK,1Row affected (0.00sec) MySQL> Insert  intoInnoDBValues(2,'name2','City2'); Query OK,1Row affected (0.00sec) MySQL> Insert  intoInnoDBValues(3,'Name3','City3'); Query OK,1Row affected (0.00Sec

Open a CMD connection mysql, we become session1, in the Session1 do the following operations

Mysql> SetAutocommit=0; Query OK,0Rows Affected (0.00sec) MySQL> Select *  fromInnoDBwhereId=1  for Update     - ;+------+-------+-------+|Id|Name|City|+------+-------+-------+|    1 |Name1|City1|+------+-------+-------+1Rowinch Set(0.00Sec

We locked the id=1 record of the InnoDB table in Session1.

We open a new cmd window, we become session2

MySQL>updateset name='name1-1'where ID=2;

This time we can see that session2 will always wait.

This proves that for fields without indexes, MySQL is not able to add locks, actually using table locks

Session1 submitted, Session2 also updated successfully.

3. Set up an index test

To add an index to the ID field of a innodb table

MySQL>altertableaddindex0 rows affected (  0.020  0  0

Set the row to lock id=1 in Session1 without auto-commit

Mysql> SetAutocommit=0; Query OK,0Rows Affected (0.00sec) MySQL> Select *  fromInnoDBwhereId=1  for Update;+------+-------+-------+|Id|Name|City|+------+-------+-------+|    1 |Name1|City1|+------+-------+-------+1Rowinch Set(0.00Sec

To perform a row of update id=2 in Session2

Update Set name='name-bak'where id=2;

Update Successful!

The above procedure proves that the InnoDB row lock is added to the index.