MySQL Truncate与外键限制
从数据库的概念上说,Truncate操作是对于数据表的截断操作,即简单地调用文件系统的截断操作系统调用来实现表的清空操作。在基于MyISAM引擎的MySQL数据库上,Truncate始终如此操作,因此Truncate结果虽然从基本等效于无where限定的Delete语句(除了Truncate会将自增字段值重置为0以外),不过效率却高得多。
但是由于InnoDB存储引擎支持外键,Truncate操作就不是那么简单,因为外键是数据完整性的限定,如果对数据文件进行简单的截断操作,将有可能破坏其外键约束完整性。
因此,在MySQL 5.1(及以下版本)中Truncate一张InnoDB表时,如果该表存在外键约束,那么实际的操作也等同于无where限定的Delete语句,也就是说,MySQL将逐条删除记录,并检查该次删除操作是否可能违反外键操作,如果没有违反约束的情况出现,那么表被顺利清空,自增字段值被重置为0;一旦出现违反外键约束,那么分为两种情况:
- 该外键设定了ON DELETE CASCADE,那么MySQL会同时删除参照表中的相应记录。
- 如果该外键没有设定ON DELETE CASCADE,那么MySQL将停止Truncate操作并报错(Error 1451)。
而在MySQL 5.5中,存在外键约束的InnoDB表,在任何情况下都不允许进行Truncate操作,并报错(Error 1701)。因此,为了向后兼容性的考虑,官方也建议,即使使用MySQL 5.1,也尽量在InnoDB引擎下使用Delete语句代替Truncate语句。
MySQL InnoDB等待锁超时错误
当一个事务在请求某个资源时,它将待这个资源被解锁后继续操作,或者停止等待并返回错误:
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
事务将为一个资源解锁等待多长的时间,取决于innodb_lock_wait_timeout参数的设定,默认设置为50秒,最小允许值为1秒,如果将这个值设定为100000000以上的话,将禁用超时(无论等待多久都不会返回超时错误)。如果因为超时而返回了错误,那么当前SQL语句将会被回滚(在MySQL 5.0.12及以前的版本中,整个事务都将直接被回滚),用户程序可以再次尝试执行这条语句直到成功(通常会等一段时间再重试这个SQL语句),或者回滚整个事务并重新开始。
在InnoDB插件1.0.2之前,更改innodb_lock_wait_timeout选项的唯一方法是修改my.cnf或者my.ini配置文件中的相应选项,并重新启动数据库实例。
在InnoDB插件1.0.2及之后的版本中,还可以在MySQL实例运行时使用SET GLOBAL或者SET SESSION命令来更改innodb_lock_wait_timeout选项。更改GLOBAL设置需要SUPER权限,更改会影响所有在此更改之后新创建的的客户端Session,更改SESSION则只对当前客户端Session有效。
Spring事务管理中@Transactional的propagation参数
关于Spring事务管理中@Transactional的其他配置问题,请参看http://deltamaster.is-programmer.com/posts/28488.html。
本文重点讲一讲propagation参数,propagation配置的就是一个事务的传播性问题。
所谓事务传播性,就是被调用者的事务与调用者的事务之间的关系。举例说明。
//in A.java Class A { @Transactional(propagation=propagation.REQUIRED) public void aMethod { B b = new B(); b.bMethod(); } } //in B.java Class B { @Transactional(propagation=propagation.REQUIRED) public void bMethod { //something } }
在上面这个例子中,传播性被设为了REQUIRED,注意,这是默认值,也即不进行该参数配置等于配置成REQUIRED。
REQUIRED的含义是,支持当前已经存在的事务,如果还没有事务,就创建一个新事务。在上面这个例子中,假设调用aMethod前不存在任何事务,那么执行aMethod时会自动开启一个事务,而由aMethod调用bMethod时,由于事务已经存在,因此会使用已经存在的事务(也就是执行aMethod之前创建的那个事务)。
对于这样的配置,如果bMethod过程中发生异常需要回滚,那么aMethod中所进行的所有数据库操作也将同时被回滚,因为这两个方法使用了同一个事务。
MANDATORY的含义是,支持当前已经存在的事务,如果还没有事务,就抛出一个异常。如果上例中aMethod的传播性配置为MANDATORY,我们就无法在没有事务的情况下调用aMethod,因此,传播性为MANDATORY的方法必定是一个其他事务的子事务,当逻辑上独立存在没有意义或者可能违反数据、事务完整性的时候,就可以考虑设置这样的传播性设置。
NESTED的含义是,在当前事务中创建一个嵌套事务,如果还没有事务,那么就简单地创建一个新事务。
REQUIRES_NEW的含义是,挂起当前事务,创建一个新事务,如果还没有事务,就简单地创建一个新事务。
请注意以上两者的区别,大多数情况下一上两种传播性行为是类似的,不过在事务回滚的问题上,以上两者有很大的区别。
首先,REQUIRES_NEW会创建一个与原事务无关的新事务,尽管是由一个事务调用了另一个事务,但却没有父子关系。
如果bMethod的传播性是REQUIRES_NEW,而抛出了一个异常,则bMethod一定会被回滚,而如果aMethod捕获并处理了这个bMethod抛出的异常,那么aMethod仍有可能成功提交。当然,如果aMethod没有处理这个异常,那么aMethod也会被回滚。
如果aMethod在bMethod完成后出现了异常,那么bMethod已经提交而无法回滚,只有aMethod被回滚了。
而对于NESTED,虽然也会创建一个新事务,但是这个事务与调用者是有父子关系的相互依存的。
如果bMethod的传播性是NESTED,而抛出了一个异常,事务的回滚行为与REQUIRES_NEW是一致的。
但是如果aMethod在bMethod完成后出现了异常,bMethod同样也会被回滚。因为事实上,EJB中没有对于NESTED传播性的类似实现,NESTED并不是真正启动了一个事务,而是开启了一个新的savepoint。
NEVER的含义很简单,就是强制要求不在事务中运行,如果当前存在一个事务,则抛出异常,因此如果bMethod传播性是NEVER,则一定抛出异常。
NOT_SUPPORTED的含义是,强制不在事务中运行,如果当前存在一个事务,则挂起该事务。
SUPPORTS的含义是,支持当前事务,如果没有事务那么就不在事务中运行。SUPPORTS传播性的逻辑含义比较模糊,因此一般是不推荐使用的。
Spring事务管理中@Transactional的参数配置
Spring作为低侵入的Java EE框架之一,能够很好地与其他框架进行整合,其中Spring与Hibernate的整合实现的事务管理是常用的一种功能。
所谓事务,就必须具备ACID特性,即原子性、一致性、隔离性和持久性,在Hibernate的实现中,需要我们编写代码来完成事务的控制工作。
public static void main(String[] args) { // Configuration cfg = new Configuration(); // cfg.configure(); // SessionFactory sf = cfg.buildSessionFactory(); //因为在HibernateUtil类中已经有一部分封装工作,所以以上三行注释掉了。 Session s = null; Transaction tx = null; try { Class.forName("com.HibernateUtil"); s = HibernateUtil.getSession(); tx = s.beginTransaction(); //这里开启了事务 //事务中所需的一系列数据库操作。 tx.commit(); } catch (HibernateException e) { //如果出现异常,且事务已经开启,则需要回滚。 if (tx != null) tx.rollback(); throw e; } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { //无论如何都需要关闭Session。 if (s != null) s.close(); } //sf.close(); System.out.println("end"); }
上面的代码大致就是事务控制的一般思路,那么,由于此事务的管理具有一定的共性,我们就更倾向于使用Spring帮助我们来完成事务管理工作,具体配置方式不是本文的重点,大家可以查看其他文章。有以下两点需要重点注意:
- @Transactional注解就代表支持事务管理,如果这个注解在类上,那么表示该注解对于所有该类中的public方法都生效;如果注解出现在方法上,则代表该注解仅对该方法有效,会覆盖先前从类层次继承下来的注解。
- 一般情况下不要将这个注解加到接口和抽象类上,因为注解是不能被继承的。
本文主要讲使用注解方式配置事务管理时@Transactional的各种参数配置问题。
- propagation参数,Propagation类型(枚举),默认值为Propogation.REQUIRED,支持的值有REQUIRED、MANDATORY、NESTED、NEVER、NOT_SUPPORTED、REQUIRE_NEW、SUPPORTS。关于这个问题的详细说明将在以后的文章中展开。
- isolation参数,Isolation类型(枚举),默认值为Isolation.DEFAULT,支持的值有DEFAULT、READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ、SERIALIZABLE。关于这个问题的详细说明将在以后的文章中展开。
- timeout参数,int类型,事务的超时时间,默认值为-1,即不会超时。
- readOnly参数,boolean类型,true表示事务为只读,默认值为false。
- rollbackFor参数,Class<? extends Throwable>[]类型,默认为空数组。
- rollbackForClassName参数,String[]类型,默认为空数组。
- noRollbackFor参数,Class<? extends Throwable>[]类型,默认为空数组。
- noRollbackForClassName参数,String[]类型,默认为空数组。
最后四个参数都与回滚有关,首先,一般不推荐使用rollbackForClassName和noRollbackForClassName两个参数,而用另外两个参数来代替,从参数的类型上就可以看出区别,使用字符串的缺点在于:如果不是用类的完整路径,就可能导致回滚设置对位于不同包中的同名类都生效;且如果类名写错,也无法得到IDE的动态提示。
但是,如果不配置任何与回滚有关的参数,不代表事务不会进行回滚,如果没有配置这四个选项,那么DefaultTransactionAttribute配置将会生效,具体的行为是,抛掷任何unchecked Exception都会触发回滚,当然包括所有的RuntimeException。
审慎而明智地使用SQL触发器
触发器可以说是数据库中的常用工具,不过,这个功能是否被合理利用,也在一定程度上影响了最终成品的各方面性能。
要想把说明触发器的适用范围,就得先说说触发器本身具有哪些特点:
- 各种DBMS实现不同的触发器语法,方言之间的差异很大,表达能力上也不尽相同。
- 触发器是对于一张表的监视,从属于某一张表(一个关系),但是被触发时可以作用于其他表。
- 从逻辑上,触发器对于程序员应当是透明的。
基于触发器的上述特点,并站在一个软件全局的角度来看,触发器应该保持对于开发人员的透明性,也就是说,开发人员应当始终不需要知道某个触发器正在对业务逻辑产生影响。
最好的做法就是,使用触发器时,避免使其影响业务逻辑本身,曾经在教科书上看到这样一个例子:
有一个学生选课情况表,字段有学号、课程号、平时成绩、考试成绩、总评成绩。需要设计一个触发器,当学生的平时成绩或考试成绩修改时,就自动按照平时成绩20%和考试成绩80%的比例计算出总评成绩。
实际上这应该是个反面教材,这个使用情形就是刚才所说的触发器干涉了业务逻辑。那么为什么不要让触发器干涉业务逻辑呢?
开发人员站在整个数据库的外侧,也就是关注到数据库的外模式,而不关心或无权过问概念模式和内模式,触发器属于概念模式范畴(甚至可能是更低的层次),不需要被开发人员所了解。那么,触发器对于业务逻辑的干涉将使开发人员对于结果产生困惑,为什么开发人员在看似不完整的数据操作之后(修改了平时成绩或考试成绩后没有修改总评成绩),却得到了完整的结果。这个说法建立在表“充当”外模式使用的前提下。
另一方面,触发器带来了程序员无法控制和了解的业务逻辑,使得软件测试最终变得困难重重,如果有一个BUG出现在触发器中,那么它可能将最后才能被发现,此时已经带来了高额的测试和调试成本。对于数据库管理员也是如此,由于存储过程和触发器处于DBMS三层模型中的不同位置,所以当检查外模式的存储过程的业务逻辑时,也同样会产生不必要的困惑。更严重的情况是,一旦存储过程与相关的触发器单方面进行了改动,最坏的情况可以导致业务崩溃,因为不同层级之间的高耦合带来难以预料的维护上的灾难。
对于上面这个简单的问题,其实使用视图就可以很容易地解决,总评成绩=平时成绩×20%+考试成绩×80%,这样的简单关系通过视图,也不会显著产生效率问题,因为计算不涉及多表。对于涉及许多表而可能影响效率的情况,可以采用存储过程来包装对于平时成绩或者考试成绩的变更,当然对于支持物化视图的DBMS,也可能是不错的选择。
教材上还有一个例子,也针对上面那张表:
设计一个触发器,当删除一条学生的选课记录时,自动在另一张与此表结构一致的备份表中插入这条被删除的记录。
这个例子可以被认为是正确地使用了触发器,它与业务逻辑无关了,即使这个触发器不存在,业务也可以照常进行下去,因为备份表对于开发人员而言实际上也是透明的,只是基于数据可靠性和系统性能考虑而增加的关系,与触发器搭配使用再恰当不过。
另一个使用触发器的场合是统计信息,例如记录用户登录失败的操作、记录用户的操作习惯等等。
但是记得别再一张表上建立太多的触发器哦,否则这张被无数触发器监视起来的表,一旦需要执行DML操作,性能就严重受到影响了。