🔒 global lock

locks the entire database instance. Use the Flush tables with read lock (FTWRL) to keep the entire database read-only, after which the database update statement (add, delete), data definition statement (build, modify table structure), and update class transaction commit statement will be blocked

locks the entire database, so an obvious use case for global locks would be to do a full library logical backup; There is another way to use set global readonly=true to make the entire library read-only, with one big difference:

exception handling, if the client is abnormal to disconnect, MySQL will automatically release the global lock, so that the whole library back to the normal state; After the entire library is set to READOnly, the database will remain readOnly after the exception is disconnected, resulting in the database being unwritable for a long time.

🔒 table level lock

MySQL has two table level locks: table lock and meta data lock (MDL)

table lock syntax lock tables... Read /write, unlock unlock talbes. It is automatically released when the client is disconnected. In addition to restricting the reading and writing of other threads, table locks also restrict the following operation objects of this thread. For example, thread A executes lock tables t1 read,t2 write, while other threads write t1 and read t2 are blocked. Before tables, A thread can only read t1 and write t2

metadata lock is automatically added when accessing a table to ensure that the table structure cannot be modified

when accessing data

🔒 row locks

MySQL row lock is implemented in each storage engine, InnoDB on the support of row lock, MyISAM engine does not support row lock

row lock is the lock that locks A row of records in the table. When A is processing this row of data and B also wants to process this data, B can only continue

after A has finished processing

two-stage locking protocol

in InnoDB transactions, row locks are added when they are needed, but they are not immediately released when they are not needed, but wait until the current transaction is completed.

so if more than one row needs to be locked in a transaction, put the rows that are most likely to cause concurrency as far back as possible.

an 🌰 :

the ticketing business of a ticketing system is approximately:

1. customer A ticket, A balance to deduct price

2. cinema B account balance increase ticket

3. record transaction log

   , the point of concurrency is cinema B to increase revenue. If each customer purchases tickets in the business order of 1, 2, and 3, then concurrent point 2 will hold the lock for a long time. If you change it to 3, 1, or 2 after processing the concurrent points, the row-level lock is released, reducing the wait time for locks between things.

   deadlock and deadlock detection

   different threads hold each other’s locks, A and B release the lock, B and A release the lock, then deadlock

   deadlock treatment strategy:

   1. one strategy is to go straight into the wait until the timeout occurs. This timeout can be set with the parameter innodb_lock_wait_timeout to

   2. to initiate deadlock detection. When a deadlock is found, it will actively roll back a transaction in the death chain bar to allow other transactions to continue execution. The parameter innodb_deadlock_detect is set to on, indicating that this logic is turned on,

      . Note here:

      (1) deadlock detection is performed only when there is a row lock on the accessed row

      (2) not all transactions are scanned for each deadlock detection. For example, at some point, the transaction waiting state is like this:

      , B is waiting for A, D is waiting for C, and now there is an E, and it is found that E needs to wait for D, then E will judge whether there will be deadlock with D and C, this detection does not care about B and A