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不同。