postgresql 中的锁表 [英] Locking table in postgresql

问题描述

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

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

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

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'.

我为此使用事务，开始时插入函数使用BEGIN，获取行数，如果行数为0，则插入新行，处理完成后，提交更改.

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 COMMITS the changes.

问题在于，如果用户同时提交了具有相同 title 的 2 个游戏，则可能会插入两次，因为我只是将行数获取到 chk对于重复记录，每个事务都会相互隔离

The problem is that, there are chances that 2 games with the same title if 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 isolated from each other

在获取行数时，我想到了锁定表:

I thought of locking the tables when getting the row count as:

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

这也会锁定表进行读取(这意味着另一个事务必须等待，直到当前事务成功完成).这将解决问题，这正是我所怀疑的.有没有更好的方法来解决这个问题(避免重复的 games 具有相同的 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 games with the same title)

推荐答案

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

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

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

I do not agree with the constraint approach entirely. You should have 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.

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

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.

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