MySQL Lock combat test code

Storage Engine	Supported locks
MyISAM	Table-Level Locks
MEMORY	Table-Level Locks
InnoDB	Row-level Locks
BDB	Page lock

Table-level Lock: Low overhead, fast lock, no deadlock, lock granularity, lock collision is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.
Page Lock: Overhead and lock time are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

locking method	thread read	/ td>	thread to perform lock write	Other threads write
read lock	readable	readable	not writable	not writable
write lock	readable	unreadable	writable	not writable

Tools involved: Navicat and SQLyog (cannot use phpMyAdmin)

Test code

Test One: Read the lock. Description: You can only read the table with other threads

Execute the following code in NAVICAT

Mysql>Locktable ' Cat ' READ; Query OK,0rows Affectedmysql> SELECT * from ' cat ' WHERE1;+----+--------+| ID | Remark |+----+--------+|1| Ceshi | |2|22222||3|33333|+----+--------+3Rowsinch SetMySQL> UPDATE ' cat ' SET remark='Navicat'WHERE id=1;1099-Table'Cat'Was locked with a READLockand can't be updated

Execute in SQLyog

1 ;
+----+--------+
| ID | Remark |
+----+--------+
| 1 | Ceshi |
| 2 | 22222 |
| 3 | 33333 |
+----+--------+
3 rows in SetUPDATE ' cat ' set remark'sqlyog' WHERE id=1

SQLyog is always in execution when the update is executed. When unlocked, execution succeeds.

Performing an unlock operation in Navicat

mysql> unlock tables;

After execution, the SQLyog update executes successfully.

Test results:

When a read lock is made, the lock thread can perform a query operation and cannot write. Other threads can perform query operations and cannot write operations.

Test one: Write lock. Description: Only when the front-thread can write to the table (other threads cannot read this part of the data)

Execute the following code in NAVICAT

Mysql>LOCK TABLE cat WRITE; Query OK,0rows Affectedmysql>Select* from' Cat 'where 1;+----+--------+| ID | Remark |+----+--------+|1| SQLyog | |2|22222||3|33333|+----+--------+3Rowsinch SetMySQL> Update ' Cat 'Setremark='Navicat' whereId=1; Query OK,1row AffectedRows matched:1Changed:1Warnings:0

And then execute it in SQLyog.

Select  from where 1;

And

Set ' Navicat ' where id=1;

Visible is always the execution state. Only after unlocking can the SQLyog execute successfully

Performing an unlock operation in Navicat

mysql> unlock tables;

SQLyog executed successfully.

Test results:

After a write lock, the thread that performs the write lock can read and write, and other threads cannot read and write

MySQL Lock combat test code