Series of articles

Tip: Table of contents for all articles

1. Understand the SQL execution process

2. Set up MySQL under Docker & check BinLog file

preface

Whenever you’re talking about modifying data at the same time between multiple pieces of data, you need to talk about concurrency control. At this time the role of the lock to the data is very full of security, when we modify a data, we can lock table, lock line, lock global to ensure the problem of data.

Tip: The following is the text of this article

One, the lock involved

1. Global lock

Lock the entire database. Flush tables with read lock (FTWRL)

Flush the contents of the query cache. FLUSHTABLES to disable the FDS of all open tables Make_global_read_lock_block_commit

Impact: blocking, adding, deleting, and modifying tables. Scenario: Full library logical backup

Question: Is there any other way to lock tables globally? Use set global Readonly =true. FTWRL is better than set global readOnly =true. Because in the event of an abnormal disconnect, the database will return to its normal updated state. Readonly does not.

2. The table level lock

Lock the entire table. There are two types of lock tables and metadata lock (MDL) implementations: InnoDB uses row-level locks to retrieve data by index conditions by locking the corresponding rows in a data block. Otherwise InnoDB will use table locks. What MDL does: Ensures correct reads and writes

Q: How to add a field to a small table safely?

  1. Prevent long transactions, resolve long transactions, long transactions will not commit to hold the lock, SELECT t.*, to_seconds(now()) -to_seconds (t.trx_started) IDLE_time FROM information_schema.innodb_trx t;
  2. Set the wait time in the ALTER TABLE statement, it is best to get the MDL lock.

3. Row-level locks

Lock a row of data. The same data can only be committed by one transaction before another. Features: high overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. Implementation: InnoDB row locks are implemented by locking index entries on indexes

Question: When does Innodb lock rows?

  1. Add as needed and release at the end of the transaction.

Two, cause deadlock how to do?

1. Troubleshooting methods

Set up a three-way changquan to check the problem.-- Query whether the lock table exists
show OPEN TABLES where In_use > 0;
Take a look at the transactions under lock
select * from information_schema.INNODB_TRX;
-- : View the currently locked transaction
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- Query process
SHOW PROCESSLIST;
-- Kill the process ID
kill 199216;
Copy the code

2. What are the strategies for deadlocks?

  1. Set timeout to exit innodb_lock_WAIT_timeout. Default is 50s
  2. Set deadlock detection, roll back one of the transactions, and set Innodb_deadlock_detect to ON

3. Question: What if a large amount of data are used to execute the same data scenario?

Under Strategy 2, does it happen that transactions exit all the time? The magnitude of deadlock detection is huge, the CPU occupation is huge, and the transaction execution is few. To solve?

  1. In scenarios, both read operations are performed, or write operations do not affect each other. You can temporarily turn off deadlock detection.
  2. When concurrency is large, the magnitude of deadlock detection increases too much due to complexity, which can be reduced by limiting concurrency.