问题描述
基于此答案中的查询(请注意/假定GROUP_CONCAT现在也保存在用户定义的变量)，何时和什么InnoDB将锁定在table1上?
Building on the query in this answer (please note/assume that the GROUP_CONCATs are now also held in user defined variables), when and what will InnoDB lock on table1?
我希望它只锁定当前正在更新的table1行，并在下一行开始时释放它.
I'd prefer that it only lock the table1 row that it's currently updating and release it upon starting on the next row.
我还希望当它锁定table2(或其行)时，SELECT s至少能够读取它.
I'd also prefer that when it locks table2 (or its' rows) that SELECTs will at least be able to read it.
要更新的列不是PK，甚至没有索引.
The column being updated is not PK or even indexed.
这是如何实现的，或者已经做到了?
How can this be achieved, or is it already doing that?
这是在TRIGGER中.
非常感谢！
推荐答案
该锁将为整个事务持有(因为该操作是原子的，这意味着所有行都已更新或没有行)，并且您可以t更改(不更改存储引擎).但是，它不会阻止读取(除非您处于SEIALIZABLE隔离级别)，因此将执行SELECT查询，但它们将读取旧值.更新只会阻止SELECT FOR UPDATE和SELECT ... LOCK IN SHARE MODE.
The lock is held for the entire transaction (as the operation is atomic, this means that either all of the rows are updated or no rows) and you can't change that (without changing the storage engine). However it does not block reads (unless you are in SEIALIZABLE isolation level), so SELECT queries will be executed, but they will read the old values. Only SELECT FOR UPDATE and SELECT...LOCK IN SHARE MODE will be blocked by an update.
这篇关于使InnoDB仅锁定当前正在更新的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！