How to test MySQL 5.5MyISAM table lock
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.
