触发器的教训

触发器是高级dbms的特性之一,可以在数据insert、delete或者update的时候,自动的进行处理,比如根据变化的数据内容对其他数据进行适应性调整。但今天我却因为这个遇到了一点小麻烦。

我的数据库是sybase 11.9.2,这个版本不支持ALTER TABLE tbl_name MODIFY col_name datatype语法,所以只能采取变通的方式,比如要把col从decimal(8,2)更改为decimal(14,2)类型:

  1. 新建colA,类型为decimal(14,2)
  2. UPDATE table SET colA=col
  3. ALTER table DROP col
  4. sp_rename “table.colA”, col

但过程中,接二连三的遇到问题,首先进行SET colA=col的时候,由于表含有一个非主键索引,所以很慢,这个只能忍,下次记得先删除索引,好在数据不多。

然后是锁类型,为了提高并发性我更改成了DATAROWS,而这种锁类型下是不允许DROP column的,所以要变更回ALLPAGES,DROP列之后再变回来。

然后是sp_rename存储过程出了莫名其妙的错误(忘记具体内容了,反正不能用),所以上面的第4步变成了再新建列col,然后SET col=colA把值再写回来,然后再删除colA。

最后,也是最致命的问题,就是这个表上还有update和delete的触发器,我太大意了,以至于在第二次UPDATE的时候才发现,但这时已经晚了,整表的UPDATE,还有其它的一些实时处理,让整个数据库系统几乎瘫痪,虽然进程不多,但由于表之间的关联和sybase的加锁机制,很多进程都成了send sleep或者sleeping状态,再过一会儿,日志满了,又多了一些等待日志空间释放的sleep进程。这个时候,drop trigger操作已经无法执行了,一方面是drop trigger和这个表的其它进程锁冲突,另一方面是当事务正在进行的过程中无法truncate日志。最后,只能中止客户端操作,并重启数据库服务。

真正的噩梦才刚刚开始,drop trigger之后,所有update操作都完成了,col类型更改的任务完成了,再重新把trigger建上,但很快发现,只要有针对这个表的update操作,即使操作的对象集为空,数据库服务器也立刻陷入一种死循环状态,update操作永远执行不完,并且进程无法kill,用户越连越多,最后不仅日志很快又满了,而且用户连接数也用完了。找了一下午原因,发现还是drop掉这个update的trigger之后就没问题了,猜测可能是由于某种客户端操作的中止,使这个表的为触发器预留的inserted和deleted数据缓存滞留在系统中,并随着trigger的触发再次被处理,进入死循环,重启服务之后,依然存在,然后再被触发,再造成死循环。

最终,新建了一个同结构的表,把数据全部导过去,然后重建索引、触发器,最后再删除旧表,把新表的名称改成现在用的这个,问题终于解决了。同时也从侧面证实了我的猜想,问题就出在这个表上。按说sybase应该不会出现这样的问题的。

总结一下,再更改字段类型的时候,应该按照这样的顺序操作:

  1. 删除触发器和索引
  2. 添加新字段colA
  3. col的值赋给colA
  4. 更改锁类型为ALLPAGES
  5. 删除字段col
  6. 更改锁类型为DATAROWS
  7. 添加新字段col
  8. colA的值再赋给col
  9. 更改锁类型为ALLPAGES
  10. 删除字段colA
  11. 更改锁类型为DATAROWS
  12. 重建索引和触发器

重复更改锁类型的原因是在ALLPAGES锁类型的情况下,进行update的操作比较慢,影响其它同时进行着的操作。并且在高版本的sybase,比如ASE 15下,就不用这么麻烦了,可以使用”ALTER TABLE tbl_name MODIFY col_name datatype”这样的SQL来直接实现。

2 thoughts on “触发器的教训”

  1. 真正的噩梦才刚刚开始,drop trigger之后,所有update操作都完成了,col类型更改的任务完成了,再重新把 trigger建上,但很快发现,只要有针对这个表的update操作,

    我想你要是将触发器删除之后,再重新建一次应该不会出现你的这个问题了。

    1. 上面已经说了,重建触发器故障依旧,所以不得已才重建表的,或许是偶然吧。

      触发器最好少用的好,虽然很方便,但出问题之后不好分析。

Leave a Reply

Your email address will not be published. Required fields are marked *