Database lock mechanism

the basic principle of lock

In order to ensure the data is finished and consistent, the database system adopts the lock to realize the isolation of the transaction. The basic theory of locks used in various large databases is consistent, but there are differences in the implementation.

From a relationship locked by a concurrent transaction, it can be divided into shared locks and exclusive locks. Different from locked objects, you can generally be divided into table locks and row locks.

Lock

A shared lock is used to read a data operation, which is non-exclusive, allowing other transactions to read its locked resource at the same time, but does not allow other transactions to update it.

An exclusive lock is also called an exclusive lock, which is suitable for modifying data. The resources it locks, other transactions cannot be read, and cannot be modified.

When a transaction accesses a database resource, if a SELECT statement is executed, a shared lock must first be obtained, and if an insert, update, or DELETE statement is executed, an exclusive lock must be obtained that locks the resource being manipulated.

When the second transaction also accesses the same resource, if a SELECT statement is executed, a shared lock must be obtained first, and an exclusive lock must also be obtained if an INSERT, update, or DELETE statement is executed. At this point, depending on the type of lock that has been rotated on the resource, the second transaction should wait for the first transaction to unlock the corresponding resource, or the lock can be acquired immediately.

Locks already placed on the resource	Second transaction for read operation	The second transaction makes an update operation
No	Get a shared lock now	Get an exclusive lock now
Shared locks	Get a shared lock now	Waits for the first transaction to unlock a shared lock
Exclusive lock	Waits for the first transaction to unlock an exclusive lock	Waits for the first transaction to unlock an exclusive lock

1 Shared locks

1, lock conditions: When a transaction executes a SELECT statement, the database system assigns a shared lock to the transaction to lock the queried data.

2. Unlocking condition: By default, the database system immediately unlocks the shared lock after the data is read. For example, when a transaction executes a query "select * from Accounts" statement, the database system first locks the first row, reads, unlocks the first row, and then locks the second row. This allows other transactions to update the unlocked rows in the accounts table at the same time during a transactional read operation.

3. Compatibility with other locks: if a shared lock is placed on the data resource, the shared and update locks can be placed again.

4, concurrency performance: with good concurrency performance, when the data is placed in a shared lock, you can also place a shared lock or update lock. So concurrency performance is good.

2 Exclusive lock

1, lock conditions: When a transaction executes an INSERT, UPDATE, or DELETE statement, the database system automatically uses an exclusive lock on the data resource that the SQL statement manipulates. If the data resource already has another lock (any lock) present, it cannot be placed in an exclusive lock.

2. Unlocking conditions: Exclusive locks need to wait until the end of the transaction to be dismissed.

3, Compatibility: Exclusive lock can not be compatible with other locks, if the data resources have been added to the exclusive lock, you can no longer place other locks. Similarly, if other locks have been placed on the data resource, then no exclusive locks can be placed.

4, concurrency performance: Needless to say, the worst. Only one transaction is allowed to access the locked data, and if other transactions require access to that data, you must wait for the previous transaction to end, unlock the exclusive lock, and the other transaction will have access to the data.

3 Update lock

The initialization phase of the update lock is used to lock resources that might be modified, which avoids the deadlock caused by the use of shared locks. For example, for the following update statement:

UPDATE accounts SET balance=900 WHERE id=1

The update operation takes two steps:

L read the record with ID 1 in the Accounts table.

L Perform the update operation.

If you use a shared lock in the first step, and then you upgrade the lock to an exclusive lock in the second step, a deadlock may occur. For example, two transactions acquire a shared lock for the same data resource, and then both have to escalate the lock to an exclusive lock, but wait for another transaction to unlock the shared lock in order to escalate to an exclusive lock, which creates a deadlock.

The update lock has the following characteristics:

L lock-in condition: When a transaction executes an UPDATE statement, the database system allocates a renewal lock for the transaction first.

L Unlock Condition: When the data is read and the update operation is performed, the update lock is upgraded to an exclusive lock.

l Compatibility with other locks: update locks are compatible with shared locks, that is, one resource can place update and shared locks at the same time, but a maximum of one update lock is placed. Thus, when multiple transactions update the same data, only one transaction can obtain an update lock, and then upgrade the update lock to an exclusive lock, other transactions must wait until the end of the previous transaction to obtain an update lock, which avoids deadlocks.

Concurrency performance: Allows multiple transactions to read the locked resource at the same time, but does not allow other transactions to modify it.

Introduction to MySQL row-level locks, table-level locks, page-level locks

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, single row of records plus lock

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.

MySQL 5.1 supports table-level locking of MyISAM and Memory tables, page-level locking of BDB tables, row-level locking of InnoDB tables.
The table locking method used for Write,mysql is as follows:
If there is no lock on the table, put a write lock on it.
Otherwise, the lock request is placed in the write lock queue.

The locking method used for Read,mysql is as follows:
If there is no write lock on the table, put a read lock on it
Otherwise, the lock request is placed in the read lock queue.

InnoDB uses row locking, BDB uses page locking. There may be deadlocks for both of these storage engines. This is because, during SQL statement processing, InnoDB automatically obtains row locks and bdb to obtain page locks rather than when the transaction starts.

Advantages of row-level locking:
· There are only a few locking conflicts when accessing different rows in many threads.
· There are only a few changes when rolling back.
· You can lock a single row for a long time.

Disadvantages of row-level locking:
· Consumes more memory than page-level or table-level locking.
· When used in most of the table, it is slower than page-level or table-level locking because you have to get more locks.
· If you frequently perform group by operations on most data or you must scan the entire table frequently, it is significantly slower than other locks.
· With high-level locking, you can easily adjust your application by supporting different types of locking, because its lock cost is less than row-level locking.

Table locking takes precedence over page-level or row-level locking in the following cases:
· Most of the statements in the table are used for reading.
· With strict keywords read and updated, you can update or delete a row that can be extracted with a single read keyword:
· UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
· DELETE from Tbl_name WHERE unique_key_col=key_value;
· SELECT combines parallel INSERT statements with few update or DELETE statements.
· There are many scan or group by operations on the entire table, and there is no write operation.

/* ========================= MySQL lock table type and unlock statement ========================= */

If you want to do a large number of insert and SELECT operations on a table, but parallel insertions are not possible, you can insert the records into the staging table and then periodically update the data in the staging table to the actual tables. This can be accomplished with the following command:

Copy CodeThe code is as follows:
mysql> LOCK TABLES real_table Write, insert_table write;
Mysql> INSERT into real_table SELECT * from insert_table;
mysql> TRUNCATE TABLE insert_table;
Mysql> UNLOCK TABLES;

The advantages of row-level locks are:
Reduce conflict locks when many threads request different records.
Reduce change data when a transaction is rolled back.
Making it possible to lock a single row of records for a long time.

The disadvantages of row-level locks are:
Consumes more memory than page-level and table-level locks.
A lock is a mechanism by which a computer coordinates multiple processes or threads concurrently accessing a resource, and the locking mechanism of a different database is similar. Because the database resource is a kind of resource that can be shared by many users, how to guarantee the consistency and validity of data concurrency is a problem that all databases must solve, and lock conflict is also an important factor that affects the performance of database concurrent access. Understanding the locking mechanism not only makes us more efficient in exploiting database resources, but also enables us to better maintain the database, thus improving the performance of the database.

MySQL's lock mechanism is relatively simple, and its most notable feature is that different storage engines support different locking mechanisms.

For example, the MyISAM and memory storage engines use table-level locks (table-level-locking), BDB storage engines are page locks (page-level-locking), and table-level locks are supported; The InnoDB storage engine supports row-level locks. Table-level locks are also supported, and row-level locks are used by default.

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.

Three kinds of locks have their own characteristics, if only from the point of view of the lock, table-level lock is more suitable for query-oriented, only a small number of index conditions to update data applications, such as Web applications, row-level locks are more suitable for a large number of index conditions to update a small number of different data, but also have concurrent query applications, such as some online transaction processing (OLTP) systems.

There are two modes for MySQL table-level locks: table-Shared read lock and table write lock. What do you mean, it means that when you read a MyISAM table, it does not block other users from reading requests for the same table, but it blocks writes to the same table, and writes to the MyISAM table blocks other users from reading and writing to the same table.

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), and when its value is set to 0 o'clock, concurrent insertions are not allowed; When its value is set to 1 o'clock, If there are no holes in the MyISAM table (that is, rows that are not deleted in the table), MyISAM allows a record to be inserted at the end of the table while another process is reading the table, and when its value is set to 2 o'clock, it is allowed to insert records concurrently in the footer, regardless of whether there are holes in the MyISAM table.

How the MyISAM lock dispatch is implemented is also a key issue. For example, 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. Fortunately, we can adjust the scheduling behavior of MyISAM through some settings. By specifying the parameter low-priority-updates, we can make the MyISAM default engine give the read request priority rights, set its value to 1 (set Low_priority_updates=1), and lower the priority.

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)
In order to allow the coexistence of row and table locks, a multi-granularity locking mechanism is implemented, and there are two kinds of intent locks (all table locks) used internally, namely intent-shared and intent-exclusive locks.
InnoDB row locks are implemented by locking the index entries, which means that only the data is retrieved through the index criteria, InnoDB uses row-level locks, or the table lock is used!

In addition: several important parameters of INSERT, update performance optimization

Copy CodeThe code is as follows:
Bulk_insert_buffer_size
Bulk INSERT cache size, this parameter is for the MyISAM storage engine. For increased efficiency when inserting 100-1000+ records at once. The default value is 8M. It can be doubled for the size of the data volume.

Concurrent_insert
Concurrent insertions, when a table has no holes (deleted records), and in the case of a process acquiring a read lock, other processes can insert at the end of the table.

The value can be set to 0 does not allow concurrent insertions, 1 when the table does not have holes, to perform concurrent insertions, 2 regardless of whether there are holes to perform concurrent insertions.
By default, 1 is set for the deletion frequency of the table.

Delay_key_write
Defer updating the index for the MyISAM storage engine. This means that when the update record is logged, the data is first up to disk, but not up, the index exists in memory, and when the table is closed, the memory index is written to disk. A value of 0 does not turn on and 1 turns on. Enabled by default.

Delayed_insert_limit, Delayed_insert_timeout, delayed_queue_size
Delay the insertion, hand over the data to the memory queue, and then slowly insert it. However, these configurations, not all of the storage engine support, for the time being, commonly used INNODB does not support, MyISAM support. According to the actual situation, the general default is sufficient

/* ==================== MySQL InnoDB lock table with lock line ======================== */

Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data sample) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).

For example: Suppose there is a form products with ID and name two fields, ID is the primary key.

Example 1: (explicitly specify the primary key, and there is this information, row lock)

Copy CodeThe code is as follows: SELECT * from the products WHERE id= ' 3 ' for UPDATE;
SELECT * FROM Products WHERE id= ' 3 ' and type=1 for UPDATE;

Example 2: (explicitly specify the primary key, if the information is not found, no lock)

Copy CodeThe code is as follows: SELECT * FROM Products WHERE id= '-1 ' for UPDATE;

Example 3: (No primary key, table lock)

Copy CodeThe code is as follows: SELECT * FROM Products WHERE name= ' Mouse ' for UPDATE;

Example 4: (primary key ambiguous, table lock)

Copy CodeThe code is as follows: SELECT * from the products WHERE id<> ' 3 ' for UPDATE;

Example 5: (primary key ambiguous, table lock)

Copy CodeThe code is as follows: SELECT * from the products WHERE ID like ' 3 ' for UPDATE;

Note 1:for Update applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect.
NOTE 2: To test the condition of the lock, you can use the command Mode of MySQL and open two windows to do the test.

Testing in MySQL 5.0 is exactly like this.

In addition: Myasim only supports table-level locks, INNERDB supports row-level locks
Data that has been added (row-level lock/table-level Lock) locks cannot be locked by other transactions or modified (modified, deleted) by other transactions
is a table-level lock, the table is locked regardless of whether the record is queried
In addition, if both A and B query the table ID but not the records, A and b do not have a row lock on the query, but a and B will get an exclusive lock, when a and then insert a record will be waiting because B already has a lock, then B and then insert the same data will throw deadlock Found when trying to get lock; Try restarting transaction then releases the lock, at which point a lock is obtained and the insert succeeds

Database lock mechanism