Shulou(Shulou.com)05/31 Report--

In this article, the editor introduces in detail "what is the concept of mysql lock mechanism", the content is detailed, the steps are clear, and the details are handled properly. I hope this article "what is the concept of mysql lock mechanism" can help you solve your doubts.

Mysql lock:

How to ensure the accuracy of data in multithreading? Yes, through synchronization. Synchronization is the equivalent of locking. What are the benefits of adding a lock? When one thread is actually manipulating the data, other threads can only wait. When a thread finishes executing, the lock is released. Only other threads can operate!

Then the function of locks in our MySQL database is similar. In the isolation of dealing with transactions, there may be problems of dirty reading, unrepeatable reading, and phantom reading, so the role of locks can also solve these problems!

In database, data is a kind of resource for many users to share and access. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases. Because of the characteristics of its own architecture, MySQL has designed locking mechanisms for specific scenarios in different storage engines, so engine differences lead to great differences in locking mechanisms.

Locking mechanism:

In order to ensure the consistency of data, a database uses a variety of shared resources to become orderly when they are accessed concurrently.

For example: when buying goods on an e-commerce website, there is only one item in the list, and two people buy it at the same time, so who can buy it is a key question.

Transactions are used here to perform a series of operations:

Take the data of the item from the commodity list first.

Then insert the order

Insert payment form information after payment

Update the quantity of goods in the commodity list

In the above process, the use of locks can protect the commodity quantity data information and achieve isolation, that is, only the first user is allowed to complete the whole purchase process, while other users can only wait, which solves the contradiction in concurrency.

Classification of locks:

Classified by operation:

Shared lock: also called read lock. For the same data, multiple transaction read operations can be locked at the same time without affecting each other, but the data record cannot be modified.

Exclusive lock: also called write lock. The read and write of other operations will be blocked before the current operation is completed

Classified by granularity:

Table-level lock: when operating, the entire table is locked. The cost is small, the locking is fast, there is no deadlock, the locking strength is large, the probability of lock conflict is high, and the concurrency is the lowest. Prefer the MyISAM storage engine!

Row-level lock: the current operation row is locked during the operation. High overhead, slow locking, deadlock; small locking granularity, low probability of lock conflict and high concurrency. Prefer the InnoDB storage engine!

Page-level locks: the granularity of locks, the probability of conflicts, and the cost of locking are between table locks and row locks, resulting in deadlocks and mediocre performance.

Classified by way of use:

Pessimistic lock: every time you query the data, you think that others will modify it, which is very pessimistic, so add a lock when you query.

Optimistic lock: every time you query the data, you think that others will not modify it, which is very optimistic, but when you update it, you will judge whether others have updated the data during this period.

Locks supported by different storage engines

Shared lock:

Multiple shared locks can be shared. If there is a key, InnoDB defaults to a row lock, and if not, it will be promoted to a table lock. When a row lock is a row lock, multiple windows can modify the data of different peers. Peers need to wait for the lock to be submitted first, and non-peers can modify it directly, but another query will also have to wait for the later modified submission. After the submission, the lock disappeared.

Shared lock:

SELECT statement LOCK IN SHARE MODE

Window 1:

-window 1ax * shared lock: data can be queried by multiple transactions, but cannot be modified * /-- enable transaction START TRANSACTION;-- query data records with id 1. Join the shared lock SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;-- to query data records with a score of 99. Join the shared lock SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;-- commit transaction COMMIT

Window 2:

-- window 2 START TRANSACTION;---enable transaction START TRANSACTION;-- to query data records with id 1 (ordinary query, you can query) SELECT * FROM student WHERE id=1;-- query data records with id 1, and add a shared lock (you can query. Shared lock and shared lock compatible) SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;-- changes the name of id to 1 to Zhang San3 (cannot be modified, lock will occur. Only after window 1 commits a transaction can it be modified successfully) UPDATE student SET NAME=' Zhang San3 'WHERE id = 1-change the name of id to 2 to Li Sisi (modified successfully, InnoDB engine defaults to row lock) UPDATE student SET NAME=' Li Sisi' WHERE id = 2 True-change the name of window 3 to Wang Wuwu (modification failed, if the InnoDB engine does not use indexed column locks. It will be promoted to table lock) UPDATE student SET NAME=' Wang Wu 'WHERE id = 3 Ting Mai-commit transaction COMMIT

Exclusive lock:

When an exclusive lock is executed, ordinary queries for other transactions can be used, but no operations can be locked.

Standard syntax SELECT statement FOR UPDATE

Window 1:

-- window 1 START TRANSACTION;-- * exclusive lock: locked data cannot be queried or modified by other transactions * /-Open transaction START TRANSACTION;-- query data record with id 1, and add exclusive lock SELECT * FROM student WHERE id=1 FOR UPDATE;-- to commit transaction COMMIT

Window 2:

-- window 2 START TRANSACTION;---enables transaction START TRANSACTION;-- to query data records with id 1 (normal query is fine) SELECT * FROM student WHERE id=1;-- queries data records with id 1 and adds a shared lock (cannot be queried). Because exclusive locks cannot coexist with other locks) SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;-- queries the data record with id 1 and adds an exclusive lock (cannot be queried. Because exclusive locks cannot coexist with other locks) SELECT * FROM student WHERE id=1 FOR UPDATE;-- changes the name of id to 1 to Zhang San (cannot be modified, locks will occur. The UPDATE student SET NAME=' Zhang San 'WHERE id=1;-- commit transaction COMMIT;MyISAM lock can be modified only after window 1 commits the transaction:

MyISAM read lock:

Myisam adds a lock to the entire table. When reading the lock, all transactions can be checked if the lock is not unlocked, and no other operations, including its own transactions, can be performed.

-- unlock LOCK TABLE table name READ;-- (unlock all tables in the current session) UNLOCK TABLES

MyISAM write lock:

When writing a lock, as long as no other transaction is unlocked and no operation can be performed, its own transaction can operate.

-- Standard syntax-- locking LOCK TABLE table name WRITE;-- unlocking (unlocking all tables in the current session) UNLOCK TABLES

Pessimistic lock:

It is very pessimistic, it holds a conservative attitude towards the operation that the data is modified by the outside world, and thinks that the data can be modified at any time.

The data needs to be locked in the whole data processing. Pessimistic locks generally rely on the locking mechanism provided by relational databases.

Row locks, table locks, both read and write locks are pessimistic locks.

Optimistic lock:

Is very optimistic, every time I manipulate the data, I think that no one will modify it, so I don't lock it.

However, when it is updated, it will determine whether the data has been modified during this period.

It needs to be implemented by the user, and concurrent preemption of resources will not occur. Only when submitting the operation, check whether the data integrity is violated.

A simple implementation of optimistic locks:

The idea of implementation: add a tag to compare, the same will execute, different will not execute

Method 1: version number

Add a version column to the data table, and add 1 to the value of this column after each update.

When reading the data, the version number is read out, and the version number is compared when the update is performed.

If it is the same, an update is performed, and if it is different, this piece of data has changed.

Users decide what to do according to this notification, such as starting over or abandoning the update.

-- create city table CREATE TABLE city (id INT PRIMARY KEY AUTO_INCREMENT,-- City id NAME VARCHAR (20),-- City name VERSION INT-- version number);-- add data INSERT INTO city VALUES (NULL,' Beijing', 1), (NULL,' Shanghai', 1), (NULL,' Guangzhou', 1), (NULL,' Shenzhen', 1) -- change Beijing to Beijing-- 1. Inquire about versionSELECT VERSION FROM city WHERE NAME=' Beijing';-- 2. Change Beijing to Beijing, version number + 1. And compare the version number UPDATE city SET NAME=' Beijing', VERSION=VERSION+1 WHERE NAME=' Beijing 'AND VERSION=1

Method 2: timestamp

Basically the same as the version number, add a column to the data table, the name does not matter, the data type needs to be timestamp

The latest time is inserted into this column after each update.

When reading the data, the time is read out, and when the update is performed, the time is compared.

If it is the same, an update is performed, and if it is different, this piece of data has changed.

Pessimistic lock and optimistic lock use premise:

When there are far more read operations than write operations, locking an update operation will block all read operations and reduce throughput. Finally, you have to release the lock, which requires some overhead, so you can choose an optimistic lock.

If the gap between read and write ratio is not very large, or the system does not respond in time, and the throughput bottleneck, then do not use optimistic locks, which increases complexity and brings additional risks to the business. At this time, you can choose pessimistic lock.

After reading this, the article "what is the concept of mysql locking mechanism" has been introduced. If you want to master the knowledge of this article, you still need to practice and use it yourself. If you want to know more about the article, you are welcome to follow the industry information channel.

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.