MySQL table lock, row lock, page lock

Refer to http://www.jb51.net/article/50047.htm "MySQL row level lock, table level lock, page level lock Details"

Page level: Engine BDB.
Table level: Engine MyISAM, understood to lock the entire table, can read at the same time, write No
Row level: Engine INNODB, separate row record locking (more detailed row lock, table lock, can refer to: http://www.cnblogs.com/charlesblc/p/5935326.html)

Table level, which locks the entire table directly, and other processes cannot write to the table during your lock. If you are writing a lock, other processes are not allowed to read
Row level, only locks the specified records so that other processes can operate on other records in the same table.
Page level, table-level lock speed, but more conflicts, row-level conflict is small, but slow. So we took a compromised page level and locked a contiguous set of records at once.

The characteristics of the above three types of locks can be broadly summarized as follows:
1) Table-level lock: Low overhead, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, the concurrency is the lowest.
2) Row-level lock: Overhead, locking slow, deadlock, lock granularity is the least, the probability of lock conflict is the lowest, the concurrency is the highest.
3) 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.

How to add a table lock:

mysql> LOCK TABLES real_table WRITE, insert_table write;mysql> INSERT INTO real_table SELECT * from INS Ert_table;mysql> TRUNCATE table insert_table;mysql> UNLOCK TABLES;

There are two modes for MySQL table-level locks: table-Shared read lock and table write lock.

When a process requests a read lock on one of the MyISAM tables, and another process requests a write lock on the same table, will MySQL be treated as a priority process? Research shows that the write process will acquire the lock first (even if the read request is first to the lock waiting queue). But this also creates a big flaw, that a large number of writes can make the query operation difficult to obtain a read lock, which can cause permanent blocking. The mechanism is changed by controlling a system variable (Concurrent_insert), which is described in more detail in the original text.

the read and write of the MyISAM table is serial , that is, the read operation cannot be written, and vice versa. However, under certain conditions, the MyISAM table also supports the concurrency of queries and insertions, and its mechanism is done by controlling a system variable (concurrent_insert). ( see the original in more detail )

The biggest difference between InnoDB lock and MyISAM lock is : one is support transaction (trancsaction) and the other is row-level lock . We know that a transaction is a logical processing unit consisting of a set of SQL statements that has four properties (the acid attribute, for short), respectively:

Atomicity (atomicity): A transaction is an atomic operating unit whose modification of the data is either performed entirely or is not executed;
Consistency (consistent): data must be in a consistent state at the beginning and completion of a transaction;
Isolation (Isolation): The database system provides a certain isolation mechanism to ensure that transactions are performed in an "independent" environment that is not affected by external concurrency operations;
Persistence (Durable): After a transaction is complete, its modification to the data is permanent, even if a system failure occurs.

InnoDB has two modes of row lock:

1) Shared lock : Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set.
(Select * FROM table_name where ... lock in share mode)

2) Exclusive lock : Transactions that allow exclusive locks are updated to prevent other transactions from acquiring shared read locks and exclusive write locks of the same data set.

(SELECT * FROM table_name where.....for update)

MySQL table lock, row lock, page lock