- 1. The basic concept of affairs
- 1.1 Basic definition
- 1.2 The storage engine in MySQL [Understand]
- 1.3 ACID (acid) attributes of transactions
- Second, the creation of the transaction
- 2.1 Implicit transaction: the transaction does not have obvious opening and closing marks
- 2.2 Explicit transaction: transaction has obvious opening and closing marks
- Step 1: Open the transaction
- Step 2: Write the SQL statement in the transaction (select insert update delete)
- Step 3: End the transaction
- 2.3 Demo
- 2.4 Demonstrate the difference between the processing of delete and truncate by transaction
- 2.5 Demonstrate the use of savepoint
- Three, the isolation level of the transaction (4 types)
- 3.1 Basic concepts
- 3.2 Set the isolation level in MySql
- View isolation level
- Set isolation level
- 3.3 Demonstration legend (to be added)
The concept and characteristics of transactions
Transaction isolation level
Case presentation of affairs
Transaction Control Language
1. The basic concept of affairs
1.1 Basic definition
Simple definition: one or a group of sql statements form an execution unit, this execution unit is either all executed or not executed at all
Complete definition: A transaction consists of one or more SQL statements in a single unit. In this unit, each MySQL statement is interdependent . The entire single unit as an indivisible whole, if a SQL statement in the unit fails or generates an error, the entire unit will be rolled back. All affected data will be returned to the state before the transaction started; if all SQL statements in the unit are executed successfully, the transaction will be executed smoothly.
案例：转账 张三丰 1000 郭襄 1000 update 表 set 张三丰的余额=500 where name='张三丰' 意外 update 表 set 郭襄的余额=1500 where name='郭襄'
1.2 The storage engine in MySQL [Understand]
1. Concept: The data in mysql is stored in files (or memory) using various technologies.
2. Use show engines; to view the storage engines supported by mysql.
3. The most used storage engines in mysql are: innodb, myisam, memory, etc. Among them, innodb supports transactions, while myisam, memory, etc. do not support transactions
1.3 ACID (acid) attributes of transactions
Atomicity means that the transaction is an indivisible unit of work, and the operations in the transaction either all happen or none happen.
The transaction must transform the database from one consistent state to another consistent state
Transaction isolation means that the execution of a transaction cannot be interfered by other transactions, that is, the internal operations and data used by a transaction are isolated from other concurrent transactions, and each transaction executed concurrently cannot interfere with each other.
Persistence means that once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it
Second, the creation of the transaction
2.1 Implicit transaction: the transaction does not have obvious opening and closing marks
Such as insert, update, delete statements
delete from table where id =1;
2.2 Explicit transaction: transaction has obvious opening and closing marks
Prerequisite: You must first set the automatic submission function to be disabled
Step 1: Open the transaction
start transaction; optional
Step 2: Write the SQL statement in the transaction (select insert update delete)
Step 3: End the transaction
commit; commit the transaction
rollback; roll back the transaction
savepoint node name; set savepoint
#1.演示事务的使用步骤 #开启事务 SET autocommit=0; START TRANSACTION; #编写一组事务的语句 UPDATE account SET balance = 1000 WHERE username='张无忌'; UPDATE account SET balance = 1000 WHERE username='赵敏'; #结束事务 ROLLBACK; #commit; SELECT * FROM account;
2.4 Demonstrate the difference between the processing of delete and truncate by transaction
SET autocommit=0; START TRANSACTION; DELETE FROM account; ROLLBACK;
truncate does not support rollback, delete it directly
2.5 Demonstrate the use of savepoint
SET autocommit=0; START TRANSACTION; DELETE FROM account WHERE id=25; SAVEPOINT a;#设置保存点 DELETE FROM account WHERE id=28; ROLLBACK TO a;#回滚到保存点 SELECT * FROM account;
Three, the isolation level of the transaction (4 types)
3.1 Basic concepts
Isolation of database transactions: The database system must have the ability to isolate and run various transactions concurrently, so that they will not affect each other and avoid various concurrency problems.
The degree of isolation between a transaction and other transactions is called the isolation level. The database specifies a variety of transaction isolation levels, and different isolation levels correspond to different levels of interference. The higher the isolation level, the better the data consistency, but the weaker the concurrency.
|Dirty read||Non-repeatable||Phantom reading|
Oracle supports two transaction isolation levels: READ COMMITED, SERIALIZABLE.
Oracle default second isolation level read committed
Mysql supports 4 transaction isolation levels. The default transaction isolation level of Mysql is: REPEATABLE READ
The default third isolation level repeatable read in mysql
3.2 Set the isolation level in MySql
- Every time a mysql program is started, a separate database connection is obtained. Each database connection has a global variable @@tx_isolation, which represents the current transaction isolation level.
View isolation level
Set isolation level
set session|global transaction isolation level 隔离级别;
Set the isolation level of the current mySQL connection:
set transaction isolation level read committed;
Set the global isolation level of the database system:
set global transaction isolation level read committed;
Other database jumps: view and change the default transaction isolation level in mysql, oracle, sql server