MySQL database Locking Mechanism

To understand the MySQL database locking mechanism, first understand the three MySQL lock types:

1. Row-level locking
2. Table-level locking
3. Page-level locking

The three lock types are used in the MySQL database as follows:
Row-level locking: MyISAM, Memory, CSV [non-transactional storage engine]
Table-level locking: InnoDB, NDB Chuster
Page lock: Berkeley DB

Two locking mechanisms: Read lock and write lock

MySQL database lock Information Query
1. query table-level locked information [MyISAM]

1. Mysql> show status like 'table % ';

2. query row-level locked information [InnoDB]

1. Mysql> show status like 'innodb _ row_lock % ';

MySQL database lock table SQL
1. Lock the table
Read lock: mysql> lock table [tablename] READ;
Write lock: mysql> lock table [tablename] WRITE;
UNLOCK: mysql> unlock tables;

MySQL deadlock instance[Steps]

Step 1: Process 1

1. Mysql> UPDATE t1 SET id = 100 WHERE id = 1;

Step 2: Process 2

1. Mysql> UPDATE t2 SET id = 1000 WHERE id = 100;

Step 3: Process 1

1. Mysql> UPDATE t1 SET id = 2000 WHERE id = 100;

Step 4: Process 2

1. Mysql> UPDATE t2 SET id = 200 WHERE id = 1;

The above are some tips on MySQL database locking mechanisms. For more information, see [easily understand the principles of MySQL database lock mechanism.

Original article reprinted, please note: Reprinted from the Best Blog http://www.biuuu.com/