如何锁定尚不存在的InnoDB行? [英] How do I lock on an InnoDB row that doesn't exist yet?

问题描述

如何保证我可以搜索数据库中是否存在用户名，然后将该用户名作为新行插入数据库中，而在SELECT和INSERT语句之间没有任何拦截?

How can I guarantee that I can search if a username exists in my database, then insert that username into the database as a new row without any intercept between the SELECT and INSERT statements?

几乎就像我在锁定不存在的行.我想用用户名"Foo" 锁定不存在的行，以便现在可以检查它是否存在于数据库中，如果不存在则将其插入数据库中.任何中断.

Almost as if I am locking on a row that doesn't exist. I want to lock on the non-existent row with the username "Foo", so that I can now check if it exists in the database AND insert it into the database if it doesn't already exist without any interruption.

我知道使用LOCK IN SHARE MODE和FOR UPDATE存在，但是据我所知，它仅适用于已经存在的行.我不确定在这种情况下该怎么办.

I know that using LOCK IN SHARE MODE and FOR UPDATE exist but as far as I know, that only works on rows that already exist. I am not sure what to do in this situation.

推荐答案

如果username上有一个索引(如果没有，应该添加一个，最好添加一个UNIQUE)，然后发出SELECT * FROM user_table WHERE username = 'foo' FOR UPDATE;将阻止任何并发事务创建该用户(以及在索引唯一的情况下可能的上一个"和下一个"值).

If there is an index on username (which should be the case, if not, add one, and preferably a UNIQUE one), then issuing a SELECT * FROM user_table WHERE username = 'foo' FOR UPDATE; will prevent any concurrent transaction from creating this user (as well as the "previous" and the "next" possible value in case of a non-unique index).

如果没有找到适合的索引(满足WHERE条件)，则不可能进行有效的记录锁定，并且整个表都将被锁定*.

If no suitable index is found (to meet the WHERE condition), then an efficient record-locking is impossible and the whole table becomes locked*.

此锁将一直保持到发出SELECT ... FOR UPDATE的事务结束.

This lock will be held until the end of the transaction that issued the SELECT ... FOR UPDATE.

有关此主题的一些非常有趣的信息可以在这些文件中找到手册页.

Some very interesting information on this topic can be found in these manual pages.

* 我说高效，因为实际上群集索引可以使用，它将被完全锁定.

* I say efficient, because in fact a record lock is actually a lock on index records. When no suitable index is found, only the default clustered index can be used, and it will be locked in full.

这篇关于如何锁定尚不存在的InnoDB行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！