Please correct any mistakes

1. What are transactions? Transaction characteristics and open and commit demonstration

Call one or more database operations that require atomicity, isolation, consistency, and persistence a transaction

  • Transaction characteristics

    -- Atomicity -- all success or all failure -- Isolation -- The state transition in A transaction will not affect the state transition in B transaction. Add money) -- Consistency -- the end result is consistent as long as it conforms to all real-world constraints. "Consistency is guaranteed by atomicity and Isolation" - Durability Durability - When a state transition in the real world is complete, the results of that transition persist forever - this rule is called persistenceCopy the code

  • Transaction open, commit, close, and transaction types, implicit commit and save points

    - open the BEGIN; -- START TRANSACTION; Type -- READ ONLY: indicates that the current transaction is a read-only transaction, that is, database operations belonging to the transaction can ONLY READ data, but cannot modify data -- READ WRITE: Identifies the current transaction as a read-write transaction, meaning that database operations belonging to the transaction can both read and modify data. -- WITH CONSISTENT SNAPSHOT: START TRANSACTION READ ONLY; START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT; START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT -- If not specified, the default is READ/WRITE mode -- COMMIT and terminate COMMIT; ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK ROLLBACK SHOW VARIABLES LIKE 'autocommit' SHOW VARIABLES LIKE 'autocommit'; Variable_name Value autocommit ON; Variable_name Value autocommit ON; This feature is called automatic submission of transactions so you can see why we set @Transactional(rollbackFor = exception.class) in the Service layer to implicitly commit and savepoint transactions ALTER, CREATE, DROP, GRANT, RENAME, REVOKE, and SET PASSWORD implicitly commit the transaction to which the preceding statement belongs. SAVEPOINT SAVEPOINT name; -- ROLLBACK TO a savepoint name.Copy the code

Example of transaction rollback: Example of a normal transaction commit:

Transaction savepoint demo:

Insert before:

Set savepoint 2 after 1 and roll back to savepoint 1 after completion:

2. The isolation level of issues that can occur in concurrent transactions

This is a serious condition in which a transaction changes data that has been modified by another uncommitted transaction. Therefore, all four isolation levels, including the lowest isolation level, will solve the problem of dirty writes

2.1 dirty reads:

If a transaction reads data that has not been committed by another transaction, it means that a dirty read has occurredCopy the code

Dirty reading schematic diagram:

2.2 Repeatable

For example, when transaction 1 reads a table record, transaction 2 updates the record and commits it. When transaction 1 reads the table record again, it finds that the contents are inconsistent with those of the first time. That is, the result of reading the same record multiple times during the execution of something is different. The schematic diagram is as follows:

2.3 phantom read

If one transaction first queries records based on certain conditions, and then another transaction inserts records that meet those conditions, the original transaction can read the records inserted by the other transaction when it queries according to those conditions, it means that a phantom read has occurred

Schematic diagram:

The difference between a phantom read and a phantom read is that a phantom read is typically for insert. A phantom read and an unrepeatable read are those that read another transaction that has been committed. However, non-repeatable read queries are all the same data item, while unreal read is for a batch of data as a whole (such as the number of data).

Transaction isolation level 4 Default mysql unrepeatable read Oracle commit by default

  • Read Uncommitted is when changes made by a transaction can be seen by other transactions before a transaction is committed. It can solve the three problems of dirty writing but dirty reading and repetitive reading can not be solved

  • Read Committed means that after a transaction is Committed, its changes can only be seen by other transactions. Dirty reads can be fixed (because they are not seen by other transactions until committed), but duplicate reads and phantom reads cannot be fixed

  • Repeatable Read refers to: The data that a transaction a sees during its execution is always the same as the data that the transaction A sees when it starts. Can solve dirty read, can repeat read but can not solve phantom read

  • Serializable: As the name implies, for the same row, “write” will add “write lock”, “read” will add “read lock”. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue. Can solve dirty reading, repeatable reading, unreal reading

3. binlog

What is a binlog?

  • The binlog is used to record writing operations (excluding queries) performed by the database and is stored in binary format on disk.
  • Binlog is the logical log of mysql and is logged by the Server layer. Binlog is logged by mysql databases using any storage engine.
  • Binlog type:
- Logical log: Records SQL statements. - Physical log: Mysql data is ultimately stored in data pages. Physical logs record changes to data pages.Copy the code

Note: The binlog file is written apend. You can set the size of each binlog file with the max_binlog_size parameter. When the file size reaches a given value, a new file is generated to hold the log.

Binlog application scenario

In practical applications, binlog can be used in master/slave replication and data recovery.

  • A master-slave replicationIn:Master The openbinlog And thenbinlog Send to eachSlave End,Slave Also be putbinlog To achieve master/slave data consistency.
  • Data recovery: by usemysqlbinlog Tools to recover data.

Binlog Flush time

For InnoDB storage engine, biglog is only recorded when a transaction commits, while biglog is still in memory. When is biglog flushed to disk? Mysql uses the sync_binlog parameter to control the flush time of Biglog. The value ranges from 0 to N

  • 0: the system determines when to write data to the disk.
  • 1: every timecommit All the timebinlog Write to disk;
  • N: Every N transactionsbinlog Write to disk.

As you can see from the above, sync_binlog is safest set to 1, which is also the default for MySQL versions after 5.7.7. However, setting a larger value can improve database performance, so it is possible to increase the value appropriately and sacrifice some consistency for better performance.

4. undo log

  • What is an undo log?

    atomicThe bottom is throughundo log The implementation.undo log It mainly records logical changes of data, such as one INSERTStatement, corresponding to oneDELETE theundo log , for everyUPDATE Statement, corresponding to an oppositeUPDATE theundo log So that in the event of an error, the data state prior to the transaction can be rolled back. At the same time,undo log Is alsoMVCC (Multi-version concurrency control) key implementation.

5. Redo log Indicates redo logs

  • What is a redo log?

    Redolog is a disk-based data structure used to correct data written by failed transactions during crash recovery. During normal operation, Redolog encodes requests to change table data generated by SQL statements or low-level API calls.The redo log guarantees important features of persistence: ->>>>> (during initialization and before connection acceptance, changes to uncompleted updated data files are automatically reprioritized before unexpected shutdown) <<<<<<.

    Note: Redo logs are unique to the InnoDB engine
  • Why do redo logs exist?

    Redolog is designed by mysql to relieve mysql IO pressure if every transaction is written to disk after committing

  • Redo log of

    1.redolog buffer

    2.redologfile

  • Redo log Parameter Description

How do redo logs and undo logs write to disk

Write to cache before Write to disk is also abbreviated as write-ahead Logging (WAL), which means that every DML is written to cache before being written to disk by fsync(). The mmap Filechannel used in NIO, for example, is something that I personally understand to have in common

Binlog and Redolog

Mysql > insert, update, delete What about success? What happens to failure?

  • Look at the picture:

6. Mysql lock

  • In my other article ~ because I can’t put it here > juejin.cn/post/699774…

7. MVCC multi-version concurrency control

The relationship between MVCC and transaction isolation level is discussed here

Read View is used to support the implementation of Read Committed (RC) and Repeatable Read (RR) isolation levels.

RR(repeatable read), RC(read committed) generation time

  • RCAt the isolation level, eachRead the snapshotwillBuild and get the latesttheRead View;Ensure that the data submitted is up to date
  • And in theRRAt the isolation level, it isIn the same transactiontheFirst snapshot readWill createRead View.After theAll snapshot reads areSame Read View, and then the queryIt won't repeatSo one transaction query results at a timeIt's all the same.Make sure you don’t read it twice

Solve illusory problems

  • Read the snapshot: control through MVCC, without lock. Add, delete, change, check and other operations according to the “grammar” specified in MVCC to avoid unreal reading.
  • The current reading: Next key lock (row lock +gap lock) to solve the problem.

InnoDB snapshot read differences in RC and RR levels

  • Under the RR level of a transaction on a record of the first snapshot Read will create a snapshot and Read the View, the current system of active other transaction record, then the call snapshot to Read, or use the same Read View, so as long as the current transaction before other transaction commit updated snapshot used to Read, Subsequent snapshot reads use the same Read View, so subsequent changes are invisible;
  • That is, when a RR snapshot Read generates a Read View, the Read View records snapshots of all other active transactions at that time. The changes made to these transactions are not visible to the current transaction. Changes made by transactions created before the Read View are visible
  • In RC level transactions, each snapshot Read creates a new snapshot and Read View, which is why we see updates committed by other transactions in RC level transactions

Reference: dev.mysql.com/doc/refman/… Juejin. Cn/book / 684473… Segmentfault.com/a/119000002…