【讨论中】求高手指点，MyISAM引擎下的读锁和写锁问题

caibird1984

有一张为了提高查询速度而创建的冗余数据表，保存了不少从整个数据库中通过各种连接、筛选、聚合操作获得的结果，目前重新生成一次数据大概需要2-3分钟的样子。既然是冗余数据，自然会有数据更新的问题。结合之前使用SQL SERVER的经验，我认为可以使用MySQL的定时程序（event scheduler）定时把冗余数据全都更新一次就好了。


根据以往的经验，在生成过程中可能会出现数据的空白，因为这个时候旧的数据被删除，而新的数据还没有填充完毕，于是想到了在生成数据的时候使用排他锁让其他对冗余数据表的查询请求等待来避免出现这种错误。不过编写完更新数据的存储过程编译时发现MySQL不允许在存储过程中使用锁，而MyISAM引擎本身也不支持事务等类似的操作。尝试在调用存储过程之前将冗余数据表加写锁，但是调用存储过程时提示我另外一个用于查询的数据表尚未加锁。

现在我这里想到的方法有这么两种：

1、考虑到整个存储过程执行时耗时最多的部分在于数据生成，因此在存储过程中建立与冗余数据表结构相同的临时表，将之前对冗余数据表的写入操作修改为先写临时表，数据生成完毕后再直接将冗余表的数据清除，从临时表中直接把数据导入到冗余表中。这样做的初衷是缩短可能出现空白数据的时间，尽量减少对用户使用带来的影响。这个是目前采用的方法，不过这样做没有解决根本问题

2、将冗余数据表建成结构相同的两张表，每次更新数据的时候先更新备份表，之后两张数据表交换表名。这样做的问题是出现了更多没啥实际用途的数据表，而且对于其他开发人员来说会比较费解。另外目前的数据库帐号没有对表进行重命名的权限，因此还是放弃了这种方法。

本人是实在没有其他比较好的方法解决上面的问题了，所以想请教各位高手有没有遇到过类似的场景，用了什么解决办法。感谢

cenalulu

方法二是一种很好的思路，也是解决大操作对用户透明的通用方法。
权限问题应该不难解决吧
至于多出没有实际用途的表，应该也不多吧，我理解只需要real，bak，temp 三张表之间不断轮换即可，只要表名起的得当不会误导开发

caibird1984

cenalulu 发表于 2012-06-26 17:52
方法二是一种很好的思路，也是解决大操作对用户透明的通用方法。
权限问题应该不难解决吧
至于多 ...

其实您说的对，权限问题“本来”不是啥大问题。不过小弟也有难处哇，比如我和上级建议使用两张结构一样的表的时候老大问我“你新建的这张表，然后再重命名，如果Hibernate访问出错怎么办BALABALABALABALA“，所以您懂的

目前我也在各种争取，比如争取把存储引擎切换到InnoDB，比如争取个具有ALTER权限的帐号来做方案二的事情，等等。不过感觉不会那么顺利，老板是个倔脾气，而且小弟也还指望着他发工资好买米下锅不是。我现在正在写方法二的DEMO程序，打算过两天这件事儿冷却下之后再和他讨论下。不过我也希望能有其他方案解决这个问题，毕竟现在缓存啥的全都没有，如果不能在数据库一级想办法，那就只有做份儿数据缓存放到程序里了

cenalulu

其实你们老大的问题有些多虑。
rename table a to b, c to a 是在一个原子操作里完成的，期间不会有访问不成功的情况，只会有短暂的锁等待。

action08

学习一下，

ruochen

cenalulu 发表于 2012-06-27 09:20
其实你们老大的问题有些多虑。
rename table a to b, c to a 是在一个原子操作里完成的，期间不会有访问不 ...

lulu回答很给力