CODEWITHSUNDEEP
sqlpostgresql
Akash
Akash

Reputation: 5012

Locking table in postgresql

I have a table named as 'games', which contains a column named as 'title', this column is unique, database used in 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'.

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.

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'

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)

Upvotes: 6

Views: 17034

Answers (2)

Philip Couling
Philip Couling

Reputation: 14923

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

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.

Upvotes: 3

mvp
mvp

Reputation: 116397

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

Instead, you can use one of the following approaches:

  • Define UNIQUE index 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 DELETE trigger that will check your condition, and if it does not hold, it should RAISE error, which will abort offending transaction

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.

Upvotes: 5

Related Questions