Transaction Control Language: used to handle unexpected interruptions
Things
One or a group of SQL statements form an execution unit, either all of them are executed or none of them are executed.
If the execution fails, an error occurs, and the entire unit will be rolled back. (Undo all operations)
All affected data is returned to the state it was in before the transaction began.
If all SQL statements in the unit are executed successfully, the transaction is executed smoothly.
Attributes that things have: ACID (acid) attributes
1.A Atomicity: Represents that a thing is an indivisible unit of work (the smallest unit), either all of it is executed or none of it is executed.
2.C Consistency: Executing a transaction will switch the data from one consistent state to another consistent state. (accurate, complete and reliable)
3.I isolation type: the execution of one thing is not interfered by other things (isolation level control)
4.D Persistence: Once a thing submits data, it is permanently changed, and subsequent operations and failures will not affect it.
Deletion means deletion. There is no way to undo it, you can only reinsert it.
View the storage engines supported by MySQL
SHOW ENGINES;
| InnoDB | The current server’s default storage engine | Supports things |
| MEMORY | It is also used a lot | Does not support things |
| MyISAM | Version 5.5 The previously used | does not support things |
Creation of things
Implicit things (automatic things)
Things have no obvious start and end marks (automatically start and end automatically)
For example, insert, update, delete statements
show things
Things have obvious beginning and end marks
Sum two implicit statements into one thing
Prerequisite: Automatic submission must be disabled
Disable autocommit: set autocommit=0;
Only valid for the current thing, not permanently closed
Case: Transfer Zhang Sanfeng 1000 Guo Xiang 1000 UPDATE table SET Zhang Sanfeng balance=500 WHERE NAME='Zhang Sanfeng' unexpected interruption UPDATE table SET Guo Xiang balance=1000 WHERE NAME='Guo Xiang'3 Wrong money But update itself is a thing, so automatic submission must be disabled
Show automatic submission status
SHOW VARIABLES LIKE 'autocommit';
autocommit automatically commits
value on means on
1. Steps to control things:
#Step 1: Open things set autocommit=0;#Disable automatic submission. If you write this, things will be enabled by default. start transaction; #optional #Step 2: Write the SQL statement in the transaction (Only insert update delete is supported, and addition, deletion, modification, and search are only useful. Select can be used but it has no meaning) There is no such thing as a thing in the ddl language Statement 1; Statement 2; ... #Step 3: End things commit; Submit things, there are no accidents, and all statements can be executed rollback; rollback things, there is an unexpected rollback, and all statements cannot be executed
MySQL has no way to demonstrate that things are rolled back abnormally and there is no abnormal submission, but jdbc can.
More situations cannot be achieved alone and must be applied jointly
# Demonstrate the usage steps of things
#1.Open things SET autocommit=0; START TRANSACTION; #2. Write a set of statements about things UPDATE account SET balance =500 WHERE username='Zhang Wuji'; UPDATE account SET balance =1500 WHERE username='Zhao Min'; #3. End things COMMIT;#Submit things, change -------------------------------------------------- ---------------------------------- #1. Turn things on SET autocommit=0; START TRANSACTION; #2. Write a set of statements about things UPDATE account SET balance =1000 WHERE username='Zhang Wuji'; UPDATE account SET balance =1000 WHERE username='Zhao Min'; #3. End things ROLLBACK;#Rollback things, undo
Data is stored in memory and not committed to disk files until the end of the transaction.
It is not until the end of the task that you decide whether to commit it to a disk file or cancel it.
2. The difference between delete and truncate when used in things
delete can roll back the undo truncate does not support rollback and undo
#deleteDemo SET autocommit=0; START TRANSACTION; DELETE FROM account; ROLLBACK; SELECT * FROM account; #truncatedemo SET autocommit=0; START TRANSACTION; TRUNCATE TABLE account; ROLLBACK;
3. Demonstrate the use of savepoint
savepoint node name (custom); set savepoint
match
ROLLBACK TO node name; #Roll back to the save point and undo the operations after the save point
#Query data in the table SELECT * FROM account; #1.Open a transaction SET autocommit=0; START TRANSACTION; #2. Delete DELETE FROM account WHERE id=1; savepoint a;#Set savepoint DELETE FROM account WHERE id=3; ROLLBACK TO a;#Roll back to the save point Only deleted: operations 1 and 3 have been undone
4. Concurrent transactions
Multiple things operate on the same data in the database at the same time. If necessary isolation mechanisms are not adopted, various concurrency problems will result
Similar to multi-threaded thread safety, five people go to the same toilet at the same time, so locking must be enabled to achieve thread synchronization and solve the problem.
Dirty read: Read data updated by other things but not submitted
Non-repeatable reading: Reading a thing multiple times will result in different results.
Phantom reading: reading data inserted by other things but not submitted
Before the first thing was updated, there were three pieces of data, and other things had not yet been submitted or added. At this time, the second thing submitted the data, and the update of the first thing affected four rows of data.
Concurrency problems can be solved by designing the isolation level of the database:
The level increases gradually from top to bottom, and the performance decreases gradually from top to bottom.
| Dirty read | Non-repeatable read | Phantom read | |
| read uncommitted | √ | √ | √ |
| read committedread committed | × | √ | √ |
| repeatable read | × | × | √ |
| Serializable | × | × | × |
MySQL default third isolation level: repeatable read
Oracle defaults to the second isolation level: read committed, and also supports the fourth: serializable
View isolation level
Setting the isolation level is only effective for the current connection.
Login is a connection, query is not a connection, it is all under the same connection.
select @@tx_isolation
Set the current isolation level
set session transaction isolation level isolation level;
Set global isolation level
set global transaction isolation level isolation level;