Database high concurrent requests, how to ensure data integrity? Detailed Mysql/innodb Lock

This article is on the Mysql/innodb, optimistic lock, pessimistic lock, shared lock, exclusive lock, row lock, table lock, deadlock concept understanding, these are often encountered in the interview, such as database high concurrent requests, how to ensure data integrity? Today, I looked up the information on the MYSQL/INNODB in the lock knowledge points in the summary, so you will not feel very cumbersome and messy, feel useful words continue to share it. Apache PHP MySQL

Note: MySQL is a database system that supports the plug-in storage engine. All the descriptions below are based on the InnoDB storage engine, and the performance of the other engines will be significantly different.

Storage Engine View

MySQL provides developers with the ability to query the storage engine, and I use MySQL5.6.4, which can be used:

SHOW ENGINES

begin！

Optimistic lock

Implemented using the data version record mechanism, which is the most common way to implement optimistic locking. What is a data version? is to add a version identifier to the data, typically by adding a "version" field of a numeric type to the database table. When the data is read, the value of the version field is read together, and the data is updated every time the version value is added 1. When we submit an update, the current version of the corresponding record of the database table is judged to be compared with the first fetch, if the current version number of the database table is equal to the first one taken out, it is updated, otherwise it is considered to be outdated data.

Example

1. database table Design

Three fields, respectivelyid,value、version

Select Id,value,version from TABLE where Id=#{id}

2. You need to do this every time you update the Value field in the table to prevent conflicts

Update Tableset Value=2,version=version+1where Id=#{id} and Version=#{version};

Pessimistic lock

The optimistic lock corresponds to the pessimistic lock. Pessimistic locking is the operation of the data, it is believed that the operation will be a data conflict, so in each operation by acquiring a lock to do the same data operation, which is similar to the Java synchronized, so pessimistic lock takes more time. In addition to the optimistic lock corresponding to the pessimistic lock is implemented by the database itself, to use, we directly call the database of related statements can be.

Here, the two other lock concepts involved in the pessimistic lock come out, and they are shared locks and exclusive locks. shared and exclusive locks are different implementations of pessimistic locks, both of which fall into the category of pessimistic locks.

Use, exclusive lock example

To use pessimistic locking, we must turn off the auto-commit property of the MySQL database, because MySQL uses the autocommit mode by default, which means that when you perform an update operation, MySQL will immediately submit the results.

We can use the command to set MySQL to non-autocommit mode:

Set autocommit=0;# after setting up autocommit, we can execute our normal business. as follows: # 1. Start transaction begin;/begin Work;/start transaction; (You can choose one of them) # 2. Query table information Select Status from table where id=1 for update;# 3. Inserts a data insert into TABLE (Id,value) values (2,2); # 4. Modify the data for update TABLE set value=2 where id=1;# 5. Submit transaction Commit;/commit work;

Shared locks

A shared lock, also known as a read lock, is a lock created by a read operation. Other users can read data concurrently, but no transaction will be able to modify the data (to get an exclusive lock on the data) until all shared locks have been freed.

If transaction T adds a shared lock to data A, the other transaction can only have a plus shared lock, and cannot add an exclusive lock. A transaction that obtains a shared lock can read only data and cannot modify the data

Open the first query window

Begin;/begin Work;/start transaction;  (You can choose one of the three) SELECT * from TABLE where id = 1  lock in share mode;

Then in another query window, update the data with ID 1

Update  TABLE set name= "www.souyunku.com" where id = 1;

At this point, the operator interface into the status of the lag, over time, prompt error message

If executed before the time-out, the commit UPDATE statement succeeds.

[Sql]update  Test_one set name= "www.souyunku.com" where ID =1;[ ERR] 1205-lock wait timeout exceeded; Try restarting transaction

The error message is also prompted when the shared lock is added

Update  Test_one set name= "www.souyunku.com" where ID =1 lock in share mode;

[Sql]update  Test_one set name= "www.souyunku.com" where ID =1 lock in Share mode;[ ERR] 1064-you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' lock in Share mode ' at Li NE 1

After the query statement is LOCK IN SHARE MODE incremented, MySQL adds a shared lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, the shared lock can be successfully requested, otherwise it will be blocked. Other threads can also read tables that use shared locks, and those threads are reading the same version of data.

When you add a shared lock, the update,insert,delete lock is automatically added to the statement.

Exclusive lock

Exclusive lock exclusive Lock (also known as writer Lock) is also called a write lock .

exclusive Lock is an implementation of pessimistic lock, also introduced in the above pessimistic lock .

If transaction 1 adds an X lock to the data object A, transaction 1 can read a or modify a, and the other transaction can no longer lock on a, until the thing 1 releases the lock on A. This ensures that other transactions cannot read and modify a until the lock on object 1 is released. Exclusive locks block all exclusive locks and shared locks.

Read why read the lock: Prevent the data from being read by the other thread with the write lock,

How to use: add it after the statement you want to execute for update .

Row lock

A row lock is a shared lock and an exclusive lock , which is understood by the literal meaning to add a lock to a row, which is a record plus a lock.

Note : Row-level locks are index-based, and table-level locks are used if an SQL statement does not use a row-level lock for the index.

Shared locks:

Noun interpretation: A shared lock is also called a read lock, all transactions can only read the operation can not write operations, plus a shared lock before the end of the transaction can only add shared locks, other than any other type of lock can not be added.

SELECT * from TABLE where id = ' 1 '  lock in share mode;  The data for the result set will be shared locks

Exclusive Lock:

Noun explanation: If something adds an exclusive lock to a line, it can only read and write to it, and before the end of the transaction, other transactions cannot lock it, other processes can read, cannot write, and waits for its release.

Select status from TABLE where id=1 for update;

You can refer to the shared lock, exclusive lock statement previously demonstrated

Because the ID field is the primary key for the table, it is also equivalent to an index. When executing locking, a record with the ID of 1 is added to the lock, and the lock is a row lock.

Table lock

How to add a table lock

InnoDB a row lock is a table that has no index locked in the case of an index.

Row locks and table locks in InnoDB

As mentioned earlier, both row and table locks are supported in the InnoDB engine, so when will the entire table be locked, or only one line locked?
InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance.

Row-level locks are index-based, and table-level locks are used if an SQL statement does not use a row-level lock for the index. The disadvantage of row-level locks is that they require a large number of lock resources to be requested, so they are slow and memory consuming.

Dead lock

Deadlock (Deadlock)
The so-called deadlock: refers to two or more than two processes in the execution process, because of the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to proceed. At this point the system is in a deadlock state or the system generates a deadlock, and these processes, which are always waiting on each other, are called deadlock processes. Because the resource consumption is mutually exclusive, when a process requests resources, so that the process without external assistance, never allocated the necessary resources and can not continue to run, which creates a special phenomenon of deadlock.

There are two ways to unlock the state of a deadlock:

The first type :

1. Check if the table is locked

Show OPEN TABLES where In_use > 0;

2. Query the process (if you have super privileges, you can see all the threads.) Otherwise, you can only see your own threads)

Show Processlist

3. Kill the Process ID (which is the ID column of the above command)

Kill ID

The second type :

1: View the current transaction

SELECT * from INFORMATION_SCHEMA. Innodb_trx;

2: View the currently locked transactions

SELECT * from INFORMATION_SCHEMA. Innodb_locks;

3: View transactions for the current lock

SELECT * from INFORMATION_SCHEMA. Innodb_lock_waits;

Kill process

Kill thread ID

If the system has sufficient resources, the resource requests of the process can be met, the likelihood of deadlocks is very low, otherwise it will be locked into a deadlock because of the contention for limited resources. Second, the process is run in a different order and speed, and may also produce a deadlock.
The four necessary conditions for creating a deadlock:

(1) Mutex condition: A resource can only be used by one process at a time.
(2) Request and hold condition: When a process is blocked by a request for resources, it remains in place for the resources that have been obtained.
(3) Conditions of deprivation: the resources that the process has acquired cannot be forcibly deprived of until the end of its use.
(4) Cyclic waiting condition: a cyclic waiting resource relationship is formed between several processes.

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Minimizing deadlocks can increase the throughput of transactions and reduce overhead, because only a few transactions are rolled back, and rollback cancels all work performed by the transaction. Resubmitted by the application because of a deadlock rollback.

The following methods help to minimize deadlocks:

(1) Access the object in the same order.
(2) Avoid user interaction in the transaction.
(3) Keep the transaction short and in a batch process.
(4) Use a low isolation level.
(5) Use a bound connection.

end!