Shulou(Shulou.com)06/01 Report--

Editor to share with you how to ensure data integrity with high concurrent requests in the database. I hope you will gain a lot after reading this article. Let's discuss it together.

Storage engine View

MySQL provides developers with the function of query and storage engine. I use MySQL5.6.4 here, and you can use:

SHOW ENGINES

Begin!

Optimistic lock

It is implemented with the data version (Version) recording mechanism, which is the most commonly used implementation of optimistic locking. What is the data version? That is, to add a version ID to the data, usually by adding a numeric type "version" field to the database table. When reading the data, the value of the version field is read out together, and each time the data is updated, add 1 to the version value. When we submit the update, we judge that the current version information recorded in the database table is compared with the version value taken out for the first time, and if the current version number of the database table is equal to the version value taken out for the first time, it will be updated, otherwise it is considered to be out-of-date data.

Give an example

1. Database table design

Three fields, id,value and version

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

2. Every time you update the value field in a table, you need to do this to prevent conflicts.

Update TABLEset value=2,version=version+1where id=# {id} and version=# {version}; pessimistic lock

Corresponding to the optimistic lock is the pessimistic lock. Pessimistic lock means that there will be data conflicts when operating data, so it is necessary to acquire a lock to operate on the same data every time. This is very similar to synchronized in java, so pessimistic locking takes a lot of time. In addition, corresponding to the optimistic lock, the pessimistic lock is realized by the database itself. when we want to use it, we can directly call the relevant statements of the database.

At this point, there are two other lock concepts involved in pessimistic locks, namely shared locks and exclusive locks. Shared lock and exclusive lock are different implementations of pessimistic lock, and they both belong to the category of pessimistic lock.

Examples of using and exclusive locks

To use pessimistic locks, we must turn off the autocommit property of the mysql database, because MySQL defaults to autocommit mode, that is, when you perform an update operation, MySQL commits the results immediately.

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

After set autocommit=0;# sets up autocommit, we can carry out our normal business. The details are as follows: # 1. Start transaction begin;/begin work;/start transaction; (choose one of the three) # 2. Query table information select status from TABLE where id=1 for update;# 3. Insert a piece of data insert into TABLE (id,value) values (2); # 4. Modify the data to update TABLE set value=2 where id=1;# 5. Commit transaction commit;/commit work; shared lock

A shared lock, also known as a read lock read lock, is a lock created by a read operation. Other users can read data concurrently, but no transaction can modify the data (acquire exclusive locks on the data) until all shared locks have been released.

If transaction T adds a shared lock to data A, other transactions can only add a shared lock to A, not an exclusive lock. Transactions that acquire shared locks can only read data and cannot modify data

Open the first query window

Begin;/begin work;/start transaction; (one of the three is fine) 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 operation interface enters the stutter state, and the error message is prompted after the timeout.

If commit is executed before the timeout, the update statement will succeed.

[SQL] update test_one set name= "www.souyunku.com" where id = 1; [Err] 1205-Lock wait timeout exceeded; try restarting transaction

After adding the shared lock, the error message is also prompted.

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 have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

Adding LOCK IN SHARE MODE,Mysql after the query statement adds a shared lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables that use shared locks, and these threads read the same version of data.

When shared locks are added, exclusive locks are automatically added to update,insert,delete statements.

Exclusive lock

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

Exclusive lock is an implementation of pessimistic lock, which is also introduced above.

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

Why should read locks be added to read: prevent data from being locked by other threads when it is read

How to use: add for update to the statement to be executed.

Row lock

Row locks are divided into shared locks and exclusive locks, which literally means to add a lock to a line, that is, a record.

Note: row-level locks are index-based, and table-level locks will be used if an SQL statement does not use an index.

Shared lock:

Noun explanation: a shared lock is also called a read lock, and all transactions can only read it, not write it, and other transactions can only add a shared lock before the transaction ends. In addition, no other type of lock can be added.

SELECT * from TABLE where id = "1" the data of the lock in share mode; result set will be shared locked

Exclusive lock:

Noun explanation: if something adds an exclusive lock to a row, it can only be read and written by this transaction. before the end of this transaction, other transactions cannot add any locks to it, and other processes can read and cannot write. We need to wait for it to be released.

Select status from TABLE where id=1 for update

You can refer to the shared lock and exclusive lock statement demonstrated earlier

Because the id field is the primary key for a table, it is also equivalent to an index. When locking is performed, the record with index 1 of id is locked, so the lock is a row lock.

Watch lock

How to add a table lock

Innodb's row lock is when there is an index, and a table without an index locks the whole table.

Row Lock and Table Lock in Innodb

As mentioned earlier, both row and table locks are supported in the Innodb engine, so when will the entire table be locked and when or only one row will be locked?

InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may lead to a large number of lock conflicts, thus affecting concurrency performance.

Row-level locks are index-based, and table-level locks are used if an SQL statement does not use an index. The disadvantage of row-level locking is that it needs to request a lot of lock resources, so it is slow and consumes a lot of memory.

Deadlock

Deadlock (Deadlock)

The so-called deadlock: refers to two or more processes in the implementation process, caused by the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to move forward. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes. Because the occupation of resources is mutually exclusive, when a process applies for resources, the process can never allocate the necessary resources and can not continue to run without external assistance, which leads to a special phenomenon deadlock.

There are two ways to release a deadlock state:

The first kind:

1. Query whether to lock the table

Show OPEN TABLES where In_use > 0

two。 Query the process (if you have SUPER permission, you can see all 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 kind:

1: view the current transaction

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

2: view currently locked transactions

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

3: view the transactions currently waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

Kill process

Kill thread ID

If the system resources are sufficient and the resource requests of the process can be met, the possibility of deadlock is very low, otherwise it will fall into a deadlock because of competing for limited resources. Secondly, the process runs in different order and speed, which may also lead to deadlock.

Four necessary conditions for creating a deadlock:

(1) Mutual exclusion condition: a resource can only be used by one process at a time.

(2) request and retention conditions: when a process is blocked by a request for resources, it holds on to the resources it has acquired.

(III) non-deprivation conditions: resources that have been acquired by the process cannot be forcibly deprived until they have been used up.

(4) cyclic waiting condition: a circular waiting resource relationship is formed between several processes.

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Reducing deadlocks to a minimum increases transaction throughput and reduces system overhead because there are very few transaction rollbacks, which cancel all work performed by the transaction. Resubmitted by the application because it is rolled back during a deadlock.

The following methods help minimize deadlocks:

(1) access objects in the same order.

(2) avoid user interaction in transactions.

(3) keep transactions short and in a batch.

(4) use a low isolation level.

(5) use binding to connect.

End!

After reading this article, I believe you have a certain understanding of how to ensure data integrity with high concurrent requests in the database. If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.