This is the 7th day of my participation in the August Gwen Challenge.


Mysql has recently encountered a problem with Navicat inserting data into the interface. Application can not find the data, at the beginning of the suspect is not the lock table, behind the lock found that there is no lock table. Later, I thought it might be a transaction problem, because I changed the configuration file, I carefully checked

Sure enough, I set autoCOMMIT to 0, which means you must commit tocommit a transaction. So let’s record the entire mysql transaction.

Mysql transaction

A transaction is the smallest unit of work of a database operation. It is a series of operations performed as a single logical unit of work. These operations are submitted to the system as a whole and either all or none of them are executed. A transaction is an indivisible set of operations

ACID properties of transactions

  • Atomicity atomic

    Like the definition of a transaction, either executed together or not executed at all, is the smallest unit of operation; If any of these operations fail, the previous operations will be cancelled.

  • Consistency Consistency

    Consistency refers to the fact that the transaction operation can still satisfy the constraint after the execution of the transaction through the characteristics of AID. For example, zhang SAN’s account has 90 yuan, and 100 yuan should be transferred to Li Si. The database constraint balance cannot be less than 0, so the transaction must not be successfully executed, because the constraint is not met.

  • The Isolation Isolation,

    Isolation has two main characteristics

    1. The intermediate (possibly inconsistent) state of the data during a transaction execution should not be exposed to all other transactions.
    2. Two concurrent transactions should not operate on the same item of data. Database management systems typically implement this feature using locks.
  • Durability persistence

    The end result of a completed transaction should be persistent.

Implicit and explicit transactions

Autocommit = 1; autocommit = 1; autocommit = 1

For DQL and DML statements such as SELECT, UPDATE, DELETE, and INSERT, mysql automatically commits the transaction. If the transaction is closed, you need to manually commit or roll back the transaction to complete the operation.

Displaying transactions means setting autoCOMMIT = 0. In a transaction, there must be a distinct open or close label

[START TRANSACTION] # optional statement [DELETE | UPDATE | INSERT | SELECT] # DML, DQL operation [COMMIT | ROLLBACK]; Commit or rollbackCopy the code

There is also the use of rollback points in explicit transactions

START TRANSACTION; [DELETE | UPDATE | INSERT | SELECT]; SAVEPOINT A; # set the rollback point, and the variable is called a [DELETE | UPDATE | INSERT | SELECT]; ROLLBACK TO a; # ROLLBACK is used with ROLLBACK TOCopy the code

Operations before the rollback point are committed, and operations after the rollback point are rolled back

Transaction isolation level

There are four isolation levels for transactions, increasing from left to right

Read-uncommitted READ- > read-committed READ- >REPEATABLE-READ Default REPEATABLE-READ default SERIALIZABLECopy the code

Transaction concurrency issues addressed by different isolation levels

Isolation level/Problem resolution Dirty read Unrepeatable read Phantom read
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

1. READ UNCOMMITTED has the lowest isolation, including dirty reads, unrepeatable reads, and magic reads.

2. The READ COMMITTED level avoids dirty reads

Dirty read refers to the two transactions T1 and T2, after T1 reads a field that has been updated by T2 but has not been committed, if T2 rolls back at this time, the contents read by T1 will be temporary and invalid

3. REPEATABLE-READ Avoid unrepeatable reading

Non-repeatable reads refer to two transactions, T1 and T2, where T1 reads a field, T2 updates the field and commits it, and T1 extracts the same field again with different values.

4. SERIALIZABLE avoids phantom reading

Phantom reading is when for two transactions T1 and T2, T1 reads a field, then T2 inserts a new field and commits it, T1 retrieves it again, and the results are inconsistent.

Note: The main difference between the unrepeatable read and the phantom read is that the unrepeatable read applies to the situation that a certain record is updated, resulting in unequal read results for the same record. A phantom read is a situation where new data is inserted, causing the entire result of two queries to be inconsistent *

The isolation level implementation is based on the mysql storage engine internal lock implementation, currently only InnoDB supports transactions, we will talk about InnoDB locks later