MySQL — transaction



1. Business understanding

1.1 concept of transaction

Transaction processing can ensure that data oriented resources will not be updated forever unless all operations in the transactional unit are completed successfully. To become a transaction, a logical common unit must meet the so-called acid attribute, that is:
1. Atomicity (a)

  • For the modified data, either execute all or not execute all

2. Isolation (c)

  • Before all operations are performed, other sessions cannot see the intermediate change process

3. Consistency (I)

  • Before and after the transaction, the total amount should match according to the rules of the data

4. Persistence

  • Once a transaction is committed, the result is permanent, and the system crash will not affect it\
    Transaction example:
    start transaction; // Open transaction  
    insert into count (prefix,count,historycount) values('dy1','0','2');  
    select * from count

1.2 implementation of transaction

MySQL uses the current log method to ensure that transactions can run quickly and persistently, that is, write the log first and write the database. When a transaction starts, an LSN log serial number of the transaction will be recorded; When a transaction is executed, it will be sent to InnoDB_ log_ Insert the transaction log (redo log) into the buffer log buffer; When a transaction is committed, the transaction log in the log buffer will be flushed to disk. This action is performed by InnoDB_ flush_ log_ at_ trx_ Commit is controlled by this parameter.

  • When issuing a commit action, whether to brush the log is determined by the variable InnoDB_ flush_ log_ at_ trx_ Commit control
  • Brush once a second. The frequency at which the log is refreshed is determined by the variable InnoDB_ flush_ log_ at_ It is determined by the timeout value. The default is 1 second. This value has nothing to do with commit.
  • When more than half of the memory in the log buffer has been used, the????
  • When there is a checkpoint, the checkpoint represents the LS location of the log when refreshing the disk to a certain extent

You can use the commandshow engine innodb status \Gsee

Log sequence number 8619676075 (indicates the current LSN log sequence number)

Log flushed up to 8619676075 (indicates the LSN log serial number flushed to the transaction log)

Last checkpoint at 8619676075 (indicates the LSN log serial number refreshed to the disk)

In addition to recording transaction logs, the database also records a certain amount of undo logs. Undo is just the opposite of redo. When modifying data, it fails for some reason, or manually executes rollback statements, you can use these undo logs to roll back the data to the way it was before modification. Redo logs are saved in IB_ Logfile0 / 1 / 2, and the undo log is saved in ibdata1. In MySQL 5.6, you can also split the undo log sheet.

1.3 distributed transaction (preliminary understanding)

Resource Manager: manages the submission and rollback of transactions and provides resources to transactions
Transaction manager: communicate with resource management to coordinate and complete transaction processing

Two stages of the process for executing distributed transactions;

  • Phase 1: all branches are prepared. They are told by transaction management that they are ready to commit, and each branch resource manager records the actions of the branch and indicates the considered feasibility.
  • The second stage: the transaction manager tells the resource manager whether to commit or rollback. If all instructions can be submitted when preparing branches, all branches are told to submit. If one branch fails, all are rolled back. In special cases, when there is only one branch, the second stage is omitted.

The main function of distributed transactions is to ensure the consistency and integrity of transactions. Using the distributed computing environment, he combines multiple transactional activities into a transaction unit. These transactions are combined to form atomic operations. The activities of these transactions either execute and commit transactions together or roll back all operations, so as to ensure the consistency and integrity among multiple activities.

2. Transactions and logs

–To view the transaction log:
show engine innodb status\G;

–View log file setting status

show variables like ‘innodb_%’;

2.1 understanding of transaction characteristics

Transaction log file\
innodb_ log_ files_ in_ Group: set several groups of transaction logs in dB. The default is 2\
innodb_ log_ group_ home_ Dir: transaction log storage directory, not set, IB_ Logfile0… Exists in the data file directory
InnoDB storage engine can store all data in the shared table space of ibdata *, or store each table in a separate table space of a separate. IBD file\

Note: in mysql, log files are the most important for data\
redo log => ib_logfile0\
undo log => ibdata\

2.1.1 atomicity:

A series of operations of a transaction are either all performed or not performed\
Atomicity and rollback log:\
To ensure the atomicity of transactions, you need to roll back the executed operations when exceptions occur. In mysql, the recovery mechanism is implemented through the undo log. All transaction modifications will be recorded in the rollback log first, and then written to the corresponding row in the database\
Note: after the system crashes and the database process is directly killed, when the user starts the database process again, he can immediately roll back the unfinished transactions by querying the rollback log, which requires that the rollback log must be persisted to the disk before the data. This is the main reason why we need to write the log before writing to the database\
In the log file: each insert used in the transaction corresponds to a delete, and each update corresponds to an opposite UPDATE statement\

2.1.2 persistence:

When the transaction is committed, the data will be written to the database and stored permanently. Generally speaking, after the transaction has been committed, it cannot be rolled back again\
Persistence and redo logs:\
Like atomicity, transaction persistence is also achieved through logs. MySQL uses redo logs to achieve transaction persistence. Redo logs are composed of two parts: one is the redo log buffer in memory, which is volatile because it is in memory, and the other is the redo log file on disk, which is persistent\

2.1.3 rollback log and redo log:

Rollback log (undo log) and redo log (redo log); In the database system, the atomicity and persistence of transactions are guaranteed by the transaction log. During implementation, that is, the two logs mentioned above. The former is used to undo the impact of transactions, and the latter redoes the committed transactions during error handling. They can ensure two points:\
one   Transactions that have errors or need to be rolled back can be rolled back successfully (atomicity)\
two   After the transaction is committed, if the data goes down before writing to the disk, the data can be successfully recovered after the next restart (persistence)\
In the database, these two logs often work together. We can regard them as a transaction log as a whole, which contains the transaction ID, modified row elements and values before and after modification.

2.1.4 transaction flow

MySQL checkpoint reference….
Checkpoint, i.e. checkpoint. Writing a checkpoint in the undo log indicates that the transactions before the checkpoint have completed the commit or rollback, that is, the transactions before the checkpoint have no data consistency problem.

InnoDB’s transaction log refers to redo log, or log for short, which is saved in the log file Ib_ Log file (go to the MySQL data directory). InnoDB also has another log undo log, but undo log is stored in the shared table space (ibdata * file, which stores the check point log serial number).

A transaction log of InnoDB goes through four stages\
1) Creation phase: create a transaction log\
2) Log refresh: log files written to disk\
3) Data refresh: the dirty data corresponding to the log is written to the data file on the disk\
4) Write to CKP: the log is written to the log file as a checkpoint\

innodb_ flush_ log_ at_ trx_ Commit parameter parsing

//View log file setting status
show variables like 'innodb_%';

set @@global.innodb_flush_log_at_trx_commit = 0; -- 0,1,2

show variables like 'innodb_flush_log_at_trx_commit';
  • Performance comparison: 0 > 2 > 1
    Data security comparison: 1 is the best, so 1 is selected by default

3. Locking mechanism

Different storage engines of MySQL support different locking mechanisms

3.1 lock type.

  1. MyISAM and memory storage engines use table level locks, while BDB engine uses page level locks and supports table level locks. Since the BDB engine has basically become history, it will not be introduced.
  2. InnoDB storage engine supports both row level locks and table level locks. Row level locks are used by default.
  3. The so-called table level lock directly locks a table, with low overhead and fast locking. There will be no deadlock. The locking granularity is large, the probability of lock conflict is higher, and the concurrency is the lowest.
  4. The so-called row level lock directly locks a record, which has high overhead, slow locking, low probability of lock conflict and high concurrency.
  5. The so-called page level lock is a locked page. In InnoDB, a page is 16kb. Its overhead is between table level lock and row level lock, and deadlock may occur. The locking granularity is also between table level lock and row level lock, and the concurrency is also between table level lock and row level lock.
  6. Only from the perspective of locks, table level locks are more suitable for query based applications, and there are only a few applications that update data according to index conditions
  7. Row level locks are more suitable for a large number of concurrent updates of a small amount of different data according to index conditions, as well as the application of concurrent queries

3.2 InnoDB row level lock

InnoDB has two types of row level locks and two internal intention locks;

  • Shared lock (s): an exclusive lock (update, insert, delete) that allows one transaction to read a row of data and prevents other transactions from reading the same data.
  • Exclusive lock (x): allows transactions that obtain exclusive locks to update data, and prevents other transactions from obtaining shared locks and exclusive locks of the same data.
  • Intentional shared lock (is): the transaction intends to add a row shared lock to the data row. The transaction must obtain the is lock of the table before adding a shared lock to a data row.
  • Intentional exclusive lock (IX): the transaction intends to add row exclusive locks to data rows. The transaction must obtain the IX lock of the table before adding an exclusive lock to a data row.
  • Pessimistic lock (abstract, unreal lock): when operating the database (update operation), you are optimistic that this operation will not lead to conflict. When operating the data, you do not carry out any other special processing (i.e. no lock), but judge whether there is a conflict after updating
  • Optimistic lock (abstract, unreal lock): observing lock means that when operating data, it is considered that there will be data conflict during this operation. Therefore, each operation can only operate on the same data by obtaining the lock. Therefore, pessimistic lock takes more time. In addition, corresponding to the optimistic lock, the pessimistic lock is implemented by the database itself. When we want to use it, we can directly call the relevant statements of the database

Coexistence logical relationship table of four kinds of locks

Lock mode Shared lock Exclusive lock (x) Intent shared lock (is) Intent exclusive lock (IX)
Shared lock compatible conflict compatible conflict
Exclusive lock (x) conflict conflict conflict conflict
Intent shared lock (is) compatible conflict compatible compatible
Intent exclusive lock (IX) conflict conflict compatible compatible

Intent locks are automatically added by the InnoDB storage engine. For ordinary select statements, InnoDB will not add any locks. For insert, update and delete statements, InnoDB will automatically add exclusive locks to the data involved. InnoDB displays the added shared locks and exclusive locks through the following statements

Shared lock statement

select * from table_name lock in share mode;

Exclusive lock statement

select * from table_name for update;

About intention lock;

Intent lock is a table level lock, which is designed to reveal the type of lock to be requested in the next row in a transaction. Two table locks in InnoDB:

Intentional shared lock (is): indicates that the transaction is ready to add a shared lock to the data row, that is, the is lock of the table must be obtained before adding a shared lock to a data row. If you need to add a shared lock to record a, InnoDB will find the table first, add an intention shared lock to the table, and then add a shared lock to record a

Intentional exclusive lock (IX): similar to the above, it indicates that the transaction is ready to add an exclusive lock to a data row, that is, the transaction must obtain the IX lock of the table before adding an exclusive lock to a data row. If it is necessary to add an exclusive lock to record a, InnoDB will find the table first, add an intention exclusive lock to the table, and then add an exclusive lock to record a
The intent lock is automatically added by InnoDB without user intervention

For shared locks and exclusive locks, the system will automatically add shared locks or exclusive locks under specific conditions, or you can add shared locks or exclusive locks manually.
Intentional shared locks and intentional exclusive locks are automatically added and released by the system without manual intervention

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

3.3 transformation and attention of InnoDB row lock and table lock

InnoDB row level locks are implemented by locking index items. InnoDB row level locks can only be used when data is retrieved through index conditions; Otherwise, table level locks are used.

When querying without index (primary key) conditions, InnoDB is a table lock rather than a row lock.

Typically, begin end is used to define a set of statement blocks

Note: if the query criteria are not indexed, the row lock will be converted to a table lock, even if the fields in the table have a primary key; Therefore, it is recommended to use index field for query

3.4 InnoDB clearance lock

It can be understood as locking the data in a certain range. If there is no data in this range, it will also be locked; It mainly solves the problem of unreal reading. If no gap lock is added, and if a record with ID between 1 and 100 is added to other things, unreal reading will occur; On the other hand, it is considered to meet the needs of its recovery and assignment.

Use of MySQL official website gap lock…

MySQL official website gap lock attribute interpretation…

By default, InnoDB_ locks_ unsafe_ for_ Binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses the next key lock for search and index scanning. To enable this variable, set it to 1. This will cause gap locking to be disabled: InnoDB only uses index record locks for search and index scanning.

3.5 how MySQL handles deadlocks

The official definition is as follows: both transactions hold the lock required by the other party and are waiting for the other party to release, and neither party will release its own lock

MySQL has two deadlock handling methods:

  • Wait until timeout (innodb_lock_wait_timeout = 50s).
  • Initiate deadlock detection, actively roll back one transaction and let other transactions continue to execute (innodb_deadlock_detect = on)

Deadlock detection:
The principle of deadlock detection is to construct a directed graph with transaction as vertex and lock as edge, and judge whether there is a ring in the directed graph.
After deadlock is detected, select the transaction rollback with the least number of rows inserted, updated or deleted, based on information_ SCHEMA.INNODB_ TRX in TRX table_ Weight field.

3.6 suggestions for locks and transactions

3.6.1 recommendations for locks

Collect deadlock information:
Use the command show engine InnoDB status to view the cause of the deadlock.
Start InnoDB during debugging_ print_ all_ Deadlocks to collect all deadlock logs.

Reduce deadlocks:
Use transactions instead of lock tables.
Ensure that there are no long transactions.
Commit the transaction immediately after the operation, especially on the interactive command line.
If you are using (select… For update or select… Lock in share mode), try to reduce the isolation level.
When modifying multiple tables or rows, keep the order of modification consistent.
Creating an index can create fewer locks.
It is better not to use (select… For update or select… Lock in share mode).
If none of the above problems can be solved, try locking multiple tables with lock tables T1, T2 and T3

3.6.2 suggestions on transactions

Because InnoDB storage engine implements several rows, the particle is smaller and the implementation is more complex. However, the concurrency performance of InnoDB row lock is much higher than that of table lock and page lock. In terms of use, the following points can be achieved as far as possible;

  1. Control the transaction size to reduce the amount of locked resources and the length of locked time.
  2. All data retrieval is done by index, so as to avoid upgrading to table lock because it is impossible to lock by index.
  3. Reduce the filtering conditions of range based data retrieval to avoid locking the data that should not be locked due to the negative impact of gap lock.
  4. If business conditions permit, try to use lower isolation level transaction isolation. Reduce additional costs associated with isolation levels.
  5. Use the index reasonably to make InnoDB lock the index more accurate.
  6. In the application, the access sequence shall be executed as far as possible
  7. If it is easy to deadlock, you can consider using table lock to reduce the probability of deadlock

This work adoptsCC agreement, reprint must indicate the author and the link to this article