MySQL transactions, locks and applications (2)

InnoDB supports transactions, row locks and table locks. MyISAM does not support transactions and only supports table locks. Only InnoDB is introduced here.

InnoDB implements the following two types of row locks.

• Shared lock (S): allows one transaction to read a row and prevents other transactions from obtaining an exclusive lock on the same data set.

• Exclusive lock (X): Allows a transaction that obtains an exclusive lock to update data, and prevents other transactions from obtaining shared read locks and exclusive write locks on the same data set.

In addition, in order to allow row locks and table locks to coexist and implement a multi-granularity locking mechanism, InnoDB also has two internally used intention locks (Intention Locks). Both intention locks are It's a table lock.

• Intention shared lock (IS): The transaction intends to add a row shared lock to the data row. The transaction must first obtain the IS lock of the table before adding a shared lock to a data row.

• Intention exclusive lock (IX): The transaction intends to add a row exclusive lock to the data row. The transaction must first obtain the IX lock of the table before adding an exclusive lock to a data row.

InnoDB row lock mode compatibility list

XIXSISXconflictconflictconflictconflictIXConflictCompatibleConflictCompatibleSConflict ConflictCompatibleCompatibleISConflictCompatibleCompatibleCompatible

##Request lock mode Whether it is compatible Current lock mode

If the lock mode requested by a transaction is compatible with the current lock, InnoDB will grant the requested lock to the transaction; On the other hand, if the two are incompatible, the transaction will wait for the lock to be released.

Note:

• Intention lock is automatically added by InnoDB and does not require user intervention.

• For UPDATE, DELETE and INSERT statements, InnoDB will automatically add an exclusive lock (X) to the involved data set.

• For ordinary SELECT statements, InnoDB will not add any locks.

• InnoDB row lock is implemented by locking the index item on the index. Therefore, the row lock implementation feature of InnoDB means that InnoDB can only use the index condition to retrieve data. Row-level locks, otherwise InnoDB will use table locks.

In addition, transactions can explicitly add shared locks or exclusive locks to the recordset through the following statements.

• Shared lock(S): SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE.

• Exclusive lock (X): SELECT * FROM table_name WHERE ... FOR UPDATE.

Use SELECT ... IN SHARE MODE to obtain a shared lock. It is mainly used to confirm whether a certain row of records exists when data dependencies are required, and Make sure no one performs UPDATE or DELETE operations on this record. However, if the current transaction also needs to update the record, it is likely to cause a deadlock. For applications that need to update the row record after locking it, the SELECT... FOR UPDATE method should be used to obtain an exclusive lock.

The above is the content of MySQL transactions, locks and applications (2). For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!