July 2015

PostgreSQL 9.5 is just around the corner and many cool new features have been added to this wonderful release. One of the most exciting ones is definitely SKIP LOCKED.

To make sure that concurrent operations don't lead to race conditions, SELECT FOR UPDATE has been supported for many years now and it is essential to many applications. The problem, however, is: If two SELECT FOR UPDATE statements are executed concurrently, it might happen that one has to wait for a long period of time. In case of high-concurrency this can lead to bottlenecks and to subsequent troubles.

In addition to that there is one more thing to consider: Let us assume somebody has locked a line because he wants to book an airline ticket. It makes sense that some other person does not see this seat anymore because it is most likely being booked anyway. Skipping locked rows can therefore make sense from a business point of view.

One more example would be: Give me all trouble tickets, which are still open, but which are not currently being worked on (= SELECT FOR UPDATE).

How it works

Here is a simple demo table containing 4 rows:

test=# CREATE TABLE t_demo AS
              SELECT         *
              FROM            generate_series(1, 4) AS id;
SELECT 4

test=# CREATE TABLE t_demo AS

SELECT *

FROM generate_series(1, 4) AS id;

SELECT 4

The content is not too surprising:

test=# TABLE t_demo;
 id
----
  1
  2
  3
  4
(4 rows)

test=# TABLE t_demo;

----

(4 rows)

Now let us assume that one user locks a row:

test=# BEGIN;
BEGIN
test=# SELECT * FROM t_demo WHERE id = 2 FOR UPDATE;
 id
----
  2
(1 row)

test=# BEGIN;

BEGIN

test=# SELECT * FROM t_demo WHERE id = 2 FOR UPDATE;

----

(1 row)

If somebody else reads all the data but skips locked rows, one row is missing from the result set:

test=# BEGIN;
BEGIN
test=# SELECT * FROM t_demo FOR UPDATE SKIP LOCKED;
 id
----
  1
  3
  4
(3 rows)

test=# BEGIN;

BEGIN

test=# SELECT * FROM t_demo FOR UPDATE SKIP LOCKED;

----

(3 rows)

As you can see, the second row has been excluded because it is already locked. Note that the second user is NOT blocked - it can proceed concurrently. This is highly important because transactions can co-exist nicely.

Read further about PostgreSQL and locks in our blog spot.

When checking out the new features of Sybase 15.7 (yes, from time to time I get around to see what commercial databases are up to), I stumbled over an interesting and yet amusing line: “Fully Recoverable DDL”. The details seem to indicate that not just Sybase is still having a hard time to handle transactional and crash safe DDLs in a proper way.

After 15 years of professional PostgreSQL I actually don't remember a time when PostgreSQL did not have transactional DDLs. The beauty of PostgreSQL is that all major DDLs are fully transactional:

test=# BEGIN;
BEGIN

test=# CREATE TABLE t_test (id int, name text);
CREATE TABLE
test=# d
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | t_test | table | hs
(1 row)

test=# ROLLBACK;
ROLLBACK

test=# d
No relations found.

test=# BEGIN;

BEGIN

test=# CREATE TABLE t_test (id int, name text);

CREATE TABLE

test=# d

List of relations

Schema | Name | Type | Owner

--------+--------+-------+-------

public | t_test | table | hs

(1 row)

test=# ROLLBACK;

ROLLBACK

test=# d

No relations found.

Basically everything people need for their daily work is fully transactional. CREATE TABLE, ALTER TABLE, CREATE INDEX - everything works just like a charm.

The exceptions to the rule ...

However, there are some minor exceptions to the rule. The most prominent thing is most likely CREATE INDEX CONCURRENTLY. The thing is: CREATE INDEX is a single transaction - CREATE INDEX CONCURRENTLY is not - and therefore it cannot be run inside a normal transaction block. The index needs to be visible to other transactions before the build can even begin:

test=# BEGIN;
BEGIN

test=# CREATE INDEX CONCURRENTLY idx_id ON t_test (id);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

test=# BEGIN;

BEGIN

test=# CREATE INDEX CONCURRENTLY idx_id ON t_test (id);

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

But, there are some more corner cases. CREATE DATABASE is one of them:

test=# CREATE DATABASE xy;
ERROR:  CREATE DATABASE cannot run inside a transaction block

1 2	test=# CREATE DATABASE xy; ERROR: CREATE DATABASE cannot run inside a transaction block

Performing a ROLLBACK in case of CREATE DATABASE should really not be a real problem anyway and nobody has ever complained about it.

The same applies to CREATE TABLESPACE:

test=# CREATE TABLESPACE some_name LOCATION '/storage';
ERROR:  CREATE TABLESPACE cannot run inside a transaction block

1 2	test=# CREATE TABLESPACE some_name LOCATION '/storage'; ERROR: CREATE TABLESPACE cannot run inside a transaction block

Of course there are some more commands along this line - this list is not meant to be complete. However, it has to be pointed out that in PostgreSQL at least 90% of all DDLs fully support transaction blocks as expected.

VACUUM: The great princess

What is interesting is that some people asked for a ROLLBACK on VACUUM. To make it clear: This is not possible and clearly makes no sense.

test=# VACUUM;
ERROR:  VACUUM cannot run inside a transaction block

1 2	test=# VACUUM; ERROR: VACUUM cannot run inside a transaction block

VACUUM is here to physically clean up rows - it is really pointless to consider a ROLLBACK here (for countless reasons).

In case you need any assistance, please feel free to contact us.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.