Copyright belongs to the author, any form of reprint, please contact the author to obtain authorization and indicate the source.

MySQL/InnoDB locking has been a common topic. For example, how do you ensure data integrity in a database with high concurrent requests? How to troubleshoot the deadlock problem? Below are the differences between different lock levels

  • Table level lock: low overhead, fast locking; Deadlocks do not occur; The lock granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.
  • Page locking: the cost and locking time between table locking and row locking; Deadlocks occur; Lock granularity is between table locks and row locks, and concurrency is mediocre.
  • Row-level locking: high overhead and slow locking; Deadlocks occur; The locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

Check the type of storage engine that the database has: SHOW ENGINES

Optimistic locking

It is implemented with the data Version recording mechanism, which is the most common implementation of optimistic locking. What is data version? This is to add a version identifier to the data, usually by adding a numeric “version” field to the database table. When reading data, the value of the version field is read together, and the version value is increased by 1 each time the data is updated. When we submit the update, we judge the current version of the corresponding database table and compare it with the version value fetched for the first time. If the current version number of the database table is the same as the version value fetched for the first time, it will be updated; otherwise, it is regarded as outdated data.

For example:

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

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version}
Copy the code

Pessimistic locking

The opposite of optimistic locking is pessimistic locking. Pessimistic locking is a process in which data is manipulated in the belief that there will be data conflicts, so each operation requires a lock to be acquired in order to operate on the same data. Similar to Java synchronized, pessimistic locking takes more time. In addition to the optimistic lock corresponding, pessimistic lock is implemented by the database itself, to use, we directly call the related statements of the database can be.

This brings us to the two other lock concepts involved in pessimistic locking: shared locking and exclusive locking. Shared locks and exclusive locks are different implementations of pessimistic locks and both fall under the category of pessimistic locks.

A Shared lock

A shared lock, also known as a read lock, is created by a read operation. Other users can read the data concurrently, but no transaction can modify the data (acquire exclusive locks on the data) until all shared locks have been released. A deadlock is likely to occur when a transaction modifies a read lock. See the figure below.

If transaction T attaches A shared lock to data A, other transactions can only attach A shared lock to data A, not exclusive locks. A transaction that acquires a shared lock can only read data, not modify it

Open the first query window

begin; /begin work; /start transaction; (Choose one of the three)#(lock in share mode)
SELECT * from TABLE where id = 1  lock in share mode;
Copy the code

Update TABLE set name=”www.souyunku.com” where id =1; If you execute commit in the first window before the timeout, the update statement succeeds.

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

After the shared lock is added, an error message is displayed

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
Copy the code

If you add LOCK IN SHARE MODE to the end of the query statement, Mysql will apply a shared LOCK to each row IN the query result set. If no other thread uses an exclusive LOCK to any row IN the query result set, Mysql will successfully apply a shared LOCK. Otherwise, Mysql will block. Other threads can also read a table that uses a shared lock, and these threads are reading the same version of the data.

After a shared lock is added, exclusive locks are automatically added to UPDATE, INSERT, and DELETE statements.

Exclusive lock

An exclusive lock (also called a writer lock) is also called a write lock. If a transaction has an exclusive lock on a row, only this transaction can read or write it. Until the end of the transaction, no other transaction can lock it. Other processes can read it, but cannot write it until it is released. Exclusive locking is an implementation of pessimistic locking, which is also described above.

If transaction 1 places an X lock on A, transaction 1 can read A and modify A, and no other transaction can place A lock on 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 do we need to add a read lock? Prevents the data being read from being written by other threads. Select status from TABLE where id=1 for update; select status from TABLE where id=1 for update;

An exclusive lock, also known as a write lock or an exclusive lock, blocks other write locks and read locks until the current write operation is complete.

Exclusive locks – example:

To use exclusive locks, we must turn off the autocommit property of the mysql database, because mysql uses autocommit mode by default. This means that mysql commits an update immediately after you perform it.

To set MySQL to non-autocommit mode, run the following command:

set autocommit=0;
After autoCOMMIT is set up, we can go about our normal business. The details are as follows:
# 1. Start the transactionbegin; /begin work; /start transaction; (Choose one of the three)# 2. Query table information (lock for update)
select status from TABLE where id=1 for update;
# 3. Insert a data pieceInsert into TABLE (id,value) values (2,2);# 4. Change the data to
update TABLE set value=2 where id=1;
# 5. Commit the transactioncommit; /commit workCopy the code

Row locks

Summary: When multiple transactions operate on the same row, subsequent transactions are in a blocked wait state. This avoids data consistency issues such as dirty reads. Later transactions can operate on other rows, solving the problem of high table locking and low concurrency performance.

# Transaction-A
mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;

# Transaction-B
mysql> update innodb_lock set v='2001' where id=2;
Query OK, 1 row affected (0.37 sec)
mysql> update innodb_lock set v='1002' where id=1;
Query OK, 1 row affected (37.51 sec)
Copy the code

Reality: Row locks are upgraded to table locks when batch modify data scripts are executed. Other operations on the order are waiting…… cause: nnoDB uses a row-level lock only when retrieving data through an index condition, otherwise uses a table lock! The simulated operation is to use ID as the search condition, and ID is the only index automatically created by MySQL, so it ignores the case of row lock variable table lock

Bottom line: InnoDB’s row locks are for indexes, not records. The index must not be invalidated, otherwise it will be upgraded from a row lock to a table lock.

  • Disadvantages of row locking: high overhead; Lock is slow; Deadlocks occur
  • Advantages of row locking: small lock granularity, low probability of lock conflict; Strong ability to handle concurrency
  • Locking mode: automatic locking. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds exclusive locks to the data set involved. For normal SELECT statements, InnoDB does not impose any locks; Of course we can also display the lock:

From the above example, row lock and table lock seem to be a pit, but MySQL is not so boring to dig a pit for you. This is because MySQL has its own execution plan. When you need to update most or even the entire table of a larger table. And you stupidly use the index as the search criteria. Accidentally opened the line lock (no trouble ah! Ensure data consistency!) . However, MySQL believes that using a large number of row locks on a single table can lead to inefficient transaction execution, which can lead to long lock waits and more lock conflicts for other transactions, resulting in serious performance degradation. So MySQL will upgrade the row lock to a table lock, that is, no indexes are actually used. If you think about it, since most of the data in the entire table has to be updated, locking row by row is less efficient. If you look at the MySQL execution plan using the explain command, you will see that the key is null. MySQL does not actually use indexes, and the escalation of row locks to table locks is consistent with the above conclusion.

Note: Row-level locks are based on indexes. If an SQL statement does not use indexes, row-level locks will not be used. Table level locks will be used.

Clearance lock

When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the entry of an existing data record that meets that condition. InnoDB will also lock the GAP if the Key is within the range of the condition, and this mechanism is called next-key lock. For example, if there are only 101 records in the emp table, the empid values are 1,2,… SQL > select * from ‘100,101’ where ‘100,101’

Select * from  emp where empid > 100 for update;
Copy the code

InnoDB locks records with empID of 101, but also “gaps” where empID is greater than 101 (these records don’t exist).

InnoDB uses a gap lock to prevent magic reads and to meet the isolation level requirements. In the example above, if InnoDB does not use a gap lock, if another transaction inserts any record with empID greater than 100, then the transaction executes the above statement again, and then a magic read will occur. On the other hand, to meet its recovery and replication needs. The effects of recovery and replication on the locking mechanism, as well as InnoDB’s use of gap locks at different isolation levels, will be discussed in a later section.

Obviously, InnoDB’s locking mechanism blocks concurrent inserts of key values within the range of the condition when retrieving and locking records using range conditions, which often results in severe lock waits. Therefore, in the actual application development, especially in the application with more concurrent inserts, we should try to optimize the business logic, try to use equal conditions to access the updated data, and avoid using scope conditions.

In particular, InnoDB uses a gap lock when locking through a range condition. InnoDB also uses a gap lock when requesting a non-existent record with an equal condition!

Example: if there are only 101 records in the emp table, the empid values are 1,2,…… , 100101. InnoDB storage engine gap lock blocking example

session_1 session_2
mysql> select @@tx_isolation; mysql> select @@tx_isolation;
+—————–+ +—————–+
@@tx_isolation @@tx_isolation
+—————–+ +—————–+
REPEATABLE-READ REPEATABLE-READ
+—————–+ +—————–+
1 row in set (0.00 sec) 1 row in set (0.00 sec)
mysql> set autocommit = 0; mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Current session locks for update records that do not exist:
mysql> select * from emp where empid = 102 for update;
The Empty set (0.00 SEC)
If another session inserts a record with empID 201 (note: this record does not exist), then the lock wait will occur:
mysql>insert into emp(empid,…) values(201,…) ;
Block waiting for
Session_1 Execute rollback:
mysql> rollback;
Query OK, 0 rows affected (13.04 sec)
Because other session_1 rollback releases the next-key lock, the current session can acquire the lock and insert the record successfully:
mysql>insert into emp(empid,…) values(201,…) ;
Query OK, 1 row affected (13.35 sec)

Harm (pit) : If the execution condition is too large, InnoDB will lock all the index keys in the whole range, which can easily affect the performance.

Table locks

How do I lock a table? Innodb locks rows only when there is an index. Tables without indexes are locked.

Innodb row lock and table lock

As mentioned earlier, Innodb supports both row and table locking, so when is the entire table locked and when is only a row locked? InnoDB will only use row-level locking if the data is retrieved by index criteria, otherwise InnoDB will use table locking!

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

Row-level locks are based on indexes. If an SQL statement does not use indexes, row-level locks are not used. Table level locks are used. The disadvantage of row-level locking is that it is slow and memory intensive because it requires a large number of lock resources.

A deadlock

Deadlock: A situation in which two or more processes are competing for resources while they are executing, and neither of them can advance without an external force. At this point, the system is said to be in a deadlock state or the system has produced a deadlock, and these processes that are always waiting for each other are called deadlock processes. Because resource usage is mutually exclusive, when a process requests resources, the process can never be allocated the necessary resources without external assistance and can not continue to run, which produces a special phenomenon of deadlock.

There are two ways to release a deadlocked state:

  1. None Example Query whether the table is lockedshow OPEN TABLES where In_use > 0;
  2. Query the process (if you have SUPER permission, you can see all threads. Otherwise, you can only see your own thread)

    show processlist
  3. Kill the process ID (the ID column of the above command)

    kill id

The second:

  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 the current isolocked transaction

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

    Kill the process

    Kill the process ID

If the system resources are sufficient and the process’s resource requests can be met, the possibility of a deadlock is low, or there will be a deadlock due to competition for limited resources. Secondly, the process is running in different order and speed, which may also produce deadlock. The four conditions necessary to create a deadlock:

  1. Mutually exclusive: 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 a resource, it holds on to a resource it has acquired.
  3. Non-deprivation condition: A process cannot forcibly take away resources it has obtained until it has used them up.
  4. Cyclic wait condition: Several processes form an end-to-end cyclic wait resource relationship.

Although deadlocks cannot be completely avoided, they can be minimized. Minimizing deadlocks increases transaction throughput and reduces overhead because there is very little transaction rollback, which cancels all the work performed by the transaction. Operations that are rolled back due to a deadlock are resubmitted by the application.

The following methods can help minimize deadlocks:

  1. Objects are accessed 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 connections.

MyISAM storage engine

There are two major differences between InnoDB and MyISAM:

  1. InnoDB supports transactions; MyISAM does not support transactions
  2. Innodb uses row locking by default and MyISAM uses table locking by default. Locking ensures transaction consistency. Where there are people, there are transactions.
  3. MyISAM is not suitable for high concurrency

Shared read locks

A read operation on a MyISAM table (with a read lock) does not block a read operation on the same table by another process, but it blocks a write operation on the same table. Write operations can be performed by other processes only after the read lock is released. No other tables can be read until the lock is released.

Exclusive write locks

A write to a MyISAM table (with a write lock) blocks other processes from reading or writing to the same table. Only when the write lock is released will the read and write operations be performed. No other tables can be written until the lock is released.

  1. Table locks. Read locks block writes, not reads. Write locks block both reads and writes.
  2. Table lock lock/unlock mode: MyISAM automatically locks all tables before a SELECT statement and locks all tables before an UPDATE (UPDATE, DELETE, INSERT, etc.). This process does not require user intervention. Users do not need to explicitly LOCK MyISAM tables directly using the LOCK TABLE command.

If the user wants to display the lock, use the following command:

Lock the table:

The LOCK TABLES tbl_name {READ | WRITE}, [tbl_name {READ | WRITE},...Copy the code

Unlock the table:

UNLOCK TABLES 
Copy the code

When a table is explicitly locked using LOCK TABLES, all the locks involved must be obtained at the same time. After LOCK TABLES is executed, only the TABLES that are explicitly locked can be accessed.

If a read lock is applied, only queries can be performed, but updates cannot be performed.

This is also true in the case of automatic locking, where MyISAM always obtains all the locks needed for an SQL statement at once. This is why there is no Deadlock Free in MyISAM tables.

Add read lock to test_table;

LOCK TABLES test_table READ 
UNLOCK test_table
Copy the code

Add a write lock to test_table

LOCK TABLES test_table WRITE
UNLOCK test_table
Copy the code

When you use LOCK TABLES, not only do you need to LOCK all the TABLES you use at once, but you also need to LOCK the same table through the same alias as in the SQL statement for as many times as it appears in the SQL statement, or else you will fail!

For example, the following SQL statement:

select a.first_name,b.first_name, from actor a,actor b where a.first_name = b.first_name;
Copy the code

The Sql statement, the actor table appeared twice, in the form of an alias, respectively is a, b, then if you want to lock before the Sql execution will use the following Sql:

lock table actor as a read,actor as b read;
Copy the code

Concurrent insert

I mentioned above that MyISAM tables are read and write sequentially, but this is in general terms. Under certain conditions,MyISAM tables also support concurrent query and insert operations. The MyISAM storage engine has a system variable, conCURRENT_INSERT, that controls the behavior of its concurrent inserts and can have a value of 0, 1, or 2, respectively.

  • When conCURRENT_INSERT is set to 0, concurrent inserts are not allowed.
  • When conCURRENT_INSERT is set to 1,MyISAM allows one process to read the table while another process inserts records from the end of the table if there are no holes in the table (that is, rows in the middle of the table that have not been deleted). This is also the default setting for MySQL.
  • When conCURRENT_INSERT is set to 2, it allows concurrent inserts at the end of a MyISAM table, regardless of whether there are holes in the table.

The concurrent insert feature of the MyISAM storage engine can be used to resolve lock contention for queries and inserts on the same table in applications.

MyISAM lock scheduling

As mentioned earlier, the MyISAM storage engine’s read and write locks are mutually exclusive and read and write operations are sequential. If a process requests a read lock on a MyISAM table and another process requests a write lock on the same table, how can MySQL handle this?

The answer is that the write process gets the lock first.

Not only that, but even if the read request comes first and the write request comes later, the write lock is inserted before the read lock request! This is because MySQL generally considers write requests to be more important than read requests. This is why MyISAM tables are not well suited for applications with a large number of update and query operations, because the large number of update operations makes it difficult for the query operations to acquire read locks and can block forever. This can go horribly wrong sometimes!

Fortunately, there are a few Settings to adjust MyISAM’s scheduling behavior.

Make the MyISAM engine give read requests priority by default by specifying the start parameter low-priority-updates.

  • By executing commandsSET LOWPRIORITYUPDATES=1,This lowers the priority of update requests from this connection.
  • Lower the priority of an INSERT, UPDATE, or DELETE statement by specifying the LOW_PRIORITY attribute.
  • In addition,MySQL provides a compromise to adjust read/write collisions by setting the max_write_lock_count parameter to an appropriate value. When a table’s read lock reaches this value,MySQL temporarily lowers the priority of the write request, giving the read process a chance to acquire the lock.

conclusion

  • The locking granularity in the database can be divided into row level lock, page level lock and table level lock.
  • MySQL’s MyISAM engine supports table-level locking.
  • Table – level locks are classified into two types: shared read locks and mutex write locks. Both types of locks are blocking locks.
  • Read locks can be added to read locks, but write locks cannot be added to read locks. You cannot add a write lock to a write lock.
  • By default, MySql uses a read lock before a query and a write lock before an update.
  • If you want to display locked/unlocked flowers you can use LOCK TABLES and UNLOCK.
  • After you use LOCK TABLES, you cannot operate on an unlocked table until you unlock it.
  • If a read lock is added, only read operations can be performed, but no write operations can be performed until the lock is unlocked.
  • If the table that you want to operate on is aliased multiple times, you must specify the alias of the table that you want to lock.
  • The MyISAM storage engine has a system variable, conCURRENT_INSERT, that controls the behavior of its concurrent inserts and can have a value of 0, 1, or 2, respectively.
  • Since read locks and write locks are mutually exclusive, MySql uses write locks first by default during scheduling. This can be set with low-priority-updates.

The practice to solve

Analysis row locking

Analyze row lock contention on the system by examining the InnoDB_row_lock state variable

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
Copy the code
  • Innodb_row_lock_current_waits: The number of locks currently waiting
  • Innodb_row_lock_time: The total amount of time the system has been locked since boot; Very important parameter,
  • Innodb_row_lock_time_avg: Average time per wait; Very important parameter,
  • Innodb_row_lock_time_max: The time it takes to wait for the most common time since the system was booted;
  • Innodb_row_lock_waits: Waits from the system since it started; Very important parameter. Directly determine the direction and strategy of optimization.

Row locking optimization

  1. As much as possible, all data retrieval is done through the index to avoid unindexed rows or invalid indexes causing row locks to be upgraded to table locks.
  2. Avoid performance degradation caused by gap locking as much as possible and reduce or use reasonable retrieval range.
  3. Reduce the granularity of transactions as much as possible, such as controlling transaction size, while providing performance from reducing the number of locked resources and the length of time, thereby reducing lock contention.
  4. The lowest possible level of transaction isolation. The higher the isolation level, the lower the concurrent processing capacity.

Table locks optimization

How open tables; 1 indicates a lock, and 0 indicates no lock.

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+
Copy the code

Analysis table locking

To analyze table locking on the system, check the status variables of table_LOCKs_waits and table_LOCKS_IMMEDIATE: show status like ‘table_LOCKS %’

mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
+----------------------------+-------+
Copy the code
  • Table_locks_immediate: indicates the number of table locks to be released immediately.
  • Table_locks_waited: indicates the number of table locks to wait. The higher the value, the more serious table-level lock contention there is.

In addition, MyISAM’s read-write lock scheduling is write-first, which makes MyISAM unsuitable as a storage engine for write-primary tables. Because after a write lock, no other thread can do anything, the large number of updates makes it difficult for queries to get the lock, thus permanently blocking.

In what scenario is table locking used

First case: full table update. The transaction requires updating most or all of the data, and the table is large. Using row locks can result in inefficient transaction execution, which can lead to long lock waits and more lock conflicts for other transactions.

Second case: multi-table query. The transaction involves more than one table, and the complicated associated query is likely to cause deadlock and cause a large number of transactions to roll back. In this case, if you can lock the tables involved in the transaction once, you can avoid deadlocks and reduce the overhead of the database caused by the transaction rollback.

  1. InnoDB supports both table locking and row locking. Row locking is used when indexes are used to modify data, and table locking is used otherwise.
  2. InnoDB automatically locks modify operations, but not query operations
  3. A row lock can be upgraded to a table lock because an index is not being used, so in addition to checking whether an index is being created, you need to check whether the index is actually being used through the Explain execution plan.
  4. The advantage of row locking over table locking is that it works better in high concurrency scenarios because of the smaller granularity of locks.
  5. Table locking is recommended over row locking when most of the table data needs to be modified or multiple tables are queried in complex associated queries.
  6. To ensure consistent data integrity, any database has a locking mechanism. The advantages and disadvantages of locking mechanism directly affect the concurrent processing ability and performance of a database.

Mysql 5.6 select * from update where index field does not exist, will the transaction cause table lock? Only primary keys and unique indexes are row locks. Normal indexes are table locks.

It is found that the common index does not necessarily cite the lock, in the ordinary index, whether to cite the lock depends on the efficiency of the ordinary index.

The “efficient” mentioned above is relative to primary keys and unique indexes. Perhaps “efficient” is not a good explanation, as long as it is understood that in general, “ordinary indexes” are less efficient than the other two. Attribute value repetition rate is high

Attribute value repetition rate

When the “value repetition rate” is low, even close to the effect of primary keys or unique indexes, the “normal index” is still row locked; When the “value repetition rate” is high, MySQL will not treat the “normal index” as an index, which will result in a SQL without an index, and this will lead to a lock.

Just as the JVM automatically optimizes Java code, MySQL also has the capability to automatically optimize SQL. Inefficient indexes are ignored, forcing developers to use correct and efficient indexes.

Attribute value repetition rate is high

To highlight the effect, I built the “normal index” under an attribute with a high “value repetition rate.” In a relatively extreme way, amplify the impact on the outcome.

I will create a “score Scale” with attributes like “ID”, “Score”, and “Level” to simulate a semi-automatic business — the “score” has been imported automatically, and the “level” needs to be updated manually.

The operation steps are as follows:

  1. Disable automatic commit for MySQL transactions
  2. Create table with id increment and create index for score
  3. Insert score value, level null
  4. Example Start two transactions session_1 and Session_2, specify different values of score for the two transactions, and lock data
  5. Session_1 and Session_2 update the level of their transaction locked content.
  6. Observe the database response to two transactions

Cancel automatic transaction commit:

mysql> setautocommit = off; Query OK, 0 rows affected (0.02sec) mysql> show variables like"autocommit"; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | autocommit | OFF |  +--------------------------+-------+ 1 rowsin set (0.01 sec)
Copy the code

Create table, create index, insert data

DROP TABLE IF EXISTS `test1 `; CREATE TABLE `test1` (
`ID`  int(5) NOT NULL AUTO_INCREMENT ,
`SCORE`  int(3) NOT NULL ,
`LEVEL`  int(2) NULL DEFAULT NULL ,
PRIMARY KEY (`ID`)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `test2` ADD INDEX index_name ( `SCORE` );

INSERT INTO `test1`(`SCORE`) VALUE (100); ... INSERT INTO `test1`(`SCORE`) VALUE (0);
Copy the code

The “value repetition rate” of the “SCORE” attribute is extremely high, reaching 50%.

mysql> select * from test1; +----+-------+-------+ | ID | SCORE | LEVEL | +----+-------+-------+ | 1 | 100 | NULL | | 2 | 0 | NULL | | 5 | 100 | NULL | | 6 | 100 | NULL | | 7 | 100 | NULL | | 8 | 100 | NULL | | 9 | 100 | NULL | | 10 | 100 | NULL | | 11 | 100 | NULL  | | 12 | 100 | NULL | | 13 | 100 | NULL | | 14 | 0 | NULL | | 15 | 0 | NULL | | 16 | 0 | NULL | | 17 | 0 | NULL | | 18 | 0 | NULL | | 19 | 0 | NULL | | 20 | 0 | NULL | | 21 | 0 | NULL | | 22 | 0 | NULL | | 23 | 0 | NULL | | 24 | 100 | NULL  | | 25 | 0 | NULL | | 26 | 100 | NULL | | 27 | 0 | NULL | +----+-------+-------+ 25 rowsin set
Copy the code

Open two transactions (one for each window) and select data:

-- SESSION_1, select SCORE = 100 mysql> BEGIN; SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE; Query OK, 0 rows affected +----+-------+-------+ | ID | SCORE | LEVEL | +----+-------+-------+ | 1 | 100 | NULL | | 5 | 100 | NULL  | | 6 | 100 | NULL | | 7 | 100 | NULL | | 8 | 100 | NULL | | 9 | 100 | NULL | | 10 | 100 | NULL | | 11 | 100 | NULL | |  12 | 100 | NULL | | 13 | 100 | NULL | | 24 | 100 | NULL | | 26 | 100 | NULL | +----+-------+-------+ 12 rowsin set
Copy the code

Open another window:

-- SESSION_2, select SCORE = 0 mysql> BEGIN; SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
| 14 |     0 | NULL  |
| 15 |     0 | NULL  |
| 16 |     0 | NULL  |
| 17 |     0 | NULL  |
| 18 |     0 | NULL  |
| 19 |     0 | NULL  |
| 20 |     0 | NULL  |
| 21 |     0 | NULL  |
| 22 |     0 | NULL  |
| 23 |     0 | NULL  |
| 25 |     0 | NULL  |
| 27 |     0 | NULL  |
+----+-------+-------+
13 rows in set
Copy the code

Failed to update LEVEL in session_1 window:

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
1205 - Lock wait timeout exceeded; try restarting transaction
Copy the code

Session_1 (SCORE = 100), session_2 (SCORE = 0), session_1 (SCORE = 0), session_1 (SCORE = 0) It simply means that a table lock has been raised at this point. Don’t worry, just went to one extreme – index attribute values are unusually repetitive – and then went to the other extreme.

Attribute value repetition rate is low

Select * from ‘SCORE’ where ‘value duplicate’ = 0;

mysql> delete from test1 where id > 2;
Query OK, 23 rows affected

mysql> select * from test1;
+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  2 |     0 | NULL  |
+----+-------+-------+
2 rows in set
Copy the code

Close the two transaction operation Windows, restart session_1 and Session_2, and select the required data:

-- SESSION_1, select SCORE = 100 mysql> BEGIN; SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
+----+-------+-------+
1 row in set-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- new window -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- SESSION_2, selected SCORE = 0 data mysql > BEGIN; SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
+----+-------+-------+
1 row in set
Copy the code

Session_1 data was updated successfully:

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings:0
Copy the code

Same table structure, same operation, two different results are surprising. The first result makes the “normal index” look like a lock, and the second result overturns the former. The only difference between the two operations is the “value repetition rate” of the index property. According to the single-variable proof method, we can conclude that when the “value repetition rate” is low, even close to the effect of primary key or unique index, “ordinary index” is still row lock; When the “value repetition rate” is high, MySQL will not treat the “normal index” as an index, which will result in a SQL without an index, and this will lead to a lock.

Here’s an example:

  1. User A has 100 YUAN in the bank card. At some point, user B transfers 50 yuan to USER A (called OPERATION B), and user C transfers 50 yuan to User A (called operation C).
  2. Operation B reads his current balance of 100 from the database and calculates the new balance of 100+50=150
  3. The C operation also reads his current balance of 100 from the database and calculates the new balance of 100+50=150
  4. The B operation writes balance=150 to the database, and then the C operation also writes balance=150 to the database
  5. And eventually A’s balance is going to be 150

The example above, A receive two 50 yuan transfer at the same time, the final balance should be 200 yuan, but because the problem of concurrency into 150 yuan, the reason is that B and C to initiate A transfer request, opened the two database session at the same time, the two transactions, A transaction after got the intermediate state before A transaction data, leading to lost updates. There are two common solutions:

  • The lock is synchronized
  • Check data consistency before update

Note that both pessimistic and optimistic locks are business logic level definitions and may have different implementations for different designs. A common pessimistic lock implementation in the mysql layer is to add an exclusive lock.

However, this is not the case. In fact, the data with an exclusive lock can no longer be locked by other transactions until the lock is released (the end of the transaction). The reason that an exclusive lock prevents update, DELETE, and other operations is because update, DELETE, and other operations are automatically locked. This means that exclusive locks cannot prevent select operations. The select XX for update syntax allows exclusive locks to be placed on select operations. So in order to prevent update loss you can put a lock on select for update which prevents other transactions from selecting for update (but note that you can’t prevent select)

Optimistic lock example:

begin;
select balance from account whereid=1; - to get the balance = 100; Then calculate balance=100+50=150 update accountset balance = 150 where id=1 and balance = 100;
commit;
Copy the code

As shown above, if the affected of UPDATE is 0 during SQL execution, the balance is not equal to 100, that is, the data has been changed by other transactions. In this case, the business can return failure or re-select the calculation

When rolling back, why do only some update statements fail, and not all updates in the transaction fail?

This is because innoDB is automatically committed by default: note that there is another condition that can cause partial statements to be rolled back. There is an argument in InnoDB called innodb_rollback_on_timeout

show VARIABLES LIKE 'innodb_rollback_on_timeout'
+----------------------------+---------+
| Variable_name              | Value   |
|----------------------------+---------|
| innodb_rollback_on_timeout | OFF     |
+----------------------------+---------+
Copy the code

The official handbook describes it this way: In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If – Innodb_rollback_on_timeout is specified, A transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.

If this parameter is turned off or does not exist, only the last Query of the transaction is rolled back. If this parameter is turned on, the entire transaction is rolled back when a timeout occurs.

Note:

  • MySQL insert, update, replace into deadlock rollback does not record the DML statement to binlog by default. If you do not process the JDBC return code for big data computing tasks such as Mapreduce and Hive, success will be displayed, causing the insert and update partially successful and partially failed. However, you can see the deadlock rollback log of the database from SHOW ENGINE INNODB STATUS\G. In this case, it is recommended to add a retry mechanism or throw exception/ Error based on the JDBC error code or SQLException.
  • In a transactional system, deadlocks do exist and cannot be completely avoided. InnoDB automatically detects a transaction deadlock, immediately rolls back one of the transactions, and returns an error. It selects the simplest (least costly) transaction to roll back according to some mechanism. Occasional deadlocks are nothing to worry about, but frequent deadlocks are something to be concerned about. InnoDB storage engine has a background lock monitoring thread that looks at possible deadlock problems and automatically informs the user.

How to reduce innoDB deadlock probability?

Deadlocks are difficult to eliminate completely in row locking and transaction scenarios, but lock conflicts and deadlocks can be reduced through table design and SQL tuning, including:

  • Try to keep the isolation level as low as possible. For example, if gap locking occurs, you can change the session or transaction isolation level to RC(Read COMMITTED), but set binlog_format to ROW or mixed format
  • Carefully design indexes and try to use them to access data to make locking more accurate and thus reduce the chance of lock conflicts;
  • Select a reasonable transaction size, small transactions are less likely to have lock conflicts;
  • 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 better to apply for an exclusive lock directly, rather than applying for a shared lock first and then requesting an exclusive lock during the modification, which is prone to deadlock.
  • When different programs access a set of tables, they should try to agree to access the tables in the same order. For a table, they should try to access the rows in the table in a fixed order. This greatly reduces the chance of deadlocks;

Example:

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
至
DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;
Copy the code

Try to use equal conditions to access data, so as to avoid the impact of gap lock on concurrent insertion; Do not apply for more locks than you actually need; Do not display locks during queries unless necessary. For certain transactions, table locks can be used to speed up processing or reduce the possibility of deadlocks.

Weird Lock wait timeout

The default lock timeout is 50s
show variables like 'innodb_lock_wait_timeout';
+--------------------------+---------+
| Variable_name            |   Value |
|--------------------------+---------|
| innodb_lock_wait_timeout |      50 |
+--------------------------+---------+
Copy the code

SHOW ENGINE INNODB STATUS\G without any deadlock information, then look at the mysql-server log to see what the data is doing at that time. MySQL Log file system MySQL log file system

  1. Error log: Records problems when starting, running, or stopping mysqld. This log is enabled by default.
  2. General log: Indicates the general query log, which records all statements and instructions. Enabling the database will cause a performance loss of about 5%.
  3. Binlog: Binary format, which records all the changed data. It is mainly used for slave replication and data recovery.
  4. Slow logs: Record all queries that take longer than long_query_time seconds or queries that do not use indexes. Slow logs are disabled by default.
  5. Innodb logs: Innodb redo log and undo log, which are used to restore data and undo operations.

From the above introduction, we can see that the log of this problem may be in 2 and 4, look at 4, that can only be enabled 2, but 2 has a certain loss to the performance of the database, because it is a full log, the amount is very large, so we must be careful to enable:

The general_log function is disabled by default. If the general_log function is enabled, the database performance will be affected by about 5%'general%'; +------------------+---------------------------------+ | Variable_name | Value | |------------------+---------------------------------| | general_log | OFF | | general_log_file | / opt/data/mysql/TJTX - 103-26. The log | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - global level of the session open:setGlobal general_log=1set general_log=1

-- setThe dynamic parameters set by the directive are invalid after MySQL restarts. If you want to make them permanent, you need to configure static variables/parameters in /etc/my.cnf. -- if you don't know my. CNF position, can according to the mysql -? | grep".cnf"Query order of preference, my.cnf,$MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
Copy the code

The dynamic parameters set by the set directive are invalid after MySQL restarts. If you want them to take effect permanently, you must configure static variables/parameters in /etc/my.cnf.

More content please refer to strongly recommended – He Deng Cheng’s technology blog he Deng cheng senior technical expert Alibaba database core team leader, the article is very in-depth

Mysql InnoDB lock insert, update, delete lock mysql InnoDB lock

conclusion

There are two ways to release a deadlocked state:

  1. None Example Query whether the table is lockedshow OPEN TABLES where In_use > 0;
  2. Query the process (if you have SUPER permission, you can see all threads. Otherwise, you can only see your own thread)

    show processlist
  3. Kill the process ID (the ID column of the above command)

    kill id

The second:

  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 the current isolocked transaction

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

    Kill the process

    Kill the process ID
# Analyze row lock contention on the system by checking the InnoDB_row_lock state variable
show status like 'innodb_row_lock%'; ` ` `# Check the locking status
show open tables where in_use > 0;

For specific instructions, please see the content above
show status like 'table_locks%';
show VARIABLES LIKE 'innodb_rollback_on_timeout';
show variables like 'general%';
Copy the code