[SQL Database Basics 06] Transaction Control Language TCL-Transaction (Concepts, Features, Isolation Level, Case Demonstration)

Article Directory

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

\1. Atomicity

Atomicity means that the transaction is an indivisible unit of work, and the operations in the transaction either all happen or none happen.

\2. Consistency

The transaction must transform the database from one consistent state to another consistent state

\3. Isolation

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.

\4. Durability

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

set autocommit=0;

Step 1: Open the transaction

set autocommit=0;

start transaction; optional

Step 2: Write the SQL statement in the transaction (select insert update delete)

Statement 1;

Statement 2;

Step 3: End the transaction

commit; commit the transaction

rollback; roll back the transaction

savepoint node name; set savepoint

2.3 Demo

#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.

img
Dirty readNon-repeatablePhantom reading
read uncommitted:
read committed:X
repeatable readXX
serializableXXX

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

select @@tx_isolation;

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

3.3 Demonstration legend (to be added)

img