Phase to recommend

  • (3) Detailed explanation of HashMap
  • (1) Detailed explanation of ArrayList
  • (2) A detailed description of the LinkedList
  • Java Collections Framework
  • Thread pool details
  • Introduction of synchronized
  • ThreadLocal, you got it?
  • Java Exception Mechanism
  • New JDK8 feature Stream
  • Classmate, are you familiar with volatile?
  • MySQL index

The transaction

A transaction is a rigorous series of operations (a collection of multiple database operation statements) in an application, all of which must be completed successfully or all the changes made in each operation will be undone. That is, transactions are atomic, and a series of operations in a transaction either succeed or do none (rollback). For example, when the transfer function is implemented, user A uses update to update the balance of the transferred account when transferring money to user B, and user B uses Update to update the balance of the transferred account when receiving the transfer from User A. These database operation statements constitute A transaction. In MySQL, only databases or tables that use Innodb database engine support transactions.

Properties of transactions (ACID)

The transaction must satisfy four conditions (ACID) :

Atomicity

In a transaction, all or none of the operations are completed successfully, and it is not possible that only part of the operation was successfully performed. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction began. This is guaranteed by the UnDOLog log, which records the log information that needs to be rolled back, and the transaction rollback undoes the operations that have been performed successfully.

Consistency

The integrity of the database is not compromised before and after the transaction begins. If a failure occurs in the running of the database system and some transactions are interrupted before they are complete, some of the changes made to the database by those unfinished transactions have been written to the physical database, then the database is in an incorrect or inconsistent state. Consistency is guaranteed by three other features.

Isolation

When multiple transactions operate on the same record or multiple records in the database, the transactions are executed in isolation and in order to avoid simultaneous operations on the same data (different transactions do not affect each other). Isolation prevents data inconsistency due to cross-execution when multiple transactions are executed concurrently. Guaranteed by MVCC. MySQL has four different isolation levels: Read Uncommitted, Read COMMITTED, repeatable Read, and Serializable.

“Durability”

After a transaction is successfully processed, changes to the data are permanent and are not lost in the event of a system failure. Persistence is guaranteed by redolog. When mysql modifs data, it records a log of data in Redolog. Even if the data is not saved successfully, as long as the log is saved successfully, the data will not be lost.

Four isolation levels for MySQL

The MySQL standard defines four isolation levels to define which changes, both inside and outside a transaction, are visible and which are not. Lower isolation levels generally support higher concurrent processing and have lower overhead.

Read uncommitted

Read uncommitted means that all transactions can see the execution results of other uncommitted transactions. Read uncommitted is the least secure isolation level, and a transaction concurrency problem occurs as dirty reads. If transaction A reads the updated data uncommitted by transaction B and transaction B rolls back, the data read by transaction A is “dirty data” and this process is called “dirty read”.

Read committed

Read committed, read what has been committed, which satisfies the simple definition of isolation: A transaction can only see submitted firms do change, so read submitted will not be “dirty read” (to write data and row-level exclusive lock (other affairs can’t read and write), the result of this process is unable to read, until completion of the transaction to release the exclusive lock, to read the data sharing row-level locks (can read, can’t write), it is unable to write, read Release the shared lock once you have read the line). For example, transaction A reads the same data for several times. Transaction B updates and commits the data during the process of reading the data for several times. As A result, when transaction A reads the same data for several times, the results are inconsistent.

Repeatable read (repeatable read)

Repeatable read is MySQL’s default transaction isolation level, which ensures that multiple instances of the same transaction will see the same rows when reading data concurrently. Repeatable reads avoid “non-repeatable reads” (add row-level exclusive locks to write data (other transactions cannot read or write data) and release at the end of the transaction, and add row-level shared locks to read data (other transactions can only read but not write data) and release at the end of the transaction), while repeatable reads cause concurrent transaction problems such as “magic reads”. Phantom reading refers to the time when a user reads a range of rows and another transaction inserts a new row within the range. When the user reads a range of rows again, a new phantom row is found.

  • InnoDB and Falcon storage engines solve this problem with a mechanism called Multiversion Concurrency Control (MVCC) (MVCC works only at the two isolation levels of read committed and repeatable, Data is stored in a snapshot-like manner when read, so that read locks and write locks do not conflict, and different transaction sessions see their own specific version of data and version chains).

  • Two hidden columns in the clustered index record:

  1. trx_id: used to store the transaction ID of each time a clustered index record is modified.
  2. roll_pointer: Every time a clustered index record is modified, the old version is written to the undo log. The roll_pointer is a pointer that points to the location of the previous version of the clustered index record and can be used to obtain the record information of the previous version. (The undo log for the insert operation does not have this property because it does not have the old version)
  • The difference between read committed and repeatable reads is the policy of the generated ReadView.

  1. When you start a transaction, you create a ReadView, which is an array that is sorted to maintain the ids of the currently active transactions (active transactions are uncommitted transactions). To access the data, obtain the transaction ID in the data (obtaining the record with the largest transaction ID), and then compare it to the readView created at the beginning of the transaction. If it is smaller than the readView (indicating that the transaction has committed), then access the data. If it is larger than or in the middle of the readView (larger than the first element in the readView array means that the transaction occurred after the readView was generated, which in readView means that the transaction has not been committed), then it is not accessible. Get roll_pointer, take the transaction ID of the previous version of the data and recompare it.

  2. A transaction at the read-committed isolation level generates a separate readView at the start of each query, whereas a repeatable read isolation level generates a readView at the first query, and subsequent queries reuse the previous readView.

serialization

Serialization, the highest isolation level, forces the transaction to be serialized by placing a shared lock on each read row. If other transactions attempt to rewrite a table with a shared lock, they are suspended until the row releases the lock (potentially leading to numerous timeouts and lock races). Serialization solves the problem of “phantom reading” in concurrent transactions.

MySQL transaction processing two methods

1, use BEGIN, ROLLBACK, COMMIT to implement

  • BEGIN/Start transactional Starts a transaction
  • ROLLBACK ROLLBACK of transactions
  • COMMIT transaction

MySQL > select * from ‘SET’ where ‘SET’ = ‘autocommit’;

  • SET AUTOCOMMIT=0 Disables automatic commit
  • SET AUTOCOMMIT=1 Enable AUTOCOMMIT

Use the reservation point SAVEPOINT

Savepoint is a method of implementing “subtransactions”, also known as nested transactions, in database transactions. Transactions can be rolled back to savePoint without affecting changes made before savePoint was created, without abandoning the entire transaction. ROLLBACK ROLLBACK can be performed by specifying a SAVEPOINT to ROLLBACK to the desired statement before multiple operations are performed (before the transaction is committed, when performing ROLLBACK, by specifying a SAVEPOINT to ROLLBACK to the specified point).

Operations to roll back transactions (before COMMITTED)

  • 1. Set savepointssavepoint a
  • 2. Cancel transactions after savepoint Arollback to a
  • 3. Cancel all transactionsrollback

Use of MySQL8.0 transactions

  • View the default transaction commit mode of MySQL

SELECT @@autocommit, 1 = AUTOCOMMIT, 0 = manual commit

  • Set the transaction commit mode

SET AUTOCOMMIT = 0 or 1

  • Check the default transaction isolation level of MySQl (repreatable-read)

SELECT @@transaction_isolation

  • Set the transaction isolation level globally

Set Global Transaction Isolation Level Isolation level

  • Sets the isolation level of the current session

Set Session Transaction Isolation Level Isolation level