CODEWITHSUNDEEP
sqlpostgresqlsql-insert
FrediDoe
FrediDoe

Reputation: 33

PostgreSQL lock INSERTS with specific values possible?

Is it possible to create a lock for a specific value in an INSERT-statement in PostgreSQL?

Lets say, I have this table:

CREATE TABLE IF NOT EXISTS bookings (
bookingID   SERIAL PRIMARY KEY,
tableID     integer REFERENCES tables ON DELETE CASCADE,
startTime   timestamp NOT NULL,
endTime     timestamp NOT NULL,
);

Now if a transaction-block with an INSERT is started, can I create a lock for all other INSERTS with the same tableID and the same date during the transaction-time?

So a second booking for the same tableID has to wait for the first one to finish. And after that can check again, if the INSERT is still possible. So its basically a ROW-lock for a specific value in INSERTS.

The program is written in Java, but I don't want to use a synchronized-block, because of bottleneck.

Thanks for the help

Upvotes: 3

Views: 6263

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 324455

What you want is called predicate locking. It is not directly supported by PostgreSQL, but advisory locking as described by @stas.yaranov is a good way around that. As @EgorRogov points out, if possible you should remove the need for locking entirely by using appropriate constraints.

Another option nobody's mentioned is using SERIALIZABLE transaction isolation in PostgreSQL 9.1 or newer. This uses a concurrency control method quite like optimistic locking, where each transaction proceeds without locking, but one of them might get aborted if it conflicts with another. This means your app has to be prepared to trap serialization failure errors and retry transactions, but it's generally a very efficient and quite simple way to handle things like this - especially in cases where exclusion constraints won't help you.

I advise using exclusion constraints in this particular case if you can do so, since that's exactly what they were designed for. But you can use serializable isolation without schema changes or adding new indexes, and it's a more general solution.

(You can't use SERIALIZABLE for this in 9.0 or older, by the way, as it's not smart enough in those versions)

Upvotes: 4

Egor Rogov
Egor Rogov

Reputation: 5398

The question is, why do you want to block other inserts for this table?

For me it looks like you want to be sure there are no intersecting intervals for the same tableID. Probably you check this in Java code and you don't want other inserts to interfere with the check.

If so, you need no locks at all: use EXCLUDE constraint.

For this, you need:

  1. Change two timestamp fields into one tsrange field.
  2. Install btree_gist extension (it is included in contribs).

Your table will looks like this:

CREATE TABLE IF NOT EXISTS bookings (
  bookingID   SERIAL PRIMARY KEY,
  tableID     integer REFERENCES tables ON DELETE CASCADE,
  during      tsrange NOT NULL,
  EXCLUDE using gist(during with &&, tableID with =)
);

Special GIST index will be created automatically to ensure that there will be no intersecting intervals (&& operator) for identical tableID (= operator).

Some examples:

-- interval for tableID=10
test=# insert into bookings values (1, 10, '[2015-11-17 10:00, 2015-11-17 12:00)');
INSERT 0 1

-- interval for tableID=11
test=# insert into bookings values (2, 11, '[2015-11-17 10:00, 2015-11-17 12:00)');
INSERT 0 1

-- can't create intersecting interval for tableID=10
test=# insert into bookings values (3, 10, '[2015-11-17 11:00, 2015-11-17 13:00)');
ERROR:  conflicting key value violates exclusion constraint "bookings_during_tableid_excl"
DETAIL:  Key (during, tableid)=(["2015-11-17 11:00:00","2015-11-17 13:00:00"), 10) conflicts with existing key (during, tableid)=(["2015-11-17 10:00:00","2015-11-17 12:00:00"), 10).

-- ok to create non-intersecting interval
test=# insert into bookings values (4, 10, '[2015-11-17 12:00, 2015-11-17 13:00)');
INSERT 0 1

Upvotes: 7

stas.yaranov
stas.yaranov

Reputation: 1787

Agree with @a_horse_with_no_name. Sure a synchronized-block would be a better solution.

But if you insist on using database locks then advisory locks (too) could help you.

For example:

begin; -- we will use transaction level lock but there is an option with session level locks
select pg_advisory_xact_lock(123); -- 123 is a tableID you need to lock
insert into bookings(tableID, ...) values(123, ...); -- or/and other queries
commit;

Upvotes: 2

Related Questions