The previous two blogs briefly talked about indexes in mysql. Today we will talk about locks and transaction implementation in mysql(InnoDB engine)

MySql > select * from ‘MySql’

MySQL(2

Everyone should be familiar with locks. For example, optimistic locks are commonly implemented by CAS algorithm in Java. A typical example is atomic classes, which update atomic operations through CAS spin. Pessimistic locks are usually implemented by Synchronized and Lock.

Optimistic and pessimistic locks

  • ** Optimistic locking: ** Every time the data is read, it is assumed that others will not modify it, so it will not lock. Instead, it will determine whether others have updated the data during the update period, so it can use the version number mechanism. This can be done in the database by adding a version number field to the data table. The version number is read together when the data is read, and the version number is incremented by one each time the data is updated. When we update, compare the current version number of the corresponding record in the database table with the value of the version number taken out for the first time. If the value is equal, it will be updated; otherwise, it is considered as expired data. Optimistic locking is suitable for multi-read applications and improves throughput.

  • ** Pessimistic locking: ** Every time data is read, it is locked because it is thought someone else will modify it, so it will block when someone else tries to read the data. MySQL uses many of these locking mechanisms, such as row locks, table locks, read locks, write locks, etc., to lock before operation.

Shared locks and exclusive locks

  • ** Shared lock: ** Shared lock is also called read lock or S lock. After adding the shared lock, other transactions can only add the shared lock before the end of the transaction, and only read operations can be performed on the lock, but no other type of lock can be added.

    Add lock in share mode

    SELECT description FROM book_book lock in share mode;

  • ** Exclusive lock: ** Exclusive lock is also called write lock or X lock. After a transaction locks data exclusively, only this transaction can read and write data. Before the transaction ends, other transactions cannot lock data.

    Plus for update

    SELECT description FROM book_book for update;

Row and table locks

Innodb uses row-level locks only if Innodb retrieves data by index criteria. Otherwise, Innodb uses table locks.

  • Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, the lock conflict probability is high, and the concurrency is low
  • Line lock: expensive, slow lock; Deadlocks occur; The lock granularity is small, the probability of lock conflict is low, and the concurrency is high

Here is a bit of a puzzle, why table locks do not appear deadlocks? In MyISAM, because there are no transactions, a lock is released after an SQL execution. There is no loop waiting, so there is only blocking and no deadlock. However, there are transactions in InnoDB is more confused, hope to have a friend to understand the point @-@

Here are two examples of the above locks:

# transaction 1 BEGIN; SELECT description FROM book_book where name = 'JAVA programming thoughts' lock in share mode; # transaction 2 BEGIN; UPDATE book_book SET name = 'new book' WHERE name = 'new'; SELECT * FROM information_schema.innodb_trx; trx_id trx_state trx_started trx_tables_locked trx_rows_locked 39452 LOCK WAIT 2018-09-08 19:01:39 1 1 282907511143936 RUNNING 2018-09-08 18:58:47 1 38Copy the code

Transaction 1: lock the book table. Transaction 2: lock the book table. Transaction 1: lock the book table.

# transaction 1 BEGIN; SELECT description FROM book_book WHERE id = 2 lock in share mode; # transaction 2 BEGIN; UPDATE book_book SET name = 'new book' WHERE id = 1; SELECT * FROM information_schema.innodb_trx; trx_id trx_state trx_started trx_tables_locked trx_rows_locked 39454 RUNNING 2018-09-08 19:10:44 1 1 282907511143936 RUNNING 2018-09-08 19:10:35 1 1Copy the code

Transaction 1 assigns a shared lock to the book table, and transaction 2 attempts to modify the book table without blocking. This is because transaction one and transaction two are indexed, so row locks are used and do not block.

Intent lock (InnoDB only)

The significance of intent locks is to facilitate the detection of conflicts between table locks and row locks

  • Intent lock: Intent lock is a table-level lock that represents an operation on a row. It IS divided into intentional shared lock (IS) and intentional exclusive lock (IX).

  • ** transaction A assigns A shared lock to A row in the table so that the row can only be read but not written. Transaction B then requests an exclusive lock for the entire table. If transaction B is successful, it can modify any row in the table, which conflicts with the row lock held by A. InnoDB introduced intent locks to determine conflicts between them.

  • SQL > select * from table where table lock (s) exists; 2, determine whether each row in the table is locked by a row lock, so that the entire table traversal, inefficient.

  • SQL > select * from table where table lock exists; SQL > select * from table where table lock exists; 2. Determine whether there is an intentional lock on the table

  • Applying for the intent lock when the intent lock exists: The action of applying for the intent lock is completed by the database. In the above example, when transaction A applies for the row lock, the database will automatically start the application for the intent lock first. When the application for the exclusive lock of transaction B detects the existence of the intent lock, the application will be blocked.

  • Will intent locks conflict: Intent locks do not conflict because intent locks simply represent operations on a row.

Coexistence between various locks

IX IS X S IX Compatible Compatible Conflict Conflict IS compatible compatible Conflict Compatible X conflict Conflict S conflict compatible Conflict compatibleCopy the code

A deadlock

  • ** Concept: ** The phenomenon of two or more transactions waiting for each other as they compete for resources during execution.

  • 1. Mutually exclusive condition: a resource can only be used by one transaction at a time. 2. Request and hold conditions: when a transaction is blocked by requesting a resource, the acquired resource is held. 3, non-deprivation conditions: the obtained resources can not be forcibly deprived before the end of use. 4. Circular waiting conditions: form a circular waiting relationship connected head to tail

  • ** Unlocks a deadlock state: ** unlocks one of the transactions

MVCC(Multi-version Concurrency Control)

MVCC enables InnoDB to better implement REPEATABLE READ in transaction isolation level

  • It makes InnoDB no longer use row lock to control the concurrency of the database, but instead combine the row lock of the database with multiple versions of the row. With very little overhead, it can achieve unlocked read, thus greatly improving the concurrency performance of the database system.

  • Implementation: InnoDB implements MVCC by storing three additional hidden fields for each row

  • 1.DB_TRX_ID: a 6-byte identifier that automatically +1 for each transaction. You can run the “show engine Innodb status” statement to find the value

  • DB_ROLL_PTR: specifies a 7byte undo log that is written to the rollback segment

  • 3.DB_ROW_ID: The size is 6 bytes, which increases monotonically with new row inserts.

  • SELECT: the returned rows of data need to meet conditions: 1, the data rows to create the version number must be less than or equal to the transaction of version 2, the delete version number (the special position in the line is set to mark it as deleted) must be undefined or greater than the version number of the current affairs, determine the line before the start of the current transaction has not been deleted.

  • INSERT: InnoDB records the current system version number for each new row as the creation version number.

  • DELETE: InnoDB records the current system version of each deleted row as the deleted version of the row.

  • UPDATE: InnoDB copies a data. The version number of this data uses the system version number. It also uses the system version number as the delete number for old data.

  • Here is the read is not lock select, MVCC implementation of repeatable read using undo has been committed data, non-blocking. DB_ROW_ID =DB_ROW_ID; Create time =DB_ROW_ID; delete time =DB_ROW_ID; delete time =DB_ROW_ID; DB_ROW_ID = DB_ROW_ID;

Clearance lock

Gap locking allows InnoDB to solve phantom reading problems, and MVCC enables InnoDB’s RR isolation level to achieve serialization level effects while preserving good concurrency performance.

Definition: When we request a shared or exclusive lock for a range condition, InnoDB locks the index of existing data that meets the condition. InnoDB also locks records whose keys are in the condition range but do not exist, called gaps.

For example, bookId 1-80 and 90-99 exist in the book table. SELECT * FROM book WHERE bookId < 100 FOR UPDATE. InnoDB locks not only bookId values 1-80 and 90-99, but also bookId gaps between 81-89 (which do not exist). This avoids phantom reads under repeatable reads at the transaction isolation level.

Students who have questions can point out mutual discussion, if you need to reprint, please indicate the source.

Reference: dev.mysql.com/doc/refman/… www.cnblogs.com/chenpingzha…