Shulou(Shulou.com)05/31 Report--

This article introduces how to test MySQL MyISAM table lock. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

For MyISAM tables, the added lock is a table-level lock; write operations block read operations, read operations block write operations, and write operations block write operations; and read operations do not block read operations.

Test 1, the read operation of the session ① blocks the write operation of the session ②

Session ①

Mysql > create table T2 (id tinyint (3) unsigned not null auto_increment)

-> name varchar (10) not null

-> primary key (id))

-> engine=myisam auto_increment=8 default charset=gbk

Query OK, 0 rows affected (0.03 sec)

Mysql > insert into T2 (name) values ('Neo')

Query OK, 1 row affected (0.03 sec)

Mysql > insert into T2 (name) values ('Trinity')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from T2

+-+ +

| | id | name |

+-+ +

| | 8 | Neo |

| | 9 | Trinity |

+-+ +

2 rows in set (0.00 sec)

Mysql > desc T2

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

| | name | varchar (10) | NO | | NULL |

+-+ +

2 rows in set (0.09 sec)

Add a read lock to the table

Mysql > lock table T2 read

Query OK, 0 rows affected (0.00 sec)

Session ②

Mysql > select * from T2

+-+ +

| | id | name |

+-+ +

| | 8 | Neo |

| | 9 | Trinity |

+-+ +

2 rows in set (0.00 sec)

The session will be waiting.

Mysql > update T2 set name='James' where id=5

Session ①

Mysql > show processlist

+-+ -+

| | Id | User | Host | db | Command | Time | State | Info |

+-+ -+

| | 1 | system user | | NULL | Connect | 748155 | Slave has read all relay log; waiting for the slave thread to update it O thread to update it | NULL |

| | 2 | system user | | NULL | Connect | 748156 | Connecting to master | NULL |

| | 13 | event_scheduler | localhost | NULL | Daemon | 600105 | Waiting on empty queue | NULL | |

| | 74 | neo | localhost | fire | Query | 0 | NULL | show processlist |

| | 75 | neo | localhost | fire | Query | 83 | Waiting for table level lock | update T2 set name='James' where id=5 |

+-+ -+

5 rows in set (0.00 sec)

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

Session ②

Mysql > update T2 set name='James' where id=5

Query OK, 0 rows affected (1 min 48.96 sec)

Rows matched: 0 Changed: 0 Warnings: 0

Test 2, the write operation of session ① blocks the read operation of session ②

Session ①

Mysql > lock table T2 write

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into T2 (name) values ('Jack')

Query OK, 1 row affected (0.01sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > show processlist

+-+ -+

| | Id | User | Host | db | Command | Time | State | Info |

+-+ -+

| | 1 | system user | | NULL | Connect | 748422 | Slave has read all relay log; waiting for the slave thread to update it O thread to update it | NULL |

| | 2 | system user | | NULL | Connect | 748423 | Connecting to master | NULL |

| | 13 | event_scheduler | localhost | NULL | Daemon | 600372 | Waiting on empty queue | NULL | |

| | 74 | neo | localhost | fire | Query | 0 | NULL | show processlist | |

| | 75 | neo | localhost | fire | Query | 2 | Waiting for table metadata lock | select * from T2 |

+-+ -+

5 rows in set (0.00 sec)

Session ②

Queries will block

Mysql > select * from T2

Session ①

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

Session ②

Mysql > select * from T2

+-+ +

| | id | name |

+-+ +

| | 8 | Neo |

| | 9 | Trinity |

| | 10 | Jack |

+-+ +

3 rows in set (47.89 sec)

When one table is locked in the same session, an error will be reported when querying another table.

Mysql > lock table test read

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test where id=80

+-+ +

| | id | name |

+-+ +

| | 80 | Lily |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from T70

ERROR 1100 (HY000): Table't 70' was not locked with LOCK TABLES

An error will be reported when using the alias of the table

Mysql > lock table test read

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test t where id=80

ERROR 1100 (HY000): Table 't'was not locked with LOCK TABLES

Aliases need to be locked

Mysql > lock table test t read

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test t where id=80

+-+ +

| | id | name |

+-+ +

| | 80 | Kame |

+-+ +

1 row in set (0.00 sec)

This is the end of the MySQL 5.5 MyISAM table lock test. I hope the above content can be of some help to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.