InnoDB Chinese Reference Manual---8 InnoDB transaction Mode and lock

Reference Manual | Chinese InnoDB chinese reference Manual---Dog dog (heart sail) Translation 8 InnoDB transaction Mode and lock
In InnoDB transaction mode, the goal has been to combine the best properties of a multiversioning database to traditional two-phase Locking. InnoDB for row-level locking and reads data in a manner similar to that of an Oracle-unlocked read (non-locking). Locked tables in InnoDB are SO (space-efficiently) and no longer need to be locked out: A typical feature is that some users may lock any row in the database or a subset of any rows without causing the InnoDB memory run to overflow.

In InnoDB, all user actions are handled in a transactional manner. If MySQL uses the Autocommit (autocommit) method, each SQL statement will be handled in a separate transaction. MySQL usually establishes a service connection in autocommit mode.

If you turn off autocommit mode by using SET autocommit = 0, the user is assumed to always operate in a transactional manner. If you emit a COMMIT or ROLLBACK SQL statement, it will stop the current transaction and start the new transaction again. Two statements will release all InnoDB locks set in the current transaction. COMMIT means permanently changing the changes in the current transaction and being visible to other users. ROLLBACK on the contrary, it is all the changes that cancel the current transaction.

If a connection is established with autocommit = 1, the user can still perform a multiple-statement transaction by beginning with begin and ending with COMMIT or ROLLBACK.

In the terms specified in the SQL-1992 transaction isolation level (transaction isolation levels), the InnoDB default is repeatable READ. Starting with 4.0.5, InnoDB provides all 4 different transaction isolation levels in the SQL-1992 standard. You can set the default transaction isolation level for all connections in the [mysqld] area of MY.CNF:
transaction-isolation = {read-uncommitted | read-committed | Repeatable-read | SERIALIZABLE}

　

Users can also change the isolation level for a single connection or for all new connections through the following SQL statement:
SET [Session | GLOBAL] TRANSACTION Isolation Level {READ UNCOMMITTED | READ Committed | Repeatable READ | SERIALIZABLE}
Note that there are no hyphens in the syntax of this SQL statement. If you specify the keyword GLOBAL in detail in the above statement, it will determine the initial isolation level of the new connection, but will not change the isolation level of the existing connection. Any user can change the isolation level of its own session, even during a transaction process. SET TRANSACTION has no effect on InnoDB tables in previous versions of 3.23.50. Only repeatable READ and serializable are available in previous versions of 4.0.5.
　

The transaction isolation level of the global and current sessions can be queried through the following statements:
SELECT @ @global. tx_isolation; SELECT @ @tx_isolation;

　

Use the so-called Next-key locking in the InnoDB row lock. This means that, in addition to the index record, InnoDB can lock the front "gap" (' gap ') of the index record to block the other user's direct insertion at the front of the index record. Next-key lock means locking an index record and the gap (GAP) before that record. Gap Lock is the gap between locking only certain index records.

Isolation level in InnoDB: READ uncommitted This is often referred to as ' dirty read ': The execution of Non-locking selects does not allow us to see an earlier version of a record; therefore, in this isolation the right and wrong Consistent ' reads; In addition, this level of isolation operates as READ committed. READ committed has some kind of isolation level similar to Oracle. All SELECT ... For UPDATE and SELECT ... The lock in SHARE MODE statement locks only the index record, not the gap before it, allowing free insertion of new records after locked records. Updates and deletes with a unique index (unique index) with a unique search condition lock only the found index record, not the gap before the index. However, in the scope of UPDATE and DELETE, InnoDB must set the Next-key or gap locks to block other users from inserting gaps in the range. Since the duplication (replication) and recovery (recovery) work for MySQL has to be blocked, this is necessary. Consistent reads works in a somewhat similar way to Oracle: each consistent read, or even the same transaction, sets and acts on its own latest snapshot. Repeatable READ This is the default transaction isolation level for InnoDB. SELECT ... For UPDATE, SELECT ... Lock in SHARE MODE, UPDATE, and DELETE, which search for unique indexes with unique criteria, lock only the found index records without locking the gap before the index. Otherwise, these operations will use the Next-key lock to lock the found index range with Next-key and Gap locks, and block new inserts from other users. In consistent reads, this is an important difference compared to the previous isolation level: At this level, all consistent reads in the same transaction read the snapshot that was determined the first time it was read. This convention means that if you emit several unformatted (plain) selects in the same transaction, these selects are consistent. SERIALIZABLE This level is similar to the previous level, but is unformatted (PLAIn) is implicitly converted to SELECT selects ... LOCK in SHARE MODE.
　
8.1 Consistent Read
Consistent read is InnoDB uses its multiple versions (multiversioning) to provide queries to a database at a point-in-time snapshot. The query will check for changes made by the transactions that were submitted prior to this point in time, as well as transactions that were changed or uncommitted after the point of time. The exception to this rule is that the query checks for changes made by the transaction itself.

If the default repeatable read isolation level, then all consistent reads in the same transaction read only the same snapshot that was determined for the first read in the transaction. You can get a new snapshot of the data by submitting the current transaction and issuing a new query.

Consistent read the default mode in InnoDB processing SELECT is the Read committed and the REPEATABLE read isolation level. Consistent read does not lock any of the tables it accesses, so any other user can modify the tables freely before consistent read is completed.
8.2 Locking Reads
Consistent read is inconvenient in some cases. Suppose you want to insert a new row in the table child, which already has a parent table.

Suppose you use consistent read to read the table PARENT and view the corresponding record in the child table. Can you really safely add a child row to the table children? Not possible, because other users may have deleted the parent row of table parent in the meantime, and you don't know it.

The solution is to run a SELECT under lock in SHARE mode.

SELECT * from PARENT WHERE NAME = ' Jones ' LOCK in SHARE MODE;

Reading in shared mode means reading the most current existing data and setting a shared-mode lock on the rows being read. If the latest data belongs to a transaction that other users have not yet committed, it will have to wait until the transaction is committed. Shared mode can prevent other users from updating or deleting rows that we currently read. When the query gets ' Jones ', you can safely add a child row to the child table, and then commit the transaction. This example shows how to implement referential integrity in application code.

Another example: in the table child_codes there is an integer-digit segment that is used to assign a unique identifier to each child row that is added to the table children. Obviously, it is not a good idea to read the values in the parent table with a consistent read, as two users may read out the same count value, and inserting two words with the same identifier will result in a duplicate key value (duplicate key) error. If two users read the counters at the same time, one of them will end up in the deadlock when trying to update the counter, so using LOCK in SHARE MODE is not a good solution when reading.

In this and the case there are two ways to read and increase the counter: (1) First update the counter and then read it; (2) First lock with a for update and then read before adding it:

SELECT Counter_field from Child_codes for UPDATE; UPDATE child_codes SET Counter_field = Counter_field + 1;

SELECT ... For update reads the most current existing data and sets its lock on the rows that are read. This lock is also set on the rows accessed by SQL UPDATE.
8.3 Next-key locking:avoiding the ' phantom problem '
A next-key locking algorithm is used on InnoDB row-level locking. A row lock is made when InnoDB searches or scans the index of a table, and it sets the shared or exclusive lock on the index to which it is accessed. Thus the row lock is more precise and also known as index record locking.

InnoDB locks that are set on the index record also affect the gap (GAP) before the index record. If a user adds a shared or exclusive lock to the index record r, the other user will not be able to insert the new record immediately before R. This clearance lock is used to prevent the so-called "phantom problem". Suppose you want to read and lock a child row with an identifier greater than 100 in the table, and update some of the fields in the record that you are searching for.

SELECT * from child WHERE ID > for UPDATE;

Suppose there is an index field ID in the table child. Our query will start scanning the index records from the first record with ID greater than 100. Now, assuming that the lock on the index record does not block the insertion at the gap, a new child record may be inserted into the table in the transaction. If you are now executing again in a transaction

SELECT * from child WHERE ID > for UPDATE;

There will be a new child record in the recordset returned by the query. This is contrary to the rule of separation of transactions: a transaction must be able to run in a string, so that the data read in the transaction will not change. The new ' phantom ' sub record will break this isolation rule.

When the InnoDB scans the index, it also locks the gap in the index after the end record (the last recording). This only occurs in the previous example: the InnoDB set lock will block any inserts with an ID greater than 100.

A unique (uniqueness) check can be implemented in an application through a next-key locking: If reading data in a shared mode does not find duplicate values for the data that will be inserted, the lock is next-key during the read process will be set on the successor to your record (successor), which will prevent other users from inserting the same record during the period, so you can safely insert your records. So, Next-key locking can allow you to ' lock ' a record that doesn't exist in your table.
8.4 The lock setting for each SQL statement in InnoDB SELECT ... From ...: This is a consistent read, the snapshot of the database is not read in a locked way, unless the transaction's isolation level is set to SERIALIZABLE, in which case the shared Next-key locks will be set on the record index that it reads. SELECT ... From ... Lock in SHARE MODE: Sets the shared lock on all the record indexes that are read. SELECT ... From ... For UPDATE: Sets an exclusive (exclusive) lock on the Read Hu record index. INSERT into ... VALUES (...): set an exclusive lock on the inserted record line; Note that this lock is not a next-key lock and does not prevent other users from inserting new records in The Gap (GAP) before the inserted row. If a duplicate key value error is generated, a shared lock is set on the duplicate index record. If a auto_increment column is defined in a table, InnoDB sets an exclusive lock on the index corresponding to the last record of the increment when initializing the self-increase counter. When you access the InnoDB counter, it sets a special table lock mode Auto-inc, which lasts only until the end of the SQL statement rather than the end of the entire transaction. INSERT into T SELECT ... From S WHERE ... Set an exclusive (no Next-key) lock on each record that has been inserted into table T. Search table s with a consistent read, but if MySQL opens the log switch, a shared lock is set on table S. When a roll forward (roll-forward) fix is made from a backup, each SQL statement must be run in the order in which it was originally executed, so InnoDB had to set the lock. CREATE TABLE ... SELECT ... Similar to the previous item, complete the SELECT in consistent read or lock mode. REPLACE If there is not a unique key conflict, its execution is consistent with the insert. Otherwise, an exclusive lock is set on the record that it will update. UPDATE ... SET ... Where ...: An exclusive lock is set on the record that was encountered while searching. DELETE from ... WHERE ...: An exclusive lock is set on each record that is encountered during the search. If there is a FOREIGN KEY constraint on a table, all inserts, update, or delete that need to check the constraint will set a record share-level lock on the record on which the constraint is being checked. Also, InnoDB sets the lock when the constraint fails. Lock tables ...: Set table locks. Set these locks at the code layer of MySQL (layer of codes). InnoDB automatic deadlock detection cannot detect table locks on the following situations: see a section below. Also view the 14th Chapter ' InnoDB Limitations and deficiencies ' about the following: Since MySQL provides row locks, it is possible that you set a lock on a row-level lock when another user sets it. But that does not put transaction integerity into danger. Prior to version 3.23.50, Show Table STATUS, when applied to a self-added table, would set an exclusive row-level lock on the maximum record index of the self-added column. This means that show TABLE STATUS may cause a deadlock in a transaction, which may be unexpected. Starting with 3.23.50, any locks are no longer set when read from the added value, except in some cases, such as when the database was started. 8.5 When does MySQL implicitly commit (Commit) or rollback (rollback) transactions? If you do not use set Autocommit=0,mysql will open autocommit mode in a session. In autocommit mode, if an SQL statement does not return any errors, MySQL will commit immediately after this SQL statement. If an SQL statement returns an error, then Commit/rollback relies on this error. See detailed description of Country 13 chapters. The following SQL statement implicitly commits the current transaction in MySQL: CREATE table (if you use the MySQL binary log ' binlogging '), ALTER TABLE, BEGIN, CREATE INDEX, DROP Databa SE, DROP table, RENAME table, TRUNCATE, LOCK tables, UNLOCK tables. The CREATE TABLE statement is handled as a separate transaction in InnoDB. This means that a user cannot be in his businessUse ROLLBACK to undo the CREATE TABLE statement operation. If you turn off autocommit mode and do not commit your transaction with commit before closing a connection, MySQL will roll back your transaction. 8.6 Deadlock Detection and rollback
InnoDB automatically detects a deadlock on a transaction and rolls back one or more transactions to prevent deadlocks. Starting with version 4.0.5, InnoDB will try to extract small transactions for rollback. The size of a transaction is determined by the number of data rows that it inserts (insert), update (update), and delete (delete). Previous to 4.0.5, InnoDB always rolled back the transaction whose lock request is the last one to build a deadlock is, a cycle in the waits-for graph of transactions.

InnoDB cannot detect deadlocks caused by MySQL lock tables statements, or locks in other table types. You have to resolve these situations by setting the Innodb_lock_wait_timeout parameters in the my.cnf.

When InnoDB performs a full rollback of a transaction, the lock that is added to the office is released. However, if only one sentence of the SQL statement is rolled back due to an error returned by the result, the lock set by this SQL statement may be persisted. This is because InnoDB R's Row lock storage format does not know which SQL statement the lock is set by.
8.7 Consistent read in InnoDB operation example
Let's say you run at the default repeatable READ transaction isolation level. When you issue a consistent read, an ordinary SELECT statement, InnoDB will check the database for your transaction at a point in time (timepoint) according to your query. Thus, if transaction B deletes a row and commits after giving you a specified point in time, then you cannot know that the line has been deleted. Inserts (insert) and update (update) are also consistent.

You can advance your time point by submitting your transaction and sending a SELECT again.

This is called multiversioned concurrency control.
Time | | | | V

User A

User B

Set autocommit=0;

Set autocommit=0;

SELECT * from T;
Empty set

INSERT into T VALUES (1, 2);

SELECT * from T;
Empty set

COMMIT;

SELECT * from T;
Empty set;
COMMIT;
SELECT * from T;
---------------------
| 1 | 2 |
---------------------
Thus, user A will see the new row inserted by User B only after User B submits his insert and user a commits his transaction so that the point of time crosses the point at which the B commits.
If you want to view the "latest freshest" status of the database, you must use the Read Committed transaction isolation level, or you can use reading locks:
SELECT * from T LOCK in SHARE MODE;

　
8.8 How to deal with deadlocks?
Deadlocks are a classic problem with transactional database systems, but they are not dangerous unless they are so frequent that you can't handle a few things at all. When a rollback occurs because of a deadlock, you can usually issue a transaction again in your application.

InnoDB uses automatic row-level locking. You may happen to have a deadlock when inserting or deleting a single record. This is because these operations are not really atomic (atomic) level: They automatically lock the inserted/deleted row's index record (possibly several).

You can handle deadlocks or reduce the number of deadlocks by using the techniques shown below: Use show INNODB STATUS in the versions of MySQL >=3.23.52 and >= 4.0.3 to determine the cause of the last deadlock. This can help you adjust your application to avoid deadlocks. Always prepare to issue a transaction again when an error occurs due to a deadlock. Deadlocks are not dangerous. Just try it again. Always submit your affairs. Small transactions have fewer collisions possible. If you use lock to read a SELECT ... For UPDATE or ... LOCK in SHARE MODE, try to use the lower isolation level READ committed. Access your tables and records in a fixed order (a). This allows the transaction to form a finer queue and avoid deadlocks. Add the appropriate index to your table. Then your query only needs to scan fewer indexes, thus setting fewer locks. Use the EXPLAIN SELECT to determine the appropriate index that MySQL chooses for your query. Lock down as little as possible: if you can get the data you need in an older data snapshot with a single SELECT, do not add the clause for UPDATE or lock in SHARE MODE. It is a good idea to use the READ committed isolation level at this point because each consistent read in the same transaction reads only the snapshot of the data it first determines. If there is still no remedial effect, use table-level locking to serialize your transaction (serialize transactions): Lock TABLES T1 WRITE, T2 READ, ...; [Do something with tables T1 and T2 here]; UNLOCK TABLES. Table-level locking allows you to form fine queues for your transactions. Note that LOCK tables implicitly starts a transaction, just as the command begin,unlock tables implicitly ends a transaction as a COMMIT. Another solution to a serial transaction (serialize transactions) is to create a secondary "semaphore (semaphore)" Table with only one row of records. Each transaction updates the record before accessing the other tables. In this way all transactions will continue to execute. Note that the InnoDB real-time deadlock detection algorithm also works because this persistent lock (serializing lock) is a row lock. We have to take a time-out in MySQL for table-level locking.