本文共 2724 字，大约阅读时间需要 9 分钟。

Transaction Control Language (TCL)

Transaction Definition

A transaction is one or more SQL statements that form a single execution unit. It is either entirely executed or entirely not executed.

Example: Transfer Money

• offset 1000
• cardiB 1000

Transaction Creation

Implicit Transaction:

No explicit start and end marks are visible.

E.g., insert, update, delete statements.

Explicit Transaction:

Requires explicit start and end marks, with autocommit disabled.

set autocommit=0; start transaction; (optional)

Transaction Steps

Savepoint

A savepoint is a marker set within a transaction to allow partial rollbacks.

Example: savepoint a;

Rollback to savepoint: rollback to a;

Isolation Levels

• Read Uncommitted: Dirty reads, phantom reads, non-repeatable reads.
• Read Committed: No dirty reads, has phantom reads, non-repeatable reads.
• Repeatable Read: No dirty reads, no phantom reads, but still can have multiple reads of the same data in the same transaction.
• Serializable: No dirty reads, no phantom reads, no non-repeatable reads.

MySQL default: Repeatable Read

Oracle default: Read Committed

Check Isolation Level

select @@tx_isolation;

Change Isolation Level

set session|global transaction isolation level [ISOLATION_LEVEL];

Transaction SQL Examples

• Update with Rollback:

set autocommit=0; start transaction; update account set balance = 1000 where username = 'a'; update account set balance = 1000 where username = 'b'; rollback;

Summary of DDL Commands

• Delete and Truncate in Transactions:
  • delete supports rollback.
  • truncate does not support rollback.

Delete Example (Support Rollback):

set autocommit=0; start transaction; delete from account; rollback;

Truncate Example (No Rollback Support):

set autocommit=0; start transaction; truncate table account; rollback;

ACID Properties

Atomicity

• Ensures all operations in a transaction are atomic.
• Entire transaction is either committed or rolled back.

Consistency

• Ensures database remains in a consistent state before and after the transaction.

Isolation

• Transactions are isolated from each other.
• Data modifications by one transaction are not visible to another until committed.

Durability

• Once committed, data changes are permanent and not affected by database crashes.

Common Commands

• show engines;
• show variables like 'auto_increment';
• delete from table where id=1;

Transaction Usage Steps

set autocommit=0; start transaction;

2. Write Transaction Statements

Example:

• update account set balance = 1000 where username = 'a';
• update account set balance = 1000 where username = 'b';

3. Commit or Rollback

commit; Or rollback;