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

The following brings you about the main role of innoDB lock in mysql. I believe you must have read similar articles. What's the difference in what we bring to you? Let's take a look at the body part. I believe that after reading the main role of innoDB lock in mysql, you will definitely gain something.

Why start transaction before locking InnoDB

Release of locks under innodb After transaction commit/rollback, locks in transactions will be automatically released once the transaction is committed/rolled back. innodb autocommit=1 by default enables automatic commit.

The difference between locks that use indexes and locks that do not:

If the search condition has an index, it will lock certain rows.

If the search criteria are not used, a full table scan is performed to lock all rows (including records that do not exist)

Read lock:

Read locks are shared, or mutually non-blocking. Multiple users can read the same resource at the same time without interfering with each other.

Write lock:

Write locks are exclusive, meaning that one write lock blocks all other write locks and read locks. Also, write locks have higher priority than read locks, so a write lock request may be inserted at the front of the read lock queue, but read locks will not be inserted at the front of the write lock queue.

Watch lock:

InnoDB also has two table locks: Intentional Shared Lock (IS) and Intentional Exclusive Lock (IX).

Line Lock:

InnoDB implements two types of row-level locks, shared locks and exclusive locks.

Optimistic lock:

Optimistic locking, also known as optimistic concurrency control, assumes that multi-user concurrent transactions do not affect each other when processed, and that transactions can process their affected portions of data without creating locks. Before committing a data update, each transaction checks to see if any other transactions have modified the data since it read it. If there are updates from other transactions, then the transaction currently committing will be rolled back.

Pessimism lock:

Pessimal locking, also known as pessimistic concurrency control, when transaction A applies a lock to a row of data, and when this transaction releases the lock, other transactions can perform operations that conflict with the lock. Share lock and exclusive lock (row lock, gap lock, next-key lock) belong to pessimistic lock

How pessimistic and optimistic locks are implemented:

Pessimal locks are implemented by the locking mechanism provided by the database, such as select * from news where id=12 for update, while optimistic locks are implemented by recording the data version, that is, by adding the version number field to the table as a key factor for successful submission.

Shared Lock (S):

Shared locks are also called read locks. A transaction acquires a shared lock on a data row, and other transactions can acquire the shared lock corresponding to the row, but cannot acquire exclusive locks. That is, when a transaction reads a data row, other transactions can also read, but cannot add, delete, or modify the data row.

Set Shared Lock: SELECT... LOCK IN SHARE MODE;

Exclusive lock (X):

Exclusive locks are also called write locks. If a transaction acquires an exclusive lock on a data row, other transactions cannot acquire other locks (exclusive locks or shared locks) on that row. That is, when a transaction reads a data row, other transactions cannot add, delete, or modify the data row.

Set exclusive lock: SELECT... FOR UPDATE

Attention:

For select statements, innodb does not add any locks, that is, multiple concurrent select operations can be performed, and there will be no lock conflicts, because there is no lock at all.

For insert, update, delete operations, innodb will automatically add exclusive locks to the data involved, only query select requires us to manually set exclusive locks.

Intentional Shared Lock (IS):

Inform the database what locks need to be applied next and lock the table. If you need to add a shared lock to record A, innodb will find the table first, add an intentional shared lock to the table, and then add a shared lock to record A. That is to say, before adding a shared lock to a data row, you must first obtain the IS lock of the table

Intentional Exclusion Lock (IX):

Inform the database what locks need to be applied next and lock the table. If an exclusive lock needs to be added to record A, innodb will find the table first, add an intentional exclusive lock to the table, and then add a shared lock to record A. That is to say, a data row must first obtain the IX lock of the table before adding and arranging other locks.

The difference between shared locks and intentional shared locks, exclusive locks and intentional exclusive locks:

Shared lock and exclusive lock. Under certain conditions, the system will automatically add shared lock or exclusive lock. Shared lock or exclusive lock can also be added manually.

Intentional shared lock and intentional exclusive lock are automatically added and released by the system, and the whole process does not need manual intervention.

Shared locks and exclusive locks are row records of locks, while intentional shared locks and intentional exclusive locks lock tables.

Lock implementation:

In MySQL, row-level locking is not a direct lock record, but an index lock. Indexes are divided into primary key indexes and non-primary key indexes. 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 related primary key index.

InnoDB row locking is achieved by locking index entries, and if there is no index, InnoDB locks records with hidden clustered indexes. In other words: if the data is not retrieved through index conditions, InnoDB locks all data in the table, and the actual effect is the same as table locks.

There are three types of row locks:

Record Lock: Lock an index entry, i.e. lock a record.

Gap Lock: locks the 'gap' between index entries, the gap before the first record or the gap after the last record, i.e. locks a range of records, excluding the records themselves

Next-key Lock: Lock a range of records and include the record itself (combination of the above two)

Note: InnoDB default level is repeatable-read level. ANSI/IOS SQL defines four levels of transaction isolation: read uncommitted, read committed, repeatable read, and serializable.

Gap Lock and Next-Key Lock:

Next-Key Lock is a combination of row lock and gap lock, so that when InnoDB scans index records, it will first add row lock to the selected index record and then add gap lock to both sides of the index record. If a gap is locked by transaction T1, other transactions cannot insert records into the gap.

Row lock prevents other transactions from being modified or deleted, Gap lock prevents other transactions from being added, and Next-Key lock formed by row lock and Gap lock jointly solves the problem of phantom reading when RR sector writes data.

When to use table locks in InnoDB:

InnoDB uses row-level locks in most cases, because transaction and row locks are often the reasons we choose InnoDB, but there are also cases where we consider using table-level locks.

When the transaction needs to update most of the data, the table is relatively large, if the default row lock is used, it is not only inefficient, but also easy to cause other transactions to wait for a long time and lock conflicts.

Transactions are complex and can cause deadlocks that lead to rollback.

Under InnoDB, two things should be noted when using table locks.

(1) Although you can add table-level locks to InnoDB using LOCK TALBES, it must be noted that table locks are not managed by the InnoDB storage engine layer, but by the MySQL Server at its upper level. Only when autocommit=0 and innodb_table_lock=1 (default setting) can the InnoDB layer know the table locks added by MySQL, and MySQL Server can sense the row locks added by InnoDB. In this case, InnoDB can automatically identify deadlocks involving table-level locks; otherwise, InnoDB cannot automatically detect and handle such deadlocks.

(2) When locking InnoDB with LOCAK TABLES, pay attention to setting AUTOCOMMIT to 0, otherwise MySQL will not lock the table; do not release the table lock with UNLOCAK TABLES before the end of the transaction, because UNLOCK TABLES will implicitly commit the transaction;COMMIT or ROLLBACK cannot release the table-level lock added with LOCAK TABLES, and the table lock must be released with UNLOCK TABLES. The correct way is as follows:

For example, if you need to write table t1 and read from table t

SET AUTOCOMMIT=0;LOCAK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and here];COMMIT;UNLOCK TABLES; Deadlock:

We said that deadlocks do not occur in MyISAM because MyISAM always acquires all the locks it needs at once, either satisfying them all or waiting for them all. In InnoDB, locks are acquired gradually, creating the possibility of deadlock.

When a deadlock occurs, InnoDB can generally detect it and roll back one transaction to release the lock and another to acquire the lock to complete the transaction. However, in cases involving external locks, or involving locks, InnoDB does not fully automatically detect deadlocks, which needs to be resolved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It should be noted that this parameter is not only used to solve the deadlock problem, in the case of high concurrent access, if a large number of transactions are suspended because they cannot obtain the required locks immediately, it will consume a large amount of computer resources, causing serious performance problems and even dragging down the database. We can avoid this by setting appropriate lock-wait timeout thresholds.

There are many ways to avoid deadlocks, but here are three common ones:

If multiple tables are accessed concurrently by different programs, try to agree to access the tables in the same order to greatly reduce the chance of deadlock. If two sessions access two tables in different order, the chance of deadlock is very high! But if they are accessed in the same order, deadlocks may be avoided.

In the same transaction, try to lock all the resources needed at one time to reduce the probability of deadlock.

For parts of the business where deadlocks are most likely to occur, try using escalation locking granularity to reduce the likelihood of deadlocks through table-level locking.

When a program processes data in batches, it can also greatly reduce the likelihood of deadlocks if the data is sorted in advance to ensure that each thread processes records in a fixed order.

At the REPEATEABLE-READ isolation level, if two threads simultaneously use SELECT... ROR UPDATE plus exclusive lock, in the absence of matching the record, both threads will lock successfully. The program finds that a record does not yet exist and tries to insert a new record. If both threads do this, a deadlock occurs. In this case, changing the isolation level to READ COMMITTED avoids the problem.

When the isolation level is READ COMMITTED, if both threads execute SELECT first... FOR UPDATE: Determine if there is a record that meets the criteria. If not, insert the record. At this point, only one thread can insert successfully, another thread will appear lock waiting, when the first thread commits, the second thread will be due to the primary key error, but although this thread error, but will obtain an exclusive lock! At this time, if there is a third thread to apply for exclusive lock, deadlock will also occur. In this case, you can directly insert the operation, and then catch the primary key double exception, or always execute ROLLBACK to release the acquired exclusive lock when encountering a primary key double error.

ps: If a deadlock occurs, you can use the SHOW INNODB STATUS command to determine the cause of the last deadlock and the improvement measures.

Summary:

For InnoDB tables, the main points are as follows

(1) InnoDB marketing is index-based, and if data is not accessed through an index, InnoDB uses table locks.

(2) InnoDB gap lock mechanism and why InnoDB uses gap lock.

(3) The locking mechanism and consistent read strategy of InnoDB are different at different isolation levels.

(4) MySQL recovery and replication also have a great impact on InnoDB locking mechanism and consistent read strategy.

(5) Lock conflicts and even deadlocks are difficult to avoid completely.

After understanding the locking characteristics of InnoDB, users can reduce lock conflicts and deadlocks by design and SQL tuning, including:

Try to use a lower isolation level

Carefully design the index and use the index to access the data as much as possible to make locking more precise, thus reducing the chance of lock conflicts.

Choose a reasonable transaction size, and the probability of lock conflicts in small transactions is also smaller.

When locking a recordset display, it is best to request a sufficient level of lock at once. For example, if you want to modify data, it is best to apply for exclusive locks directly, rather than applying for shared locks first and then requesting exclusive locks when modifying, which is easy to produce deadlocks.

When different programs access a group of tables, they should try to agree to access the tables in the same order, and for a table, access the rows in the table in a fixed order as much as possible. This greatly reduces the chance of deadlock.

Try to access data with equality conditions, which avoids the impact of gap locks on concurrent inserts.

Do not request more locks than you actually need; do not display locks on queries unless you must.

For certain transactions, table locks can be used to speed up processing or reduce the likelihood of deadlocks.

What is the main role of innoDB lock in mysql above? Do you think it is what you want? If you want to know more about it, you can continue to pay attention to our industry information section.

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.