The Skip Locked feature in Postgres 9.5
Our topic this episode is the new skip locked feature in PostgreSQL 9.5. This episode is brought to you by Hashrocket, a consultancy with expertise in PostgreSQL - visit us at https://hashrocket.com and www.pgcasts.com
Transcript:
The most obvious use for `skip locked` is multiple workers consuming jobs from
a single source. So our example will be a simple job queue.
We will create simple `jobs` table with `id` and `payload` columns.
```sql
create table jobs(
id serial primary key,
payload json not null
);
```
Now let's add a few jobs.
```sql
insert into jobs(payload) values
('{"type": "send_welcome", "to": "[email protected]"}'),
('{"type": "send_password_reset", "to": "[email protected]"}'),
('{"type": "send_welcome_email", "to": "[email protected]"}'),
('{"type": "send_welcome_email", "to": "[email protected]"}'),
('{"type": "send_password_reset", "to": "[email protected]"}');
```
Let's take a look at the table.
```sql
table jobs;
```
A worker would typically do the following. First, start a transaction.
```sql
begin;
```
PostgreSQL's row locking functionality only works inside of a transaction.
Next, select a row with a `for update` clause.
```sql
select * from jobs limit 1 for update;
```
Now the worker application would execute the job which in this case is to send
an email. Once completed, the job is deleted.
```sql
delete from jobs where id=1;
```
And finally, commit the transaction.
```sql
commit;
```
This works fine for a single worker. But happens if we have multiple workers?
```sql
-- Run in two connections at the same time
begin;
select * from jobs limit 1 for update;
```
The second worker is blocked trying to read the locked row.
Only when the first worker completes can the second begin.
```sql
-- In the first connection
delete from jobs where id=2;
commit;
```
With this style of row locking our workers are serialized, and there isn't any
point to having more than one worker.
```sql
-- In the second connection
delete from jobs where id=3;
commit;
```
`skip locked` is a new feature in Postgres 9.5 that solves this issue. It
purposely bypasses the isolation between transactions that Postgres normally
provides and skips rows that other transactions have locked.
Let's try this again.
```sql
-- Run in two connections at the same time
begin;
select * from jobs limit 1 for update skip locked;
```
Both connections were able to select and lock different rows. Now we can have
multiple workers consuming from the same job queue at the same time.
Thanks for watching.
Watch video The Skip Locked feature in Postgres 9.5 online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 28 June 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 4,227 once and liked it 89 people.