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

This article introduces the relevant knowledge of "what is the locking mechanism of MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Why lock it?

Problem background

When multiple users access data concurrently, multiple transactions will access the same data at the same time in the database. If there is no control over concurrent operations, incorrect data may be read and stored, and the consistency of the database will be destroyed.

The problem to be solved

Ensuring database integrity and consistency in multi-user environment

What's the lock?

In computer science, lock is a synchronization mechanism used to forcibly restrict resource access when multithreading is executed, that is, it is used to meet the requirements of mutual exclusion in concurrency control.

Locking is a very important technology to realize database concurrency control. When a transaction makes a request to the system and locks it before operating on a data object. After locking, the transaction has some control over the data object, and other transactions cannot update the data object until the transaction releases the lock.

Classification of locks

OK

Row level lock

Row-level locks are the finest-grained locks in Mysql, indicating that locks are only applied to the rows of the current operation. Row-level locks can greatly reduce conflicts in database operations. The locking granularity is the smallest, but the locking overhead is also the largest. Row-level locks are divided into shared locks and exclusive locks.

Characteristics

Cost D, slow locking; deadlock occurs; locking granularity is the smallest, F-born lock conflict probability is the lowest, and F-degree is the highest.

Table

Table-level lock is the largest lock granularity in MySQL, which means locking the whole table of the current operation. It is easy to implement, low resource consumption, and is supported by most MySQL engines. Both MYISAM and INNODB, the most commonly used, support table-level locking. Table-level locking is divided into table shared read lock (shared lock) and table exclusive write lock (exclusive lock).

Characteristics

The overhead is small and the locking is fast; there is no deadlock; the locking granularity DPowerF has the highest probability of lock conflict and the lowest concurrency.

Page

Page-level lock is a lock whose granularity is between row-level lock and table-level lock in MySQL. The speed of table-level lock is fast, but the conflict is more, the row-level conflict is less, but the speed is slow. So take the eclectic page level and lock the adjacent set of records one at a time. BDB supports page-level locks

Characteristics

The overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average.

Locking Mechanism of commonly used Storage engine in MySQL

MyISAM and MEMORY use table-level locks (table-level locking)

BDB uses page lock (page-level locking) or table level lock. The default is page lock.

InnoDB supports row-level locks (row-level locking) and table-level locks. The default is row-level locks.

InnoDB row locking is achieved by locking index items on the index. The implementation feature of InnoDB means that InnoDB uses row-level locks only when data is retrieved through 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.

Example illustration

The default data modification statement of the MySQL InnoDB engine: update,delete,insert automatically adds an exclusive lock to the data involved.

The select statement does not add any lock type by default. If you add an exclusive lock, you can use select. For update statement, with shared lock, you can use select. Lock in share mode statement.

Therefore, data rows with exclusive locks cannot be modified in other transactions, nor can they be queried through for update and lock in share mode locks, but directly through select. From... Query data because a normal query does not have any locking mechanism.

Row-level lock and deadlock

Deadlocks do not occur in MyISAM, because MyISAM always gets all the locks needed at once, either satisfying them all or waiting for them all. In InnoDB, the lock is acquired step by step, resulting in the possibility of deadlock.

In MySQL, row-level locks are not direct lock records, but lock indexes. Indexes are divided into primary key index and non-primary key index. If a sql statement operates on a primary key index, MySQL will lock the primary key index; if a statement operates on a non-primary key index, MySQL will lock the non-primary key index first, and then lock the relevant primary key index. During UPDATE and DELETE operations, MySQL locks not only all index records scanned by WHERE conditions, but also adjacent key values, called next-key locking.

When two transactions execute at the same time, one locks the primary key index and waits for other related indexes. The other locks the non-primary key index and is waiting for the primary key index. This will lead to a deadlock.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock and roll back, while the other acquires the lock to complete the transaction.

Shared lock and exclusive lock

Shared lock (Share Lock)

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 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 are allowed to share locks can only read data and cannot modify it.

Usage SELECT... LOCK IN SHARE MODE

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.

Exclusive lock (eXclusive Lock)

An exclusive lock is also called a write lock. If transaction T adds an exclusive lock to data A, other transactions can no longer add any kind of lock to A. Transactions that are granted an exclusive lock can both read and modify data.

Usage SELECT... FOR UPDATE

Adding FOR UPDATE,Mysql after the query statement adds an exclusive 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 an exclusive lock, otherwise it will be blocked.

Optimistic lock (Optimistic Lock)

What is it

It is assumed that the data will not cause conflicts in general, so when the data is submitted for update, the conflict of the data will be formally detected. If a conflict is found, the wrong information of the user will be returned and the user will decide what to do.

Compared with pessimistic locks, optimistic locks do not use the locking mechanism provided by the database when dealing with the database. The general way to implement optimistic locking is to record the data version.

Data version, an additional version identification for the data. When reading the data, the value of the version identification is read out together, and each time the data is updated, the version identification is updated at the same time. When we submit the update, we judge that the current version information recorded in the database table is compared with the version identification taken out for the first time, and if the current version number of the database table is equal to the version identification value taken out for the first time, it will be updated, otherwise it is considered out-of-date data.

There are two ways to implement a data version, the first is to use the version number, and the second is to use a timestamp.

Use version number to implement optimistic lock

When using a version number, you can specify a version number during data initialization, and each update to the data performs a + 1 operation on the version number. And determine whether the current version number is the latest version number of the data.

1. Query the commodity information select (status,status,version) from t_goods where id=# {id} 2. Generate order 3 according to commodity information. Modify the product status to 2update t_goodsset status=2,version=version+1where id=# {id} and version=# {version}

Advantages and disadvantages

Optimistic concurrency control believes that the probability of data contention between transactions (data race) is relatively small, so do as much as possible and do not lock until commit, so there are no locks or deadlocks. However, if you simply do this directly, you may still encounter unexpected results. For example, if both transactions read a row in the database and write it back to the database after modification, you will encounter a problem.

Pessimistic lock (Pessimistic Lock)

What is it

The data is locked during the whole data processing process. The implementation of pessimistic locking often depends on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can really ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in this system, there is no guarantee that the external system will not modify the data)

The process of pessimistic locking

Before making changes to any record, try adding an exclusive lock (exclusive locking) to the record.

If locking fails, the record is being modified, and the current query may have to wait or throw an exception. The specific response mode is decided by the developer according to the actual needs.

If the lock is successful, the record can be modified and unlocked after the transaction is completed.

In the meantime, if there are other operations to modify the record or add an exclusive lock, it will wait for us to unlock or directly throw an exception.

Use pessimistic lock in MySQL InnoDB

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. Set autocommit=0

/ / 0. Start transaction begin;//1. Query out the commodity information select status from t_goods where id=1 for update;//2. Generate order insert into t_orders (id,goods_id) values (null,1) according to commodity information; / / 3. Change the commodity status to 2update t_goods set status=2;//4. Commit transaction commit

In the above query, we used select... For update, which achieves pessimistic locks by opening exclusive locks. At this point, in the t _ goods table, the data with an id of 1 is locked by us, and other transactions must wait until this transaction is committed. In this way, we can ensure that the current data will not be modified by other transactions.

Locking Mechanism of Java

Synchronization of threads

Before a piece of synchronized code is executed by a thread, he needs to get the permission to execute the code. In java, he gets the lock of a synchronization object (an object has only one lock). If the lock of the synchronization object is taken away by another thread, he (the thread) can only wait (the thread is blocked in the lock pool waiting queue). After getting the lock, he starts executing the synchronization code (code decorated by synchronized); after the thread executes the synchronization code, the lock is returned to the synchronization object, and some other thread waiting in the lock pool can get the lock to execute the synchronization code. This ensures that only one thread is executing the synchronization code at a unified time.

Synchronization method for threads:

1. Add the synchronized keyword to the method signature of the method that needs synchronization.

two。 Use synchronized blocks to synchronize code snippets that need to be synchronized.

3. Use the Lock object in the java.util.concurrent.lock package provided in JDK 5.

ThreadLocal

When using ThreadLocal to maintain a variable, ThreadLocal provides an independent copy of the variable for each thread that uses the variable, so each thread can change its own copy independently without affecting the corresponding copy of other threads. In the ThreadLocal class, there is a Map that stores the variable copy of each thread, the key of the element in Map is the thread object, and the value corresponds to the variable copy of the thread. Typical scenarios using ThreadLocal, such as database connection management and thread session management, are only suitable for independent variable copies, but not for high concurrency if the variables are globally shared.

This is the end of the content of "what is the locking mechanism of MySQL database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.