Mysql Lock Granularity

Table Lock:

A table lock is the most current lock policy in MySQL and is the least expensive strategy: it locks the entire table.

Before a user locks a table (add, delete, change), the first thing to do is to obtain a write lock, which blocks all read and write operations to the table by other users. Only when there is no write lock can other users get read locks, and read locks are not blocked from each other.

In a particular scenario, table locks may also have good performance. For example, the READ LOCAL table lock supports some types of concurrent write operations. In addition, the write lock has a higher priority than the read lock, so a write lock request may be inserted in front of the read lock queue (the write lock can be inserted in front of the lock queue, whereas the read lock cannot be inserted in front of the write lock);

Although the storage engine can manage its own locks, MySQL itself uses a variety of valid locks for different purposes. For example, the server uses table locks for statements such as ALTER table and ignores the lock mechanism of the storage engine.

Row-level Locks:

Row-level locks provide maximum support for concurrent processing (as well as maximum lock overhead). It is well known that row-level locks are implemented in InnoDB and xtradb and some other storage engines. Row-level locks are implemented only at the storage engine layer, and the MySQL server layer is not implemented.

Mysql Lock Granularity