Mysql transaction,

Mysql transaction,

1. What is a transaction?
A transaction can be considered as a business logic Processing Unit. This unit is either executed or not executed;
2 ACID principles:
(1) Automicity)
(2) Consistency)
Account A has 3000 RMB, Account B is 2000 faster,
-- A -- 500 -- "B
The sum of account A and Account B is 5000.
Before and after a transaction is executed, the total state should be consistent.
(3) Isolation)
Account A starts from account 3000 -- 500 --> account B 2500
Update (1) transfer transactions
Sum between select sun () A and B
(4) Durability)
Ensure durability:
1> before transactions:
Data is written to the disk before the transaction. What if I want to recall it? Recall data from disk? Slow speed? Very painful? What should I do?
2> complete with the transaction log
Transaction logs also exist on disks. What is the difference between them and data files on disks?
Difference: transactions generate sequential I/O, while disk data files are random I/O transaction logs. Each operation is written to a continuous block on the disk in sequence, this is not the case with our data files.
(1) Transaction Log: sequential IO
(2) Data Files: Random I/O
Write the transaction into the transaction log. After a while, the transaction background or related processes will re-launch the data in the transaction to the data file, so that we can ensure the durability of the data operated by the transaction.

3. Transaction status.
(1) It is either an active transaction.
The transaction is being executed.
(2) partially commit transactions
We know that a transaction is either executed or not executed. What is partial commit?
That is, the transaction has been executed, some have been written to the disk, the other part is being executed, and the last execution statement is being written. We call it part commit.
(3) failed transactions
The transaction is committed normally, but it does not reach the goal. It is called a failed transaction.
(4) suspended transactions
To abort a job, it is called "Abort.
(5) committed transactions
For a Healthy committed transaction, our transaction must exist in one of the five States. Once committed, the transaction cannot be undone.

4. Transaction concurrency and Scheduling
Transaction concurrency:
1> improve throughput and resource utilization
2> reduce wait time
To reduce the impact between transactions, we need to isolate them. What if they are isolated? This depends on Transaction Scheduling. Any database system itself has many policies in this complex scheduling method to complete scheduling, generally, to ensure the isolation and atomicity of transactions, they need to be completed through transaction scheduling. There are two general scheduling policies:

Transaction Scheduling:
1> recoverable Scheduling
Generally, when two transactions are scheduled between each other, the cross execution of any two transactions will not change the status of another transaction. We call it "resumable scheduling ".

2> stepless Scheduling
The combined recovery scheduling means that after the transaction ends, there is no impact between them, especially when the transaction is rolled back, it will still be affected when the transaction is rolled back, so in order to avoid the impact during rollback, the so-called stepless scheduling can also be implemented.

5. transaction isolation
We know that transaction scheduling involves multiple operations, and these operations can be performed in parallel. The existence of transaction scheduling is to better schedule the order so that these cross-execution tasks do not affect each other too much, in order to reduce the impact between transactions, we have a so-called isolation level, with four transaction isolation levels. Which four?
1> read uncommitted
At the isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications, because its performance is no better than other levels, reading uncommitted data has also become: Dirty Read (Dirty Read)
2> read and submit read committed
The default isolation level of most databases (but this is not the default level of Mysql), which satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions;
3> repeatable read
This is the default transaction isolation level of Mysql. It ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. However, theoretically, this leads to another problem: phantom read: a Phantom read refers to a row in which a user reads data in a certain range. Another transaction inserts a new row in this range, when the user reads data in this range again, a new Phantom line is found"
The Innodb and Falcon engines solve this problem through the Multi-version concurrency control (MVCC) mechanism.

4> serializable
This is the highest isolation mechanism. It forces transaction sorting to enable conflicting steps, thus solving the phantom read problem, Simply put: it adds a shared lock to each read row. At this level, it may cause a large number of timeout and lock competition.

Generally, to achieve the isolation level of concurrency control, we need to implement it based on concurrency control, while our concurrency control is real-time.
Technologies rely on the following technologies:
(1). Lock. Read lock, write lock, exclusive lock, shared lock.
(2). timestamp. the start time and execution time of each transaction must be recorded.
(3). Multi-version and Snapshot isolation

Additional extensions:
The starvation status of the transaction (Lock hunger ):
When a transaction applies for a type of lock, in order to achieve concurrency control, it is willing to need this lock, but due to the transaction scheduling results, it will never get this lock, the result is:
Lock hunger. There is also a lock called a deadlock.

6. Start transactions and roll back

Generally, the following operations start a transaction:
1> Start the transaction
Start transcation
2> transaction rollback
Rollback
3> commit transactions
Commit
6.1 actual transaction rollback operations:
1> Start a transaction
Start transaction;
2> Delete the information in one of the student tables.
Delete from student where num = '4'
3> if data is restored? Transaction rollback. By mistake.
Rollback;
4> check for verification. 4. Are you back?
Select * from student;
Yes;

6.2 can I perform operations after the transaction is committed during rollback?
1> Start a transaction
Start transaction;
2> Delete the information in one of the student tables.
Delete from student where num = '4'
3> transaction commit
Commit;
4> try rollback again
Rollback
5> Can I restore the data again?
Select * from student;
No:

6.3 Save the savepoint for a transaction
For example, a transaction contains hundreds of operations and 100 operation statements. When I executed 80th statements, I found that 75th errors were incorrect, is rollback required at this time, and the first 80 are all withdrawn? What should we do at this time? Save the point. For example, I save every 10 operations:
----> 10 (save point 1) ------> 20 (save point 2)

1> Start a transaction
Start transaction;
2> Delete the "Cuihua" information in one of the student tables.
Delete from student where name = 'cuihua'
Save a transaction point
Savepoint yya
3> Delete the information of "Simon brag" in one of the student tables.
Delete from student where name = 'ximenchuiniu'
Savepoint yyb
4> Delete the "niu Er" information of one of the student tables.
Delete from student where name = 'niao'
Savepoint yyb
5> start recovery
If you want to restore Simon to snow
Rollback to yya. What is the storage point of yya?
6> check whether the recovery is successful
Select * from student;

6.3 automatic transaction submission
1> query automatic mysql transaction commit
Select @ autocommit;
Mysql> select @ autocommit;
+ -------------- +
| @ Autocommit |
+ -------------- +
| 1 |
+ -------------- +
1 row in set (0.00 sec)

2> modify mysql to submit automatically
Set autocommit = 0;

3> test and verification
Delete from student where name = 'ximenchuniu'
Rollback;
Restore? And we didn't explicitly start a transaction? Transactions support DML statement rollback. DDL is for implicit trial commit, so it is a pity that it is not supported.

Verification read not submitted:
Query isolation level select @ tx_isolation;
Change the isolation level set tx_isolation = 'read _ uncommittd ';
One mysql instance opens two windows, and both parties start a transaction.
Common Operations of window 1 and window 2:
1. select @ tx_isolation;
2. set tx_isolation = 'read _ uncommittd ';

Window 1 operations:
1. start transaction; start a cross transaction
2. update student set age = '70' where d_id = '000000 ';

Window 2 Operations
1. start transaction; start a cross transaction
2. select * from student; you can see that window 1 is changed to window 70, and window 1 is not committed. It is seen by window 2, which is called dirty read.

Verify read submission
One mysql instance opens two windows, and both parties start a transaction.
Common Operations of window 1 and window 2:
1. select @ tx_isolation;
2. set tx_isolation = 'read _ committd ';

Window 1 operations:
1. start transaction; start a cross transaction
2. update student set age = '70' where d_id = '000000 ';
3. commit; only submitted here. The submitted data will be re-viewed in step 3 of window 2.

Window 2 Operations
1. start transaction; start a cross transaction
2. select * from student; no data updated in window 1 is displayed.
3. select * from student; the updated data can be seen only after three steps are submitted in window A to avoid dirty reading.

Repeatable Verification
One mysql instance opens two windows, and both parties start a transaction.
Phantom read:
Definition: the same transaction T1, in two different time periods, for example, 1 2, the number of records obtained by executing the query statement on the same day is different. It is called phantom read.

Common Operations of window 1 and window 2:
1. select @ tx_isolation;
2. set tx_isolation = 'repeatable-read ';

Window 1 Operations
1. start transaction; start a cross transaction
2. update student set age = '60' where num = '10 ';
3. commit;

Window 2 Operations
1. start transaction; start a cross transaction
2. select * from student; no data updated in window 1 is displayed.
3. select * from student; 1 commit; no updated data in window 1 is displayed.
4. commit; 2. The data updated in window 1 is displayed after the window is submitted.

Verify serializable
One mysql instance opens two windows, and both parties start a transaction.
Common Operations of window 1 and window 2:
1. select @ tx_isolation;
2. set tx_isolation = 'serializable ';

Window 1 Operations
1. start transaction; start a cross transaction
2. update student set age = '60' where num = '10'; cannot be executed. It can only be executed after window 2 2 is submitted!

Window 2 Operations
1. start transaction; start a cross transaction
2. commit; Submitted here. update in window 1 can be executed successfully. This is serializable!
Cause:
When two transactions simultaneously modify the same data, one party must first commit. Otherwise, the read/write operations are blocked.