Advisory locks in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to acquire a session-level advisory lock with key 12345.

PostgreSQL

SELECT pg_advisory_lock([1]);

Drag options to blanks, or click blank then click option'

Alock_key

B'12345'

C12345

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the key making it a string.

Passing NULL instead of a key.

The function pg_advisory_lock takes an integer key to acquire a session-level advisory lock. The key should be a number, not a string.

2fill in blank

medium

Complete the code to release the advisory lock with key 12345.

PostgreSQL

SELECT pg_advisory_unlock([1]);

Drag options to blanks, or click blank then click option'

A12345

B'12345'

Clock_id

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using a string instead of a number for the key.

Trying to release a lock with NULL.

The pg_advisory_unlock function releases the advisory lock identified by the integer key. The key must be a number without quotes.

3fill in blank

hard

Fix the error in the code to check if the advisory lock with key 12345 is held by the current session.

PostgreSQL

SELECT pg_try_advisory_lock([1]);

Drag options to blanks, or click blank then click option'

A'12345'

BNULL

Clock_key

D12345

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the key causing a type mismatch.

Passing NULL instead of a key.

The pg_try_advisory_lock function attempts to acquire the lock and returns true if successful. The key must be an integer without quotes.

4fill in blank

hard

Fill both blanks to acquire a transaction-level advisory lock with two 32-bit keys 123 and 456.

PostgreSQL

SELECT pg_advisory_xact_lock([1], [2]);

Drag options to blanks, or click blank then click option'

A123

B456

C'123'

D'456'

Attempts:

3 left

💡 Hint

Common Mistakes

Using string keys instead of integers.

Swapping the order of keys.

The pg_advisory_xact_lock function takes two integer keys to acquire a transaction-level advisory lock. Both keys must be integers without quotes.

5fill in blank

hard

Fill all three blanks to check if the advisory lock with keys 789 and 1011 is currently held by any session.

PostgreSQL

SELECT pg_try_advisory_lock_shared([1], [2]) AS acquired, pg_advisory_unlock_shared([3], [2]);

Drag options to blanks, or click blank then click option'

A789

B1011

C'789'

D'1011'

Attempts:

3 left

💡 Hint

Common Mistakes

Using string keys instead of integers.

Mixing keys between blanks incorrectly.

The functions pg_try_advisory_lock_shared and pg_advisory_unlock_shared use two integer keys to manage shared advisory locks. Keys must be integers without quotes. The first and third blanks use the same key 789.