Oracle 与 MySQL 的差异分析（9）：事务

1 自动提交

1.1Oracle

默认不会自动提交，需要显式的提交或回滚。如果断开连接时有未提交事务，客户端工具一般可以配置自动提交或回滚。

1.2 MySQL

InnoDB支持事务，默认是自动提交的。

关闭自动提交的方法：

（1）会话级关闭：

set autocommit = 0; // 1表示开启

检查是否关闭：

show variables like “autocommit”;

结果：autocommit OFF

（2）全局关闭：

set global autocommit = 0 或者把 autocommit=0 加到参数文件中。

注：在开启自动提交情况下，可以用starttransaction; 开启一个事务，然后就不会自动提交了。

2 锁级别

2.1Oracle

DML语句产生行锁，锁信息保存在数据块上，只有被更新的数据会被锁定。

2.2 MySQL

不同引擎使用不同的锁级别，InnoDB默认也是行锁，但是它锁定的是索引条目，这一点与Oracle显著不同，如果没有通过索引来更新数据的话，那么就会锁定整个表。需要注意：

（1）虽然查询列上有索引，但是优化器仍然可能选择全表扫描，由于没有通过索引访问数据，此时仍然是表锁（锁定所有数据）。

（2）通过A索引查出的数据，虽然被B条件过滤掉了，但是该数据仍然会被锁定。比如，userid = 1and username = ‘a’ 如果是走了userid的索引，虽然没有更新username = ‘b’的数据，但是试图通过userid = 1and username = ‘b’ 更新数据时也会被阻塞。

（3）如果通过userid > 1 来更新数据，并且走userid的索引，那么会锁定所有userid >1 的数据，包括未插入的数据，所以此时insert一条userid = 100 的数据时也会被阻塞。这一点与Oracle很不同，Oracle的insert语句一般不会被阻塞。

3 事务隔离级别

SQL标准中定义的隔离级别，隔离级别越高，并发性越差。

（1）Read Uncommitted：

B会话可以看到A会话未提交事务修改的数据（脏数据）。

（2）Read Commited:

A会话的事务提交了，B会话就可以看到修改的数据。

可以避免脏读（读到未提交的数据）。

（3）Repeatable：

A会话的一个事务内查询同一个表的数据不会变化，即使B会话修改了数据并且已经提交。

可以避免不可重复读问题（同一个SQL第二次发现数据已变化）。

（4）Serializable：

事务好像是串行的，它是在每个读的数据上加上共享锁。select相当于select ... from update。

可以避免幻影读问题（同一个SQL第二次读/写不会看到新的数据）。

3.1Oracle

默认隔离级别是Read Commited，支持Serializable，不过一般不用。

3.2 MySQL

InnoDB默认事务隔离级别是Repeatable，其它几种也支持。

查询事务隔离级别：

select @@tx-isolation;

结果：REPEATABLE-READ

修改方法：

set session|global tx-isolation = ‘serializable’;

4 锁超时时间

4.1Oracle

如果A会话锁定了某条数据，那么B会话试图更新这条数据时会被阻塞，并且会一直等待。

4.2 MySQL

MySQL数据库参数：innodb_lock_wait_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报超时错误。

默认值有些小，建议改大些，代码需要考虑这个特性，锁定数据有失败的风险，需要捕获异常，这一点与Oracle不同。