Mysql row-level locks, table-level locks, page-level locks detailed introduction _mysql

Page level: Engine BDB.
Table level: Engine MyISAM, understood to lock the entire table, can be read at the same time, write not
Row level: Engine INNODB, separate one-line record lock

Table level, locking the entire table directly, and other processes cannot write to the table while you are locked out. If you are writing a lock, then the other processes are read and not allowed
Row level, which locks only the specified record, so that other processes can manipulate other records in the same table.
Page-level, table-level lock speed, but more conflicts, row-level conflict is small, but slow. So take the eclectic page level and lock the next set of records one at a time.

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 principle of 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 use row locking, BDB use page locking. For both types of storage engines, deadlocks may exist. This is because, during SQL statement processing, InnoDB automatically obtains row locking and BdB obtaining page locks, rather than when the transaction is started.

The advantages of row-level locking:
· Only a small number of lock conflicts exist when different rows are accessed in many threads.
· There are only a few changes when rolling back.
· You can lock a single row for a long time.

The disadvantage of row-level locking:
         consumes more memory than page-level or table-level locks.
         is slower than page-level or table-level locking when used in most of the table because you have to get more locks.
         is significantly slower than other locks if you frequently perform group by operations on most data or must scan the entire table frequently.
         with high-level locking, you can easily adjust your application by supporting different types of locks because the lock cost is less than row-level locking.

Table locking takes precedence over page-level or row-level locking in the following situations:
· Most statements for a table are used for reading.
· To read and update strict keywords, you can update or delete a line 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 and has few update or DELETE statements.
· There are many scans or group by operations on the entire table, and there are no writes.

/* ========================= 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 inserts are not possible, you can insert records into a temporary table, and then periodically update the data in the temporary table to the actual tables. You can implement the following command:

Copy Code code 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 conflicting locks when many threads request different records.
Reduces data change when a transaction rolls 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 locks and table-level locks.
A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently, and the locking mechanisms of different databases are similar. Because database resource is a kind of resource that is shared by many users, how to ensure the consistency and validity of data concurrent access is a problem that all databases must solve, lock conflict is also an important factor that affects the performance of concurrent access of database. Understanding the lock mechanism not only enables us to develop and utilize database resources more effectively, but also enables us to better maintain the database, thereby improving the performance of the database.

MySQL's lock mechanism is simpler, 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 the storage engine with page locks (page-level-locking), and table-level locks, while 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 summarized as follows:
1 table-level lock: small overhead, lock fast, no deadlock, locking granularity, the highest probability of lock conflict, the least concurrency.
2 row-level lock: Expensive, lock slow, deadlock, lock granularity, the lowest probability of lock conflict, the highest degree of concurrency.
3 page Lock: Cost and lock time bounded between table lock and row lock, deadlock, lock granularity bounded between table lock and row lock, concurrency is general.

Three kinds of locks each have their own characteristics, in the case of locks only, table-level locks are more suitable for queries, and only a small number of applications that update data by index conditions, such as Web applications, and row-level locks are more suitable for a large number of concurrent updates of small amounts of data by index and concurrent query applications, such as some online transaction processing (OLTP) systems.

There are two modes of MySQL table-level locks: table-Shared read lock and table-exclusive write-lock (table write lock). What does that mean? When you read a MyISAM table, it does not block other users from reading requests to the same table, but it blocks writes to the same table, and writing to the MyISAM table blocks other users from reading and writing to the same table.

The reading and writing of the MyISAM table is serial, that is, the write operation cannot be performed while reading, and vice versa. However, under certain conditions the MyISAM table also supports concurrent operation of query and insert operations, the 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, there are no deleted rows in the table), MyISAM allows another process to insert records from the footer while one process reads the table, and when the value is set to 2 o'clock, the record is allowed to be inserted at the end of the table, regardless of whether there is a hole

How to implement the MyISAM lock scheduling is also a key issue. For example, when a process requests a read lock on a MyISAM table and another process requests a write lock on the same table, will MySQL be treated like a priority process? The research shows that the write process will get the lock first (even if the read request is first to the lock wait queue). But this also creates a big flaw, namely a large number of write operations can make the query operation difficult to get read lock, which may cause blocking forever. Fortunately, we can use some settings to adjust the MyISAM scheduling behavior. By specifying the parameter low-priority-updates, the MyISAM default engine gives priority to the read request and sets its value to 1 (set Low_priority_updates=1) to lower the priority.

The biggest difference between the InnoDB lock and the MyISAM lock is that it supports the transaction (trancsaction) and the second is the 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 property for short), respectively:

Atomicity (atomicity): A transaction is an atomic unit of operation whose modification of the data is either fully executed or not implemented;
Consistency (consistent): When a transaction starts and completes, the data must be in a consistent state;
Isolation (Isolation): Database system provides a certain isolation mechanism to ensure that the transaction is not affected by external concurrency operations of the "independent" environment implementation;
Persistence (Durable): After a transaction is completed, its modification to the data is permanent and can be maintained even if a system failure occurs.

InnoDB has two modes of row locks:

1 shared Lock: Allows one transaction to read a row and prevent other transactions from acquiring exclusive locks on the same dataset.
(Select * FROM table_name where ... lock in share mode)

2 Exclusive Lock: Allows the transaction update data to acquire exclusive locks, preventing other transactions from acquiring shared read locks and exclusive write locks of the same dataset. (SELECT * FROM table_name where.....for update)
In order to allow the coexistence of row and table locks, a multiple-granularity locking mechanism is implemented, and two internal intent locks (both table locks) are intended to share the lock and intent exclusive lock respectively.
The InnoDB row lock is implemented by locking the index entry, that is, the InnoDB uses row-level locks only if the data is retrieved through index conditions, otherwise the table lock is used!

In addition: inserting, updating several important parameters of performance optimization

Copy Code code as follows:

Bulk_insert_buffer_size
Bulk INSERT cache size, this parameter is for MyISAM storage engine. This is useful for increasing efficiency when inserting a 100-1000+ record at a time. The default value is 8M. The amount of data can be increased by doubling.

Concurrent_insert
Concurrent inserts, when the table has no holes (deleted records), and when a process acquires a read lock, other processes can be inserted at the end of the table.

The value can be set to 0 does not allow concurrent inserts, 1 performs concurrent inserts when the table has no holes, and 2 regardless of whether there is a hole to perform concurrent insertion.
By default, 1 is set for the deletion frequency of the table.

Delay_key_write
For the MyISAM storage engine, delay the update of the index. It means that when the update is logged, the data is up to disk, but not up, the index is in memory, and when the table closes, the memory index is written to disk. The value is 0 does not open, 1 is open. Open by default.

Delayed_insert_limit, Delayed_insert_timeout, delayed_queue_size
Delay insertion, first handing the data to the memory queue, and then slowly inserting. But these configurations, not all of the storage engine support, at present, the commonly used InnoDB not supported, MyISAM support. According to the actual situation, the general default is enough

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

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

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

Example 1: (explicitly specifying the primary key and having this information, row lock)

Copy Code code as follows:

SELECT * from the products WHERE id= ' 3 ' for UPDATE;
SELECT * from the products WHERE id= ' 3 ' and type=1 for UPDATE;

Example 2: (explicitly specify the primary key, if no such information, no lock)

Copy Code code as follows:

SELECT * from the products WHERE id= '-1 ' for UPDATE;

Example 3: (No primary key, table lock)

Copy Code code as follows:

SELECT * from the products WHERE name= ' Mouse ' for UPDATE;

Example 4: (primary key ambiguous, table lock)

Copy Code code as follows:

SELECT * from the products WHERE id<> ' 3 ' for UPDATE;

Example 5: (primary key ambiguous, table lock)

Copy Code code 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 status of the lock, you can use MySQL's command Mode to open two windows for testing.

The test in MySQL 5.0 is indeed the case

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 by other transactions (modified, deleted)
is a table-level lock, the table is locked, regardless of whether or not a record is queried
In addition, if both A and B query the table ID but the query does not have a record, then A and B will not row lock on the query, but a and B will acquire exclusive locks, at which point A and then insert a record will be waiting because B is already locked, when B inserts the same data will throw deadlock Found when trying to get lock; Try restarting transaction and then release the lock, at which point a gets the lock and the insert succeeds