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.