Mysql InnoDB 表已锁定，但我可以“选择”来自另一个会话。什么给？
在开发一些代码期间，我需要“写锁”InnoDB 表以避免竞争条件并发问题。 “读锁”还不够好，因为某些并行会话将“读取”锁定的表（由其他会话锁定），将获得错误数据，因为一旦锁定会话完成其工作，它所读取的内容可能会消失（删除）。
到目前为止，为什么我需要“写锁”。欢迎对此发表评论，但需要很长时间才能解释为什么（以我的愚见）除了表的完整终端锁定之外我看不到任何其他方法。
现在，对于我的测试，我打开了两个 mysql 命令行会话，两者都使用普通用户（无 root 或类似用户）。在一次会议中我做了： 锁定表 mytable 写入； 结果没问题（很好，0 行受影响...） 在第二个命令行会话中，我连接到同一个数据库并在同一个表上运行简单的 select * 。令我惊讶的是，我得到了完整的答复。 在来自实际 Web 应用程序的更多测试中，我确实注意到，在某些涉及 Web 应用程序（带有持久连接属性的 PHP + PDO）的用例中，命令行或 Web mysql 连接确实会阻塞，直到锁被释放，但我没有确定到底是什么导致了这种（期望的）效果，并且它还涉及不同的环境（PHP + PDO 详细说明以及命令行与 2 个命令行会话）。
我的问题是：为什么？为什么在写锁定表上运行简单的“选择”的第二个命令行会话不会被阻止？
这是否与基于行的 InnoDB 锁的性质有关？如果是这样，这到底有什么关系？ 我如何在 InnoDB 表上实现如此简单的锁。我知道我可以创建一个“信号量”MyIsam 表，除了充当“红绿灯”之外没有其他任何用途，但这将失去数据库级别保护的效果，并将移动所有要在应用程序中完成（或错误完成）的保护等级。
蒂亚！
MySQL 版本是 5.1.54 (Ubuntu 11.04)。
During my development of some code, I needed to 'write lock' an InnoDB table in order to avoid race conditions concurrency problems. 'read lock' is not good enough as some parallel session that will 'read' a locked table (locked by other session) will get false data as what it reads might evaporate (deleted) once the locking session finishes its job.
Thus far as to why I need 'write lock'. Comments are welcome on this but it will simply take long to explain why (to my humble mind) I cannot see any way other than complete terminal lock of the table.
Now, for my tests, I have opened two mysql command line sessions, both with regular user (no root or similar). In one session I did:
lock tables mytable write;
which resulted ok (uery OK, 0 rows affected...)
On the second command line session I connected to same DB and run a simple select * on the same table. To my surprise I got a full response.
In more tests from the actual web application I did notice that on some use cases that involve the web app (PHP + PDO with persistent connections attribute on) a command line or web mysql connectivity did block until the lock was released but I did not identified what exactly caused this (desired) effect, and it involves also different environment (PHP + PDO as detailed and command line vs. 2 command line sessions).
My question is: why? why wouldn't the second command line session, running a simple 'select' on the write-locked table blocked?
Does this has to do with the nature of InnoDB locks which is row-based? If so, how exactly does this relate?
How do I get such a simple lock implemented on an InnoDB table. I know I can create a 'semaphore' MyIsam table with no purpose other than act as a 'traffic light' but that will lose the effect of DB level protection and will move all the protection to be done (or wrongly done) in the app level.
TIA!
MySQL version is 5.1.54 (Ubuntu 11.04).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
虽然InnoDB具有行级锁定，但它还具有多版本并发控制http://en.wikipedia.org/ wiki/Multiversion_concurrency_control，所以这意味着读者不需要被作者阻止。他们只能看到记录的当前版本。 （技术实现上，更新时会就地修改行，并且将写入先前版本以撤消旧事务的空间。）
如果要使写锁阻止读取器，则需要将 SELECT 更改为 FOR UPDATE（即SELECT * FROM my_table WHERE cola = n FOR UPDATE）。
While InnoDB has row level locking, it also has multi-version concurrency control http://en.wikipedia.org/wiki/Multiversion_concurrency_control, so this means that readers don't need to be blocked by writers. They can just see the current version of the record. (Technical implementation, on update the row is modified in place and the previous edition will be written to undo space for older transactions.)
If you want to make the write lock block readers, you need to change the SELECT to be FOR UPDATE (i.e. SELECT * FROM my_table WHERE cola = n FOR UPDATE).