Study on mysql-Lock

Table level: Engine MyISAM, understood to lock the entire table, during the lock, other processes cannot write to the table, if it is a read lock, other processes can read at the same time, if it is a write lock, then other processes are not allowed to read

Row level: Engine INNODB, single row record lock, other processes can also operate on other records in the same table

Page level: Engine BDB, table-level lock speed, but more conflict, less row-level conflict, but slow, page-level compromise, once locked adjacent group of records

Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, the least concurrency

Row-level Lock: High overhead, locking slow, deadlock, lock granularity is minimal, the probability of lock collision is the lowest, concurrency is also the highest

Page Lock: Overhead and lock time bounds between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, concurrency is generally

Table lock

Table shared read lock (table read lock) and table exclusive write lock (table write lock)

For write, the table locking method used by MySQL works as follows: If there is no lock on the table, put a write lock on it, otherwise, put the lock request in the Write lock queue

For read, the table lock method used by MySQL works as follows: If there is no write lock on the table, put a read lock on it otherwise, put the lock request in the Read lock queue

Row lock

During SQL statement processing, InnoDB automatically obtains row locks and bdb to get page locks, rather than when a transaction starts

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!

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

A single row can be locked for a long time

Disadvantages of row-level locking:

Consumes more memory than page 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 (such as update multiple data)

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 type 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 to read

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 only a few update or DELETE statements

There are many scan or group by operations on the entire table with no write operations

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 the records into the staging table, and then periodically update the data in the staging table to the actual table, which can be implemented with the following command:

MySQL>  LOCK TABLES real_table WRITE, insert_table write;mysql>Insert  into SELECT* from insert_table;mysql>TRUNCATE TABLE Insert_table;mysql > UNLOCK TABLES;

MyISAM

However, under certain conditions the MyISAM table also supports 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, and 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 a table while another process reads the table at the end of the table, and when its value is set to 2 o'clock, no holes in the MyISAM table are allowed.

How is MyISAM lock dispatch implemented, which is also a critical issue, such as when a process requests a read lock on a MyISAM table while another process requests a write lock on the same table, then MySQL will be like a priority process? Research shows that the write process will first obtain a lock (even if the read request first to the lock waiting queue), but this also creates a large flaw, that is, a large number of write operations will make the query operation is difficult to obtain read locks, which may cause permanent blocking, fortunately we can use some settings to adjust the MyISAM scheduling behavior, 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

InnoDB

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

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

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

SELECT *  fromProductsWHEREId='3'  for UPDATE;SELECT *  fromProductsWHEREId='3'  andType=1  for UPDATE;

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

SELECT *  from WHERE id='-1'forUPDATE;

Example 3: (No primary key, table lock)

SELECT *  from WHERE Name='Mouse'forUPDATE;

Example 4: (primary key ambiguous, table lock)

SELECT *  from WHERE ID<>'3'forUPDATE;

Example 5: (primary key ambiguous, table lock)

SELECT *  from WHERE  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 command Mode, open two windows to do the test

Testing in MySQL 5.0 is exactly like this.

Added (row-level lock/table-level Lock) lock data can not be locked by other transactions, and other transactions are not modified (modified, deleted) is a table-level lock, whether or not to query the record, will lock the table;

If both A and B query the table ID but no records are queried, A and b do not have a row lock on the query, but a and B will get an exclusive lock, and a then a second record will be in wait because B already has a lock, and B 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

Study on mysql-Lock