MYSQL 教程:§10, 在InnoDB中使用事务

什么是事务?

       事务是具有原子性得指令。计算机中往往有多个多户，程序，进程，线程访问数据库系统。

比如存钱的表：

create table account

(

  number int not null auto_increment primary key,

  balance float

) type = InnoDB;

 

insert into account (balance) values (0.0);

insert into account (balance) values (1000.0);

insert into account (balance) values (2000.0);

 

转账的部分：

update account set balance = balance - 1000 where number = 2;

update account set balance = balance + 1000 where number = 1;

 

可以用如下方式合做一个查询：

update account as source, account as dest

  set source.balance = source.balance – 1000,

    dest.balance = dest.balance + 1000

  where source.number = 2 and dest.number = 1;

不过可读性不好，可以改用事务：

start transaction;

update account set balance = balance - 1000 where number = 2;

update account set balance = balance + 1000 where number = 1;

commit;

       事务在更新的时候对其他会话是不可见的。不会有其他线程读取到不一致的数据。complete and committed.之后方可见。

       事务可以回滚，比如：

start transaction;

update account set balance = balance - 1000 where number = 2;

update account set balance = balance + 1000 where number = 1;

select balance from account where number = 2;

# select tells us that account #2 has a negative balance!

# we'd better abort

rollback;

 

 

在mysql中使用事务

       START TRANSACTION is和BEGIN or BEGIN WORK的效果是一样的，不过START TRANSACTION符合SQL-99，推荐使用。

 

＊     设置Autocommit模式

默认情况下，mysql把每个查询当作一个事务，

比如：

update account set balance = balance - 1000 where number = 2;

update account set balance = balance + 1000 where number = 1;

等同于：

start transaction;

update account set balance = balance - 1000 where number = 2;

commit;

start transaction;

update account set balance = balance + 1000 where number = 1;

commit;

               开关：set autocommit=0; set autocommit=1; autocommit只对本地会话有效。Autocommit关闭的时候，可以不写START TRANSACTION，但是不要忘记COMMIT。

        其他操作也可能自动COMMIT，比如：Requesting a lock will implicitly commit any outstanding queries.

 

＊     使用Locks

手工加锁和解锁可以部分达到事务的功能。LOCK TABLES锁定所有表。UNLOCK TABLES解锁当前所有锁定的表。

lock tables account write;

select balance from account where number = 2;

update account set balance = 1500 where number = 2;

unlock tables;

需要锁定多表最好写在一起。

lock tables account write, account as a read, othertable low_priority write;

write锁禁止读写，read锁禁止写。write锁也可以设置为low_priority。通常是写锁的优先级高于读锁。low-priority write lock可以改变这点，在比较繁忙的服务器，可能会需要。

如果你的表需要很高的性能，偶尔也使用事务。用非事务的表加上lock会更好。

处理mysql数据文件时也需要使用LOCK TABLES 。

加锁后要尽快释放。reindexing or backing up large files等需要加锁的操作可能需要很长的时间。

 

 

§10.3           InnoDB的事务模型

InnoDB采用了细粒度，行级别的加锁机制。不同事务可以同时运行在同一表，如果它们都是读取或者写入但不是同一行。Uncommitted changes lock锁定的是受影响的行。这些功能有ACID依从等。

 

＊     ACID依从

 

ACID依从包含Atomicity, Consistency, Isolation, and Durability。MyISAM表没有通过ACID依从。

Atomicity，原子性：

Consistency，一致性：数据库从一个有效状态转换到另一个有效状态，没有数据不一致的中间状态。比如不能为用户创建不存在的银行账号；不能删除还有钱的银行账号。

Isolation，独立性：事务在执行过程中不能相互影响。一般用锁来实现。

Durability，持续性：事务执行后，影响是永久的。单个程序比较好实现。但是在复杂的RDBMS中，使用了锁和多版本允许多用户并发访问，并使用cache以提高性能，这就比较麻烦了。而且，在出错的时候，能通过备份和log恢复数据。

InnoDB中，transaction的语法保证原子性。事务和外键保证一致性。独立性是可选的。binary log and repair tools提供了持续性（复制更好）。

 

＊     事务的独立性

InnoDB提供的独立性支持从强到弱如下：Serializable，Repeatable read，Read committed，Read uncommitted。你需要权衡性能和稳健性来采用。

Serializable isolation：写完之后，读才能开始。打开：set transaction isolation level serializable;

repeatable read：默认的事务模式。Mysql使用next key locking, 事务锁定行的时候，next key locking also locks the gaps between rows found in the index.所以2次能读取出同样的值。

read committed： 可能2次读取出不同的值。打开：set transaction isolation level read committed;

read uncommitted：dirty read的状态，没有真正的事务。比如你知道所有要读或写的线程，但是不是两者。打开：set transaction isolation level read uncommitted;

 

比较如下：

Table 10.1. Transaction Isolation Level Characteristics
	Dirty Read	Nonrepeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Not possible	Possible	Possible
Repeatable Read	Not possible	Not possible	Possible (but unlikely)
Serializable	Not possible	Not possible	Not possible

 

§10.4           小结：

A transaction is a related set of SQL queries treated as a single atomic unit. It can be either entirely committed or entirely rolled back.

 

The standard SQL way to express a transaction is

 

 

 

start transaction;

# do work

commit;

 

but there are many equivalent ways to get the same effect.

 

ACID stands for Atomicity, Consistency, Isolation, and Durability. You should understand what these terms mean or other geeks will refuse to talk to you.

 

In order from strongest to weakest, the transaction isolation levels are serializable, repeatable read, read committed, and read uncommitted. The InnoDB default is repeatable read.