MySQL---Database from getting started to Big God series (vi)-transaction processing and transaction isolation (lock mechanism)

MySQL transaction processing simply describes the transaction:

MySQL transaction is mainly used to deal with large-scale and high-complexity data.
For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

A transaction is a contiguous set of database operations, as if it were a single unit of work. In other words, it will never be a complete transaction unless each individual operation within that group is successful. If any operation on the transaction fails, the entire transaction fails.

Transactions can be used to maintain the integrity of the database, to ensure that a batch of SQL statements are either all executed or not executed

Transactions are used to manage multiple insert,update,delete statements

In general, a transaction is required to meet 4 conditions (ACID):
Atomicity (atomicity), consistency (stability), isolation (isolation), durability (reliability)

1. Atomicity of a transaction: a set of transactions, either successful or withdrawn.
2, Stability: There is illegal data (foreign key constraints and the like), transaction recall.
3. Isolation: The transaction runs independently. The result of one transaction affects other transactions, and then other transactions are recalled. The 100% isolation of a transaction requires a sacrifice of speed.
4, Reliability: After the hardware and software crashes, the INNODB data table driver will use the log file to reconstruct the changes. Reliability and high speed cannot be combined, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when to save the transaction to the log.

Start a transaction

start transaction

In MySQL, the transaction begins working and ending with a commit or ROLLBACK statement. A large number of transactions are formed between the start and end statements of the SQL command.

COMMIT & ROLLBACK:
These two keyword commits and rollbacks (undo transactions) are primarily used for MySQL transactions.

When a successful transaction is completed, issuing a commit command should make changes to all participating tables effective.

In the event of a failure, you should issue a rollback command to return each of the tables referenced in the transaction to the previous state.

The transaction behavior that can be controlled is called a autocommit set session variable. If Autocommit is set to 1 (the default), then each SQL statement (either in the transaction or not) is considered to be a complete transaction and is committed by default when it is complete. When Autocommit is set to 0 o'clock, the set autocommit = 0 command is issued, and the subsequent sequence of statements acts like a transaction until an explicit commit statement is made, and there is no active commit.

Setautocommit (false) in Java corresponds to "START TRANSACTION" in MySQL; The function

Description of the SQL code demo:

start transaction;delete from aa where id=‘7‘;update aa set sname=‘aaaa‘ where id=‘5‘;rollback;/*事务回滚-执行失败*//*commit;提交事务-执行成功*/

Description: Starting from "Start transaction" to "Bollback; or commit; ", the middle of the statement is a whole, if you do" Bollback ", then these actions will be rolled back (undone). If "commit" is executed, all execution succeeds.

Java Code Demo:

@Test     Public void Transactiondemo()throwsClassNotFoundException, sqlexception{///1, Load connector (drive) DriverClass.forName ("Com.mysql.jdbc.Driver");//2, Establish connectionString URL ="Jdbc:mysql://127.0.0.1:3306/hncu?useunicode=true&characterencoding=utf-8"; Connection con = drivermanager.getconnection (URL,"Root","1234");///3, Get Statement objectStatement st = Con.createstatement ();//4, the following for Java implementation of transaction processing        Try{Con.setautocommit (false);//Starting with set false, the following is a transactionString sql ="INSERT into AA values (1, ' Zhang San ');"; St.execute (SQL);//Increasesql ="Delete from AA where id = 5"; St.execute (SQL);//Deletesql ="Update AA set Sname= ' rose111 ' where id=6";            St.execute (SQL); Con.commit ();//Submit}Catch(Exception e) {Con.rollback ();//If an exception occurs, we let the transaction roll back}finally{Con.setautocommit (true);//Set back againCon.close (); }    }

Transaction isolation Level (locking):

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.

To query the transaction ISOLATION level:

select @@tx_isolation;

READ UNCOMMITTED (Read UNCOMMITTED content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. READ UNCOMMITTED data, called dirty Read (Dirty read).

start transaction设置事务隔离级别(read-uncommitted)：set session transaction isolation level read uncommitted;2、B执行start transaction，再修改一条记录，3、A查询记录，得到了以为正确的记录4、B回滚。问题：A读到了B没有提交的记录，也就是脏读。 

Read Committed (read submit content)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm.

This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.

Non-repeatable read (nonrepeatable Read):
Any modifications made from the beginning until the commit are not visible to other transactions. Two times the same query might get a different result.

start transaction设置事务隔离级别(read-committed)：set session transaction isolation level read committed;2、B执行start transaction，修改一条记录，查询记录，记录已经修改成功3、A查询记录，结果还是老的记录4、B提交事务5、A再次查询记录，结果是新的记录。问题：两次查询结果不一致，也就是不可重复读问题。

Repeatable Read (Reread)-mysql default transaction ISOLATION LEVEL

It ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data.

Ensures that the results are consistent across multiple reads in the same transaction. But it can cause another phantom-reading problem, when a transaction reads a range record, another transaction is inserted in that range and a new record is created, and a magic line is generated when the transaction reads the range record again.

Test flow: 1, a set repeatable-read, start transaction, query record, result is old record set transaction isolation level (repeatable-read, mysql default):set  Session Transaction isolation  level repeatable read;2, B execution start transaction, modify a record, query the record, record has been modified successfully 3, a query record, the result is the old record 4, B Commit TRANSACTION 5 , a again query records, the results are still old records. Problem: Can read repeatedly, a during a transaction, even if B modifies the data and commits, a reads the old data. can be read repeatedly. Note: There may be a new problem, a in the transaction process, B adds a record, and commits, resulting in a two reads inconsistent, there will be one more record, that is, Phantom read. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism. 

Serializable (Serializable)

This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

start transaction，查询记录，结果是老的记录设置事务隔离级别(serializable，最高级别)：set session transaction isolation level serializable;2、B执行start transaction，修改一条记录，B卡在这里，要等待A完成才行。3、A查询记录，结果还是老的记录，A提交。4、B的修改操作才进行下去。注意：B在等待过程中，会出现lock超时。

Small Knowledge Points:

共享锁：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。排他锁：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

To view the current isolation level:

select @@tx_isolation;

To set the isolation level syntax:

set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

These four isolation levels are implemented with different lock types, which can be problematic if the same data is being read. For example:

Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback (rollback) operation, the latter will read the data is incorrect.

Non-repeatable read (non-repeatable Read): Data inconsistency in two queries for a transaction, which may be the original data that was inserted in the middle of a transaction update during the two query process.

Phantom Read (Phantom Read): In a transaction two times the number of data pens inconsistent, for example, one transaction queried several columns (row) of data, while another transaction at this time inserted a new column of data, the previous transaction in the next query, you will find that there are a few columns of data that it did not previously.

MySQL---Database from getting started to Big God series (vi)-transaction processing and transaction isolation (lock mechanism)