According to the scope of the lock

  • Global lock
  • Table level lock
  • Row locks

According to the reason for locking

  • Optimistic locking
  • Pessimistic locking

Global lock

The lock object is: the entire database instance

Flush tables with Read Lock (FTWRL)- Makes the entire library read-only

Usage scenario: Perform full-library logical backup

Full library logical backup

Why do you need a global lock for data backup?

For example, in sales, I record the shipment on one sheet and the deduction on the other. Turns out I was backing up the shipping records. At this time someone bought something, only the charge was deducted but there was no delivery record. This is obviously not going to work

The official logical backup tool is mysqldump. When mysqldump uses the — single-transaction argument, a transaction is started before the data is exported to ensure that the consistency view is retrieved. Due to the support of MVCC, data can be updated normally during this process. However, this is on a transaction basis and the data engine is not available for MyISAM, so it is possible that some tables are not based on innoDB data engine

Of course, if all innoDB data engine tables are used, then it is better to use the default mysqldump parameter – single-transaction for global logical backup

FTWRL and set global readOnly =true

  1. Readonly can be used in other logic (different systems)
  2. FTWRL automatically releases the lock when the client is disconnected. Prevent deadlock problems

Table level lock

Command :lock table {tableName} read/write command: UNLOCK table lock table {tableName} read/write

A locked resource allows only the current thread to perform the corresponding operation. The current thread can only perform operations on locked tables

For example, if lock table T1 read is run, the current thread can only read but cannot write, and other threads cannot read or write

MDL lock

Does not need to be used explicitly, automatically added when accessing a table (to prevent problems with table structure changes)

MDL read locks are used when adding, deleting, or modifying a table, and MDL write locks are used when changing the structure of a table

  • MDL read locks (shared locks), which are not mutually exclusive. Therefore, multiple threads can be allowed to add, delete, change and check simultaneously
  • MDL write lock (exclusive lock), this lock and other read and write lock are mutually exclusive. That is, current data changes or queries or structural changes cannot be executed until other MDL write locks are released

Row locks

MyISAM does not support row locking, so the engine can only update one thread at a time.

In a transaction: a row lock is added when a row is needed, but all row locks are released until the transaction commits.

Thread A performs the following operations begin; update t1 set a=1 where id=1; update t2 set b=2 where id=2; Commit Lock t1 id=1 and t2 id=2. If thread B accesses T1 with id=1, it will not be able to access t1 until thread A commits, even though the first statement has been executedCopy the code

So, for us, if the update is not sequential, try to execute the most accessed statement last (because locks are sequential, but locks are released together).

A deadlock

Deadlocks can occur in many cases, most of which are caused by problems with database operations. Such as

Thread A (id=1) and thread B (id=2) change the lock of thread A (id=1). Thread B (id=2) changes the lock of thread A (id=2) and starts the operation on thread A (id=2) Thread B waits for id=1 to release the lock and then dereports the operation on id=1 to achieve a cyclic deadlockCopy the code

There are two strategies for dealing with this problem:

  1. Wait until time out. This timeout can be set using the innodb_lock_WAIT_timeout (default is 50s) parameter. This can’t be set too short, what if it’s not a deadlock?
  2. Initiate deadlock detection, and when a deadlock is found, actively roll back one of the transactions in the chain so that the other transactions can continue. Setting innodb_deadlock_detect to ON (the default is on) enables this logic.

Optimistic locks and pessimistic locks

concept Optimistic locking Pessimistic locking
concept Assume that no concurrency conflicts occur

Determine if there are data problems only at commit time		 Suppose a concurrency conflict occurs

Thus locked
Implementation level Business code level, their own implementation Mysql database implementation itself
The concurrent situation Concurrent big Concurrent small
implementation Add a version number field to the database,

Check whether the version before the operation is the same as the current version		 Share lock :select lock XXXXXX

Exclusive lock :select XXXX for update
other Synchronized in mysql is an exclusive lock

Shared lock: Run other threadscheckDon’t allowIncreases the deletion

Exclusive locks:Additions, deletions and alterations are not allowed