postgresql 中的锁表

Locking table in postgresql

提问by Akash

I have a table named as 'games', which contains a column named as 'title', this column is unique, database used in PostgreSQL

我有一个名为 as 的表'games'，其中包含一个名为 as 的列'title'，该列是唯一的，数据库用于PostgreSQL

I have a user input form that allows him to insert a new 'game'in 'games'table. The function that insert a new game checks if a previously entered 'game'with the same 'title'already exists, for this, I get the count of rows, with the same game 'title'.

我有一个用户输入表单，允许他'game'在'games'表中插入一个新的。插入新游戏的函数检查以前输入'game'的相同游戏是否'title'已经存在，为此，我得到count of rows, 具有相同的游戏'title'。

I use transactions for this, the insert function at the start uses BEGIN, gets the row count, if row count is 0, inserts the new row and after process is completed, it COMMITSthe changes.

我为此使用事务，开始时的插入函数使用BEGIN，获取行数，如果行数为 0，则插入新行，并在过程完成后COMMITS进行更改。

The problem is that, there are chances that 2 games with the same titleif submitted by the user at the same time, would be inserted twice, since I just get the count of rows to chk for duplicate records, and each of the transaction would be isolatedfrom each other

问题是，title如果用户同时提交2 个相同的游戏，有可能会插入两次，因为我只是获取行数以 chk 重复记录，并且每个事务都将是彼此隔离

I thought of locking the tableswhen getting the row count as:

我想在获取行数时锁定表：

LOCK TABLE games IN ACCESS EXCLUSIVE MODE;
SELECT count(id) FROM games WHERE games.title = 'new_game_title' 

Which would lock the table for reading too (which means the other transaction would have to wait, until the current one is completed successfully). This would solve the problem, which is what I suspect. Is there a better way around this (avoiding duplicate gameswith the same title)

这也会锁定表进行读取（这意味着另一个事务必须等待，直到当前事务成功完成）。这将解决问题，这正是我所怀疑的。有没有解决这个（避免重复一个更好的办法games与同title）

采纳答案by Philip Couling

Using the highest transaction isolation(Serializable) you can achieve something similar to your actual question. But be aware that this may fail ERROR: could not serialize access due to concurrent update

使用最高的事务隔离（Serializable），您可以实现与您的实际问题类似的东西。但请注意，这可能会失败ERROR: could not serialize access due to concurrent update

I do not agree with the constraint approach entirely. You shouldhave a constraint to protect data integrity, but relying on the constraint forces you to identify not only what error occurred, but which constraint caused the error. The trouble is not catching the error as some have discussed but identifying what caused the error and providing a human readable reason for the failure. Depending on which language your application is written in, this can be next to impossible. eg: telling the user "Game title [foo] already exists" instead of "game must have a price" for a separate constraint.

我不约束的方式同意完全。您应该有一个约束来保护数据完整性，但依赖该约束迫使您不仅要确定发生了什么错误，还要确定是哪个约束导致了错误。问题不是像某些人讨论的那样捕获错误，而是确定导致错误的原因并提供人类可读的失败原因。根据您的应用程序是用哪种语言编写的，这几乎是不可能的。例如：告诉用户“游戏标题 [foo] 已经存在”而不是“游戏必须有一个价格”作为单独的约束。

There is a single statement alternative to your two stage approach:

有一个单一的语句替代您的两阶段方法：

INSERT INTO games ( [column1], ... )
SELECT [value1], ...
WHERE NOT EXISTS ( SELECT x FROM games as g2 WHERE games.title = g2.title );

I want to be clear with this... this is not an alternative to having a unique constraint(which requires extra data for the index). You must have one to protect your data from corruption.

我想清楚这一点......这不是拥有唯一约束（它需要额外的索引数据）的替代方法。您必须有一个来保护您的数据免受损坏。

回答by mvp

You should NOT need to lock your tables in this situation.

在这种情况下，您不需要锁定您的表。

Instead, you can use one of the following approaches:

相反，您可以使用以下方法之一：

• Define UNIQUEindex for column that really must be unique. In this case, first transaction will succeed, and second will error out.
• Define AFTER INSERT OR UPDATE OR DELETEtrigger that will check your condition, and if it does not hold, it should RAISEerror, which will abort offending transaction

• UNIQUE为确实必须唯一的列定义索引。在这种情况下，第一个事务会成功，第二个会出错。
• 定义AFTER INSERT OR UPDATE OR DELETE触发器来检查你的条件，如果它不成立，它应该RAISE出错，这将中止违规交易

In all these cases, your client code should be ready to properly handle possible failures (like failed transactions) that could be returned by executing your statements.

在所有这些情况下，您的客户端代码应该准备好正确处理可能通过执行您的语句返回的故障（如失败的事务）。