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

What is a transaction?

A set of atomic SQL statements that can be committed or rolled back to atomic units of work. When a transaction makes multiple changes to the database, either all changes succeed after the transaction is committed, or all changes are undone when the transaction is rolled back.

Transaction characteristics ACID property A: atomicity (atomicity): all operations in the entire transaction either succeed or all fail and roll back; C: consistency (consistency): the entire database always transitions from one consistency state to another; I: isolation (isolation): the operations done by one transaction are not seen by other transactions until they are committed. D: durability: once a transaction is committed, its changes are permanently saved in the database. Transaction life cycle

Transaction management explicitly starts the transaction: BEGINBEGIN WORKSTART TRANSACTION ends the transaction: # commit COMMIT# rollback ROLLBACK Note: only the DML statement of the transactional storage engine can support automatic commit of such operations: default is 1, if 0 is set to non-auto commit > show variables like 'autocommit' +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ 1 row in set (0.00 sec) set autocommit= {1 | 0} Note: it is recommended to explicitly request and commit transactions instead of using the "autocommit" feature. Transaction support SavePoint: SAVEPOINT identifierROLLBACK [WORK] TO [SAVEPOINT] identifierRELEASE SAVEPOINT identifier View transaction: # View current transaction SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;# View current locked transaction SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;# View current locked transaction SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS Transaction isolation level MySQL transactions support four isolation levels, from top to bottom more stringent transaction isolation level description dirty read possibility non-repeatable read possibility phantom read possibility READ UNCOMMITTED can read to uncommitted data, dirty read YesYesYesNoREAD COMMITTED can read committed data, but uncommitted data is not readable, multiple committed data can be read, resulting in inconsistent data read each time NoYesYesNoREPEATABLE READ can be read repeatedly The data read many times are consistent, resulting in illusory reading, that is, in the reading process, even if other committed transactions modify the data, they can only read the old data before it is modified. This is the MySQL default setting YesYesYesNoSERIALIZABLE serializable, uncommitted read transactions block modified transactions (add read locks, but do not block read transactions), or uncommitted modified transactions block read transactions (add write locks, read and write of other transactions cannot be performed). Isolation levels of NoNoNoYesMVCC and transactions that can lead to poor concurrent performance

MVCC (multi-version concurrency control mechanism) only works under the two isolation levels of REPEATABLE READ and READ COMMITTED. The other two isolation levels are not compatible with MVCC because READ UNCOMMITTED always reads the latest rows of data rather than rows that match the current transaction version. SERIALIZABLE locks all read rows.

How to specify transaction isolation level reference: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tx_isolation1. The system variable tx_isolation is specified and defaults to REPEATABLE-READ. Valid range: Global, Session# syntax: set tx_isolatioin='value';# valid value READ-UNCOMMITTEDREAD-COMMITTEDREPEATABLE-READSERIALIZABLE2. The server option specifies to use the transaction-isolation option to set the [mysqld] transaction-isolation = value transaction isolation level verification background in the [mysqld] block of the / etc/my.cnf file: verify the two transactions through two terminal displays. The table contents and table structure of the user table used for verification are as follows: mysql > desc user +-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | int (10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar (30) | NO | | NULL | | +- -+ 2 rows in set (0.00 sec) mysql > select * from user +-+-+ | id | name | +-+-+ | 2 | root | | 3 | admin | +-+ 2 rows in set (0.00 sec) 1.READ UNCOMMITTED level

2.READ COMMITTED level

3.REPEATABLE READ level

Uncommitted read transactions at the 4.SERIALIZABLE level block modify transactions (add read locks, but do not block read transactions)

Uncommitted modified transactions block read transactions (with write locks, read and write of other transactions cannot be performed)

Note: this transaction isolation level can lead to poor concurrency performance. Additional case: if a transaction generates a write lock and then does not end the transaction, this will result in the read and write of other transactions cannot be executed. In this case, you can find the unfinished and blocking transaction and kill it manually. It is verified by the case that the above uncommitted modified transaction blocks the read transaction.

Open a third session and do the following

Deadlock

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.