Database lock (notes)

Time:2019-11-20

Introduction

MyISAMTransaction is not supported.

What is the difference between MyISAM and InnoDB in terms of locks

MyISAMTable level lock is used by default, row level lock is not supported.

InnoDBRow level locks are used by default, and table level locks are also supported.

Read lock / shared lock: othersessionCan read, but can’t write.

Write lock / exclusive lock: othersessionCan’t read or write.

MyISAMWhen querying data, a table level read lock is automatically added to the table; when adding, deleting or modifying data, a table level write lock is added to the table. When the “read lock” is not released, anothersessionWriting to this table can be blocked.

InnoDBThe row lock of is similar to the lock of a row. If it is a read lock, other transactions can read but not write. If it is a write lock, other transactions can neither read nor write.

InnoDBThe transaction will be automatically opened for the add, delete, modify and query statement, and will be automatically committed or rolled back. After the transaction is committed or rolled back, the lock will be automatically released at the same time.

If there is no transaction, the whole table will be traversed and promoted to table lock.

MyISAMSuitable scene

  1. Frequent execution of full tablecountSentence.MyISAMThe number of rows for the entire table is saved.
  2. The frequency of adding, deleting and modifying data is not high, and the query is very frequent.
  3. It is suitable for scenarios without transactions.

InnoDBSuitable scene

  1. Data addition, deletion, modification and query are quite frequent.
  2. The reliability requirements are relatively high, and support transactions are required.

Lock classification

By lock granularity

Table level lock, row level lock and page level lock.

Table level locks are fast, but they have more conflicts and fewer row level conflicts, but they are slow. The page level lock is between the two, locking the adjacent set of records.

By lock level

Shared lock, exclusive lock.

Divided by locking mode

Automatic lock, explicit lock.

By operation

DMLLock,DDLLock.

By usage

Optimistic lock, pessimistic lock.

Optimistic lock: it is optimistic during the operation and believes that the operation will not cause concurrency problems (no other thread will modify the data), so it will not lock. However, when updating, it will judge whether other threads have modified the data before this.

Pessimistic lock: always assume the worst case. Every time you fetch data, you think that other threads will change, so you will add (pessimistic) lock. Once the lock is applied, only one thread can execute when different threads execute at the same time. Other threads wait at the entrance until the lock is released.

Four characteristics of database transaction

ACID

A: atomicity, all operations contained in the transaction are either all successful or all failed.
C: consistency. Transactions ensure that the data only has the pre operation state and post operation state. Intermediate state will never occur during access.
I: isolation. When multiple transactions are executed concurrently, the execution of one transaction should not affect other transactions.
D: persistence. Once a transaction is committed, changes to the data are permanently saved.

Transaction isolation level and concurrent access problems at all levels

Isolation level of MySQL transactions

  1. READ-UNCOMMITTED: in one transaction, uncommitted data changes of other transactions can be read.Not recommended in production environments.
  2. READ-COMMITTED: in one transaction, you can read data changes that have been committed by other transactions. yesOracleDefault transaction isolation level.
  3. REPEATABLE-READ: in one of the transactions, the data seen at the beginning of the transaction can be read repeatedly until the end of the transaction. yesMySQLDefault transaction isolation level.
  4. SERIALIZABLE: add table level shared lock to each read data row and table level exclusive lock to each write of data. This will causeInnoDBWhen the concurrency capability of is reduced, a lot of timeout and lock contention will occur.Not recommended in production environments.

Problems caused by concurrent transaction access and how to avoid them

  1. Update lost:MySQLAll transaction isolation levels can be avoided at the database level.
  2. Dirty read: one transaction reads the uncommitted data of another transaction,READ-COMMITTEDTransaction isolation level above can be avoided.
  3. Non repeatable reading: other transactions are committed, resulting in inconsistent results when reading the same data multiple times,REPEATABLE-READTransaction isolation level above can be avoided.
  4. Unreal reading: updatingMData. Other transactions added or deleted data, but succeededM+N/M-NBar, it’s hallucinating,SERIALIZABLETransaction isolation level can be avoided. howeverMySQLOfInnoDBEngine inREPEATABLE-READIt avoids the phenomenon of unreal reading.

How to avoid unreal reading under InnoDB repeatable-read isolation level

Image: snapshot read (pseudoMVCC

Internal:Next-key lock(row lock +)gapLock)

Current and snapshot reads

Current read: that is to say, add, delete, modify and query statements with lock are used to read the latest version of the record. After reading, it is also necessary to ensure that other concurrent transactions cannot modify the current record, so lock is needed.

Snapshot read: non blocking read without lock,SELECT。 In order to improve the concurrent performance, there is no conflict between reading and writingMVCC, which avoids the lock operation and reduces the cost.

MVCC: multi version concurrency control, avoid lock, save data snapshot at a certain time point.

Next-key lock

Row lock: lock a single row.

GapLock: lock the interval.

Will gap lock be used for primary key index or unique index?

IfWHEREIf all conditions are hit, it will not be usedGapLock, only record lock will be added.

IfWHEREConditional partial hit or total miss, theGapLock.

Gap locks are used in current reads that are not unique indexes or do not walk indexes

Non unique index

Only the adjacent sections of the operation will be locked.

No index

All sections will be locked.

How to realize non blocking read of InnoDB at RC and RR level

  • In the data lineDB_TRX_IDDB_ROLL_PTRDB_ROW_IDField.
  • undoJournal.
  • read view, which version of data can be seen by the current transaction.