数据库唯一索引的问题

Question

现在有这样一个场景，比如有个帖子(post_id)，规定一个人(user_id)只能回复一次，可以在表字段post_id和user_id加唯一索引来保证这个逻辑。但是由于处于某种需求，回复需要实现逻辑删除的功能，比如有个active_flag字段，为1时表示正常，0为删除。这样因为唯一索引的原因，导致某人的回复被删除后还是不能回复，所以需要修改唯一索引。请问：基于这样的场景如何设计唯一索引？

Answer 1

在插入前判断active_flag不行？

Answer 2

开始事务SELECT active_flag FROM tt where ..  FOR UPDATE;
UPDATE tt SET active_flag =  1;提交事务

Answer 3

可以使用乐观锁定模式，把old值加全。
update ...... where user_id = old_user_id and post_id = old_post_id and active_flag=old_flag
这样不用显示的去hold锁了。
然后判断affected count

Answer 4

用触发器试下，在INSERT中判断一下active_flag=1 的有几个，=0则正常执行，非0则抛异常。http://blog.csdn.net/acmain_chm/article/details/4380183
MySQL 中如何在触发器里中断记录的插入或更新？
MySQL 不象其它有些数据库可以在触发器中抛出异常来中断当然触发器的执行以阻止相应的SQL语句的执行。在MySQL的目录版本中还无法直接抛出异常。这样我们如何实现呢？ 下面是一种实现的方法。思路就是想办法在触发器中利用一个出错的语句来中断代码的执行。 mysql> create table t_control(id int primary key);Query OK, 0 row...

Answer 5

因为是互联网产品，数据库不可能用触发器存储过程等之类的东东，用索引或者行级锁还算可以接受我想到一个办法，不知道大家觉得这个办法搓不：
增加一个字段，比如叫version好了，create unique index on (post_id, user_id, version)
当新增字段的时候给active_flag和version字段都赋值为1，此时如果有并发的事务插入由于索引的存在，后提交的事务会报错
当逻辑删除这条记录时，version字段给一个随机数，这样不同的已经逻辑删除的记录和正常的记录之间都不会有重复的现场
当把某一个逻辑删除的记录恢复时，把version字段重新赋值为1，如果已经存在post_id,user_id相同并且version为1的记录时会报错，有其他恢复或者新增记录的事务时，也是后提交的事务报错，这样通过索引保证了数据库中每个帖子单个用户只有唯一的回复请帮忙指出不好的地方，或者提供更好的建议

Answer 6

对乐观锁定而言，最终只有一个插入或更新成功的。因为where条件的限制。