InnoDB和MyISAM中的MySQL Atomic UPDATE [英] MySQL Atomic UPDATE in InnoDB vs MyISAM

问题描述

无论引擎是什么(例如InnoDB或MyISAM)，此比较和交换"语句是否始终是原子的? :

Is this "compare and swap" statement always atomic regardless of engine (e.g. InnoDB or MyISAM)? :

UPDATE tbl_name SET locked=1 WHERE id=ID AND locked <> 1;

之所以问这个问题，是因为我打算使用此语句进行与事务和非事务数据库表兼容的伪行级锁定.

I ask this because I intend to use this statement to do pseudo row-level locking that is compatible with both transactional and non-transactional database tables.

这是推荐用于MyISAM的方法，但我不确定这是否适用于InnoDB，因为该文档建议改为使用事务.

This is the method that is recommended for MyISAM, but I am uncertain as to whether this works for InnoDB since the documentation suggests using transactions instead.

推荐答案

是. 在InnoDB中，该行将被锁定(使您在id上具有唯一索引，更新将锁定它必须扫描的所有行)，更新并释放该锁定.如果您不在显式事务中/自动提交处于启用状态，则每个语句都在其自己的事务中运行，但它仍然是事务并执行锁定

Yes. In InnoDB, the row will be locked (make you have an unique index on id, the update locks all rows it has to scan), updated and the lock released. If you are not in an explicit transaction / auto-commit is on, each statement is run in its own transaction, but it's still a transaction and lockings are performed

这篇关于InnoDB和MyISAM中的MySQL Atomic UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！