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

This article mainly introduces "the summary of knowledge points of MySQL transactions and locks". In the daily operation, I believe that many people have doubts about the summary of knowledge points of MySQL transactions and locks. The editor has consulted all kinds of materials and sorted out simple and useful methods of operation. I hope it will be helpful to answer the doubts of "summary of knowledge points of MySQL transactions and locks". Next, please follow the editor to study!

MySQL transaction and lock

1. Transaction and lock

1.1. Business

A transaction is a set of data operation execution steps, which are treated as a unit of work for grouping multiple statements, which can be used when multiple clients concurrently access data in the same table; all steps succeed or fail, if all steps are normal, they are executed, if the steps are incorrect or incomplete, they are cancelled; the transaction is ACID compliant.

With transactions, you can execute one or more SQL statements as a unit of work, so that all statements either succeed or fail. This can occur in isolation from the work performed by any other transaction. If all statements are successful, you can commit the transaction to permanently record its effect in the database. If an error occurs during a transaction, you can roll back to cancel it. Any statements previously executed in the transaction are undone, leaving the database in the same state as it was before the transaction started.

Note: in MySQL, only tables that use a transaction storage engine, such as InnoDB, support transactions. These statements do not have any significant impact on tables managed by the non-transactional storage engine.

MySQL transactions comply with ACID:

Atomic (atomicity): all statements are executed successfully or cancelled as a unit.

Consistent (consistency): if the database is in a consistent state at the beginning of the transaction, the consistent state is retained during the execution of the transaction.

Isolated (isolation): transactions do not affect each other.

Durable (persistence): after the transaction completes successfully, all changes made are accurately recorded in the database. Changes made will not be lost

1.1.1 transaction SQL control statement

START TRANSACTION (or BEGIN): explicitly start a new transaction

SAVEPOINT: allocates a location during a transaction for future reference

COMMIT: permanently records changes made by the current transaction

ROLLBACK: cancels changes made by the current transaction

ROLLBACK TO SAVEPOINT: canceling changes made after savepoint

RELEASE SAVEPOINT: deleting savepoint identifiers

SET AUTOCOMMIT: disable or enable the default autocommit mode for the current connection

1.1.2 AUTOCOMMIT mode

How to set the AUTOCOMMIT mode determines how and when to start a new transaction. By default, AUTOCOMMIT is enabled globally, which means that each SQL statement is forced to start a new transaction implicitly. You can disable AUTOCOMMIT globally through a configuration file, or you can disable it per session by setting the autocommit variable. Enabling AUTOCOMMIT restricts each statement, which in turn affects the transaction table in its own transaction. This effectively prevents multiple statements from being executed in a transaction. This means that you will not be able to submit or roll back multiple statements as a unit through COMMIT or ROLLBACK. Sometimes this situation is mistaken for no transaction at all. However, this is not the case. When AUTOCOMMIT is enabled, each statement is still executed atomically. For example, by comparing the effect of violating constraint restrictions when inserting multiple rows, you can see the difference between enabling AUTOCOMMIT and having no transaction at all. In a non-transactional table, such as MyISAM, once an error occurs, the statement terminates and rows that have been inserted remain in the table. For the InnoDB table, all rows that have been inserted are deleted from the table without any actual impact.

AUTOCOMMIT determines how and when to start a new transaction; by default, AUTOCOMMIT mode is enabled: each statement is implicitly committed as a transaction

If the AUTOCOMMIT mode is set to 0 in my.cnf, or SET GLOBAL AUTOCOMMIT=0;SET SESSION AUTOCOMMIT=0;SET @ @ AUTOCOMMIT: = 0, AUTOCOMMIT is disabled, the transaction spans multiple statements, and you need to end the transaction using COMMIT or ROLLBACK

Use SELECT to check the AUTOCOMMIT settings:

SELECT @ @ AUTOCOMMIT

1.1.3 implicit submission

The COMMIT statement always explicitly commits the current transaction. Other transaction control statements, such as those listed in this slide, also have the effect of implicitly committing the current transaction. In addition to these transaction control statements, other types of statements may also have the effect of implicitly committing and terminating the current transaction. These statements behave as if COMMIT were issued before the actual statement was executed. In addition, these statements themselves are not transaction statements, that is, if successful, they cannot be rolled back. Typically, data definition statements, data access and user management statements, and Lock statements have this effect.

Note: there are many exceptions, and not all of these statements can lead to implicit commit on all versions of the server. However, it is recommended that all non-DML statements be considered to result in implicit commit. For a complete list of statements that result in implicit submission, see the MySQL reference Manual: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

An implicit commit terminates the current transaction. SQL statement for implicit submission:

L START TRANSACTION

L SET AUTOCOMMIT = 1

The non-transactional statement that caused the commit:

L data definition statements (ALTER, CREATE, and DROP)

L administrative statements (GRANT, REVOKE, and SET PASSWORD)

L Lock statements (LOCK TABLES and UNLOCK TABLES)

Example of a statement that causes an implicit submission:

Mysql > TRUNCATE TABLE

Mysql > LOAD DATA INFILE

1.1.4 transaction storage engine

Use SHOW ENGINES to list engine characteristics:

Mysql > SHOW ENGINES\ G

* 2. Row *

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking

And foreign keys

Transactions: YES

XA: YES

Savepoints: YES

* 1. Row *

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

...

To ensure that the transaction storage engine is compiled into the MySQL server and can be used at run time, use the SHOW ENGINES statement. The value in the Support column is YES or NO, indicating whether the engine is available. If the value is DISABLED, the engine exists but is turned off. The value DEFAULT is used to indicate which storage engine the server uses by default. The engine specified as DEFAULT should be considered available. The Transactions, XA, and Savepoints columns are used to indicate whether the storage engine supports these features.

1.2. Transaction isolation level

1.2.1 introduction to isolation level

If one client's transaction changes data, whether other client's transactions should discover these changes or should be isolated from them, the transaction isolation level determines how simultaneous transactions interact with each other when accessing the same data. The isolation level can be achieved using the storage engine. Isolation level options vary from database server to database server, so the levels implemented by InnoDB may not exactly correspond to those implemented by other database systems. InnoDB can implement four isolation levels to control the extent to which changes made by the transaction can be noticed by other simultaneous transactions. The four levels of isolation are as follows:

L READ UNCOMMITTED: allows transactions to view uncommitted changes made by other transactions; allows "dirty" reads, non-repeatable reads, and virtual reads.

L READ COMMITTED: allows transactions to view committed changes made by other transactions; allows unrepeatable and virtual reads to occur. Uncommitted changes are still not visible.

L REPEATABLE READ: ensure that the SELECT output of each transaction is consistent, the default level of InnoDB; you will get the same result twice, regardless of whether changes made by other transactions have been committed or not. In other words, different transactions produce consistent results for the same data.

L SERIALIZABLE: completely isolates the results of one transaction from other transactions; similar to REPEATABLE READ, but more restrictive, that is, the rows selected by one transaction cannot be changed by other transactions until the first transaction is completed.

1.2.2 set isolation level

The system default transaction level is: repeatable-read

Method 1. Set the level when the server starts.

-use the-- transaction-isolation option in the mysqld command.

-set transaction-isolation in the configuration file:

[mysqld]

Transaction-isolation =

Set the value in the configuration file or on the command line to:

L READ-UNCOMMITTED

L READ-COMMITTED

L REPEATABLE-READ

L SERIALIZABLE

Method 2. Use the SET TRANSACTION ISOLATION LEVEL statement to set up the running server.

-example of syntax:

SET GLOBAL TRANSACTION ISOLATION LEVEL

SET SESSION TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL

For the SET TRANSACTION ISOLATION LEVEL statement, set the value to:

L READ UNCOMMITTED

L READ COMMITTED

L REPEATABLE READ

L SERIALIZABLE .

This transaction level can be set globally or on a per-session basis. If not explicitly specified, the transaction isolation level is set on a per-session basis. For example, the following statement sets the isolation level for the current mysql session to READ COMITTED:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This statement is equivalent to:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

To set the default level for all subsequent mysql connections, use the GLOBAL keyword instead of SESSION:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED

Note: the global default transaction isolation level set applies to all newly established client connections from the time of setup. Existing connections are not affected.

Method 3. SET GLOBAL TX_ISOLATION

SUPER permission is required

Mysql > set global tx_isolation=' READ-COMMITTED'

Mysql > select @ @ tx_isolation

Mysql > show variables like 'tx_isolation'

Transaction_isolation MySQL 5.7.20 was introduced to replace the soon-to-be-deprecated tx_isolation (MySQL 8.0)

(root@localhost) [information_schema] > show variables like'% isolat%'

+-+ +

| | Variable_name | Value |

+-+ +

| | transaction_isolation | REPEATABLE-READ |

| | tx_isolation | REPEATABLE-READ |

+-+ +

Transaction_isolation was added in MySQL 5.7.20 as an alias for tx_isolation, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation.

1.3. Lock concept

MySQL uses multithreaded architecture, and multiple clients will have problems when accessing a table, so it is necessary to coordinate with clients. Lock is a mechanism to prevent concurrency problems, which is managed by the server, Lock is accessed by one client, and other client access is restricted. Lock types: shared locks, mutexes

The Lock mechanism prevents problems caused by multiple clients accessing data at the same time. This mechanism Lock data as one client to restrict access to that data by other clients until Lock is released. The Lock allows clients with locks to access data, while restricting what other clients competing for access can do. As a result of the Lock mechanism, access to data is serialized so that each client has to wait in turn when multiple clients are about to perform conflicting operations. Not all types of concurrent access are conflicting, so the type of Lock required to allow the client to access the data depends on whether the client wants to read or write:

If a client wants to read data, other clients that want to read the same data will not conflict and they can read at the same time. However, if another client wants to write (modify) data, it must wait until the read is complete.

If one client wants to write data, all other clients must wait until the write is complete, regardless of whether they want to read or write.

Readers must block writers, but not other readers. Writers must block both readers and writers. These restrictions can be enforced through read and write locks. With Lock, you can put the client into a waiting state until you can safely access the data. In this way, Lock can prevent data corruption by preventing conflicting changes from being made concurrently and reading data that is being changed.

1.3.1 explicit row lock

InnoDB supports two types of row Lock:

LOCK IN SHARE MODE: Lock each line using a shared lock

SELECT * FROM Country WHERE Code='AUS' LOCK IN SHARE MODE\ G

FOR UPDATE: use mutexes to Lock every line

SELECT counter_field INTO @ @ counter_field

FROM child_codes FOR UPDATE

UPDATE child_codes SET counter_field =

@ @ counter_field + 1

InnoDB supports two types of Lock modifiers that can be added to the end of the SELECT statement:

LOCK IN SHARE MODE clause: shared locks, that is, although no other transaction can acquire a mutex, other transactions can use shared locks at the same time. Because normal reads do not Lock anything, they are not affected by Lock.

FOR UPDATE clause: use mutexes to Lock each selected row to prevent other objects from acquiring any locks on those rows, but allow those rows to be read.

At the REPEATABLE READ isolation level, LOCK IN SHARE MODE can be added to the SELECT operation so that if other transactions want to modify the selected row, they must wait for the current transaction to complete. This works similar to the SERIALIZABLE isolation level, where InnoDB implicitly adds LOCK IN SHARE MODE to the SELECT statement without any explicit Lock modifiers. If a row modified in an uncommitted transaction is selected, it is Lock SELECT until the transaction commits.

1.3.2 deadlock

A deadlock occurs if multiple transactions need to access the data and another transaction has already Lock the data in a mutually exclusive manner. When a circular dependency occurs between two or more transactions. For example, T1 waits for resources from T2 Lock, while T2 waits for resources from T3 Lock, while T3 waits for resources from T1 Lock. InnoDB detects and aborts (rolls back) one of the transactions and allows the other to complete.

Deadlocks are a classic problem in transactional databases, and they are not harmful unless they occur so often that you cannot run some transactions at all. The conditions for deadlocks are as follows:

Transactions get Lock on multiple tables, but in reverse order.

Such as UPDATE or SELECT. Statements such as FOR UPDATE Lock a series of index records and gaps, in which each transaction only gets part of the Lock because of timing problems.

There are multiple transactions, each of which is waiting for another transaction to complete, forming a loop. For example, T1 is waiting for T2, T2 is waiting for T3, and T3 is waiting for T1.

If InnoDB performs a full rollback of a transaction, all Lock set by that transaction will be released. However, if only one SQL statement is rolled back due to an error, some of the Lock set by that statement may be retained. The reason for this problem is that InnoDB stores row locks in such a format that it cannot subsequently recognize the correspondence between locks and statements. If a SELECT statement calls a stored function in a transaction and an error occurs in a statement in that function, the statement will be rolled back. At the same time, if ROLLBACK is executed later, the entire transaction will be rolled back.

For more information about InnoDB deadlocks, see the MySQL reference Manual:

Http://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html .

Transaction example: deadlock

Session 1

Session 2

S1 > START TRANSACTION

S1 > UPDATE Country

-> SET Name = 'Sakila'

-> WHERE Code = 'SWE'

S2 > START TRANSACTION

S2 > UPDATE Country

-> SET Name = 'World Cup Winner'

-> WHERE Code = 'ITA'

S1 > DELETE FROM Country

-> WHERE Code = 'ITA'

S2 > UPDATE Country

-> SET population=1

-> WHERE Code = 'SWE'

ERROR 1213 (40001): Deadlock

Found when trying to get lock

Try restarting transaction

Query OK, 1 row affected (0.0 sec)

The first DELETE statement hangs while waiting for a lock. During the execution of the UPDATE statement, a deadlock was detected in session 2 because of a conflict between the two sessions. The UPDATE will be aborted, allowing the DELETE in session 1 to complete.

1.3.3 implicit lock

The MySQL server Lock the table (or row) based on the command issued and the storage engine used:

Operation

InnoDB

MyISAM

SELECT

Unlocked *

Table-level shared lock

UPDATE/DELETE

Row-level mutex

Table level mutex

ALTER TABLE

Table-level shared lock

Table-level shared lock

* No lock unless SERIALIZABLE level, LOCK IN SHARE MODE, or FOR UPDATE is used

The InnoDB table uses row-level Lock so that multiple sessions and applications can read and write to the same table at the same time without waiting for each other or producing inconsistent results. For this storage engine, avoid using LOCK TABLES statements; it does not provide any additional protection, but reduces concurrency.

With automatic row-level Lock, you can make these tables suitable for the busiest databases that store the most important data, while simplifying application logic because you don't have to Lock and unlock the tables. In this way, the InnoDB storage engine becomes the default setting in MySQL 5.6

At this point, the study of "knowledge summary of MySQL transactions and locks" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.