13.MySQL lock mechanism

Classification of Locks

From the type of data (read \ Write) points:

1. Read lock (Shared lock): For the same data, multiple read operations can be performed simultaneously without affecting each other

2. Write lock (Exclusive lock): Before the current write operation is completed, it will block other write and read locks

From the granularity of the data operation:

1. Table lock

2. Row lock

Table lock (partial read)

1. Biased to MyISAM storage engine, low overhead, fast lock, no deadlock, high lock size, highest probability of lock conflict, lowest concurrency

2.MyISAM before executing the query (SELECT) statement, the read lock is automatically given to all tables involved, before the update operation (Update/delete/insert, etc.) is performed.

will automatically write locks to the table involved, this process does not require user involvement, we tried to use lock table in order to simulate the concurrency environment

Read lock

1. Session One in a table read lock (lock table TableName Read), session two do not lock any table

2. Session one can query a table, can only query a table, the write operation of a table error, the other table read and write operation also error

3. Session Two, you can read a table, or read and write to other tables, but only for a table write operation will be blocked, must wait for session one to release a table read lock (unlock tables), session two to complete the write operation

Add write Lock

1. Connect one, write a lock on a table, this connection can read and write this table, but cannot access other tables

2. Connection Two can access other unlocked tables, but the read and write of the table with the write lock is blocked (that is, exclusive), waiting for the release of the lock in the connection

In short, the read lock blocks the write, but does not block the read, while the write lock blocks both read and write (for other connections)

Table lock analysis Show status like ' table% '

Row lock (offset) (The row lock appears as if it were in a transaction)

1. Biased to INNODB storage engine, high overhead, locking slow, deadlock, lock is the smallest, the probability of lock conflict is the lowest, concurrency is the highest

2.Innodb and MyISAM The biggest difference has two points: one is just business (TRANSACTION), and the second is the use of row-level lock

　　

3. As the row lock support transaction, we review the knowledge about the transaction

1. Transactions and their ACID properties

2. Problems caused by concurrent transactions

1. Update missing (Lost update)

2. Dirty Reading (Dirty Reads)

3. Non-repeatable reading (non-repeatable Reads)

During the execution of transaction A, transaction B is opened, modified, and committed, resulting in inconsistent data read by transaction A before transaction B executes and after transaction B executes.

4. Phantom Reading (Phantom Reads)

is similar to non-repeatable read, but cannot be read repeatedly for changes to columns, while Phantom reads are for column increments

3. Transaction ISOLATION LEVEL

　　

4. Line Lock Basic Demo: Updates to data in a transaction will not touch the release lock.

For example, a connection opens a transaction, in a transaction, a row of data in the table is updated (this line is locked) (the query is not locked),

Before the transaction was committed, connection two issued an UPDATE statement that wanted to update the locked row of data, at which point the statement for connection two would be blocked.

Need to wait for connection one thing to commit, line lock release, to continue execution

However, because it is a row lock, the data update of the connection two to other rows is not affected

5. No index row lock upgrade to table lock (index invalidation)

When a row lock occurs, if the row lock lock has a field above it (such as name) indexed, if the index fails (for example, the character type does not add ' where name = 1234 '),

The row lock is automatically upgraded to a table lock, and updates to other rows of data are locked, and other connections cannot update the table

6. Gap lock (rather than the wrong to kill, can not be misplaced)

When we retrieve data using a range condition instead of an equal equality condition, it locks all index key values in the entire range, even if the key value does not exist

Such as: there is such a piece of data, id = 1,id = 3,id = 4 ... in the middle of an id = 2, if the connection at this time to open a transaction, update the data when the 1<id <4,

At this time, MySQL in the wrong to kill, not the principle of misplaced, even if the id=2 does not exist will be ruthless lock,

Connection two The data you want to insert into a id=2 is not working, it blocks, waits for a transaction commit, and the row lock releases

7. How to lock a row

For update can lock a row or lock the entire table

The main is to see if the SELECT statement after the scope of the Where,where limit is the scope of the lock, select XXX ... for update

If there is no where, the entire table is locked, and other connections cannot update the locked range after locking

8. Row lock analysis: Show status like ' innodb_row_lock% '

Why say the line lock offset write, table lock read, that is MyISAM partial write, InnoDB partial read

High performance of Myisam reading

Myisam read-write lock invocation is write-first, which is also Myisam not suitable for writing the main table engine. Because the other threads cannot do anything after the lock is written, a large number of updates can make the query difficult to get locked, causing permanent blocking

InnoDB row locks are single-ratio queries, but InnoDB supports high concurrency, supporting transactions

InnoDB Insert performance is more stable in applications with less write-and-read, and can provide basic performance in concurrent situations

can choose the appropriate MySQL storage engine according to the system's read and write situation

Optimization recommendations

1. Make all data retrieval possible by indexing, avoid escalation of row lock without index to table lock

2. Properly design the index to minimize the range of locks

3. Minimize search conditions and avoid gap locks

4. Try to control transaction size, reduce the amount of lock resources and length of time

5. As low-level transaction isolation as possible

Page lock (Learn about it)

1. Overhead and lock time between table lock and row lock

2. Deadlock will occur

3. Lock granularity between table lock and row lock, concurrency is generally

　　　　

　　

13.MySQL lock mechanism