MySQL: Transaction (Part I - Basic Concept)

 1. What is transaction? ----> It is a sequence of operations that should be regard as a whole and cannot be split. One operation in the sequence failed, then all the operation should be rollback.

 

    1) Take a common scenario for example:

         1) Money transfer in Bank. A want to transfer 5000$ to B.

         2) So we have to subtract 5000$ from A and subjoin 5000$ to B.

         3) The process of subtract and subjoin should be regarded as a whole.

    2) What if we subtract A but hasn't subjoin B, in this process the Bank has power off?

 

2. Example

# Create a table for example
create table account(
id int,
name varchar(20),
deposit int
) engine=innodb charset=utf8;

# Insert data into table
insert into account values(1, 'zhangsan', 3000);
insert into account values(2, 'lisi', 3000);
select * from account;
+----+----------+---------+
| id | name     | deposit |
+----+----------+---------+
|  1 | zhangsan |    3000 |
|  2 | lisi     |    3000 |
+----+----------+---------+

 

3. Properties of Transaction (ACID)

    1) Atomicity : Atomicity requires that each transaction is "all or nothing"

    2) Consistency: The consistency property ensures that any transaction will bring the database from one valid state to another. 

    3) Isolation: The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e. one after the other.

    4) Durability: Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.

 Translation in chinese:

    1) 原子性：一组操作要么都成功，要么都不成功

    2) 一致性：事务发生前和发生后，数据总量相匹配。参见下边例子。

    3) 隔离性：在一个事务中所有操作都执行完毕之前，其他会话是不可以看到中间改变的过程的。比如可能刚将张三的deposit减掉了100，但尚未给李四的deposit加上100。此时如果从外部看的话张三李四的钱应该是执行之前的数量，而不应该是张三减掉但李四未加上的状态。

    4) 持久性：事务产生的影响是不能够被撤销的，即使事务出现错误，也只能通过补偿性事务来弥补错误。例如，将钱款转入错误的账号，之后可能会再将钱款转回。但是这已经是两个事务了。第二个事务称为“补偿性事务”。

 

4. Example:

alter table account drop deposit;

# Set the type of deposit column to be unsigned tinyint whose range is 0~255
alter table account add deposit tinyint unsigned not null default 200;

# Make sure the change take effects
select * from account;
+----+----------+---------+
| id | name     | deposit |
+----+----------+---------+
|  1 | zhangsan |     200 |
|  2 | lisi     |     200 |
+----+----------+---------+

# Subtract deposit from zhangsan
update account set deposit=deposit-100 where id=1;

# Add deposit to lisi
update account set deposit=deposit+100 where id=2;
ERROR 1264 : Out of range value for column 'deposit' at row 2

# But error occurs because 300 is out of range.
# Take a look at rang check policy in MySQL 
show variables like '%mode%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| innodb_autoinc_lock_mode | 1                                                              |
| innodb_strict_mode       | OFF                                                            |
| slave_exec_mode          | STRICT                                                         |
| sql_mode                 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+----------------------------------------------------------------+
# By default sql_mode is STRICT_TRANS_TABLES. Which will prevent out of range operation. But there is some other policies that enables to truncate the out of range data.
# So if we use such mode, the deposit of lisi would be 255 and not 300. So they sufferd a 45 bucks loss.
# This is called consistency issue.