mysql- (transaction, lock, isolation level)


4 attributes:

  1. Atomicity: The programs included in the transaction are either all executed or not executed at all (do one thing, do it and finish it, or don't do it)
  2. Consistency: After the data operation is performed, the data will not be destroyed (a user transfers the money to user b, and as much as a deduction is made, b must be added)
  3. Isolation: The data of each transaction operation cannot affect each other. (Two transaction modification, query, and operation on one value at the same time. Open the transaction at the same time, the data is 5, the data is first modified to 6. Complete, submit the transaction. But the query transaction data must be 5. It will not be due to another transaction. Data changes, but there are changes)
  4. Persistence: After the transaction is completed, the modification of the data is permanent

Concurrent transactions:

  1. Update loss or dirty write: Two transactions modify one data concurrently, and the data may be overwritten, resulting in data loss (a is equal to 5, and two transactions perform an increase operation. Transaction 1: Increase 1, Transaction 2: Increase 2. If executed sequentially , The value of a should be equal to 8. The occurrence of concurrent operations may become a equal to 6 or 7. Because transaction one and transaction two, the obtained a is 5)
  2. Dirty read: Transaction a reads the modified but committed data of transaction b (if transaction b rolls back, the data read by a will be invalid)
  3. Non-repeatable read (data modification) : The transaction is opened but not submitted, and the same statement is queried twice (the data is modified during the period), and the results obtained are different. (In one thing)
  4. Phantom read (data addition) : In the transaction, transaction a reads the data submitted by transaction b (one table, three data. A transaction query, b transaction new. Two transactions are opened at the same time, b transaction is completed first. Transaction a queries the newly added data of transaction b. When transaction a is opened, there are only three data in the table, transaction a can only query how much data there is in the table when the transaction is opened)

Isolation level:

  1. Read uncommitted: transaction a can read data that is not committed by transaction b
  2. Read committed: transaction a can only read the data submitted by transaction b
  3. Repeatable read: transaction a can only be read, data that already exists in the database when transaction a is opened (modified data cannot be read. New data can be read)
  4. Serializable: One transaction is opened, other transactions cannot be operated (set serialization (query will also be locked), one transaction queries a piece of data, then other transactions cannot operate on this data.)


Performance points: optimistic locks and pessimistic locks (read locks and write locks are both pessimistic locks)

Database operation:

  1. Read lock: for the same sub-data, multiple read operations at the same time do not affect each other
  2. Write lock: before the current write operation is completed, it will block other write locks and read locks

Database granularity:

  1. Table lock: lock the table (each operation locks the entire table. The overhead is small and the lock is fast; there will be no deadlocks, and the probability of lock conflicts with large granularity is high)
  2. Row lock: row data lock (locking a row of data per operation, high overhead, slow locking, deadlocks, small granularity, high concurrency, and small conflicts)


Open a client A, and set the current transaction mode to serializable, query id=1

Open a client A, and set the current transaction mode to serializable,

  1. Query id=1, it will be blocked
  2. Query or update id=2, it will be executed

Gap lock:

If a transaction query is a range query, if b transaction operates data within the scope of a. Then b will be blocked.

Pro key lock: also a range

No index row lock, query will upgrade the table lock

Lock optimization suggestions

1: As far as possible, all data retrieval is completed through the index, to avoid the upgrade of non-indexed row locks to table locks    2: Design the index reasonably to minimize the scope of the lock    3: Minimize the scope of retrieval conditions as much as possible to avoid gap locks    4: Try to control the size of the transaction, reduce the amount of locked resources and the length of time, and try to place the SQL involving transaction locks at the end of the transaction for execution    5: As low-level transaction isolation as possible

Reference link: