Table Lock Issues in PostgreSQL

Recently we need to heavy lifting some datasets from AWS Redshift to AWS Aurora in daily basis.

Intuitively I was thinking this progress should be very straightforward because both Redshift and Aurora are nothing but Postgres instances, and we could utilise all the Postgres toolings (pg_dump, pg_restore, COPY etc) to transfer the data. But in reality, nothing is hard until you start to implement and write the actual code to do the work.

There are few things that are not been considered at the beginning of this project.

  1. From Redshift side, the source data is dynamically changing quite frequently
  2. From Aurora side, there are lots of user operations applying to the data, e.g. creating customerised views linked with source data, granting various permissions to different users
  3. The most important and criterial issue is that the query concurrency scenario is out of scope at the beginning. Schemas and tables will be in Table Lock mode if there is a user query against those table, and hence the data updating process will hang there until the schemas and tables are released.

It seems to the table lock problem is the most criterial issue. Luckily we could use the system table pg_locks to detect the current Table Lock information. pg_locks provides a global view of all locks in the database cluster, not only those relevant to the current database. Although its relation column can be joined against pg_class.oid to identify locked relations, this will only work correctly for relations in the current database (those for which the database column is either the current database’s OID or zero).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
prd=> SELECT relation::regclass, locktype, database, relation, pid, mode, granted, fastpath FROM pg_locks;
relation | locktype | database | relation | pid | mode | granted | fastpath
-------------------------------------------------+------------+----------+-----------+-------+-----------------+---------+----------
pg_stat_database | relation | 13934 | 11703 | 54371 | AccessShareLock | t | t
| virtualxid | | | 54371 | ExclusiveLock | t | t
pg_locks | relation | 21465 | 11577 | 68877 | AccessShareLock | t | t
| virtualxid | | | 68877 | ExclusiveLock | t | t
powerusers_xerocard.puxcuc_created | relation | 21465 | 351168910 | 64656 | AccessShareLock | t | t
powerusers_xerocard.puxcuc_usrdeets | relation | 21465 | 322042383 | 64656 | AccessShareLock | t | t
powerusers_xerocard.puxcuc_usrlogin | relation | 21465 | 322042377 | 64656 | AccessShareLock | t | t
powerusers_xerocard.ix_xc_uc_status | relation | 21465 | 314731513 | 64656 | AccessShareLock | t | t
powerusers_xerocard.ix_xc_uc_email | relation | 21465 | 314731512 | 64656 | AccessShareLock | t | t
powerusers_xerocard.ix_xc_uc_userid | relation | 21465 | 314731511 | 64656 | AccessShareLock | t | t
powerusers_xerocard.puxcoc_orgid | relation | 21465 | 314731271 | 64656 | AccessShareLock | t | t
powerusers_xerocard.puxcoc_orgflags | relation | 21465 | 314731270 | 64656 | AccessShareLock | t | t
powerusers_xerocard.ix_xc_userorgrole_role | relation | 21465 | 314731528 | 64656 | AccessShareLock | t | t
powerusers_xerocard.ix_xc_userorgrole | relation | 21465 | 314731487 | 64656 | AccessShareLock | t | t
digitalmarketing_dbo.google_xerogoogleidlink | relation | 21465 | 165584573 | 64656 | AccessShareLock | t | t
digitalmarketing_dbo.core_dimuseraccount | relation | 21465 | 165585662 | 64656 | AccessShareLock | t | t
powerusers_xerocard.organisationmilestones | relation | 21465 | 307437428 | 64656 | AccessShareLock | t | t
powerusers_xerocard.usercard | relation | 21465 | 307432249 | 64656 | AccessShareLock | t | t
powerusers_xerocard.organisationcard | relation | 21465 | 307432243 | 64656 | AccessShareLock | t | t
powerusers_xerocard.userorganisationrole | relation | 21465 | 307437422 | 64656 | AccessShareLock | t | t
| virtualxid | | | 64656 | ExclusiveLock | t | t
digitalmarketing_dbo."IX_core_dimuseraccount" | relation | 21465 | 165594407 | 64656 | AccessShareLock | t | f
powerusers_xerocard.puxcom_orgid | relation | 21465 | 314731283 | 64656 | AccessShareLock | t | f
pg_database_datname_index | relation | 0 | 2671 | 54371 | AccessShareLock | t | f
pg_database | relation | 0 | 1262 | 54371 | AccessShareLock | t | f
digitalmarketing_dbo.pk_xerogoogleidlink | relation | 21465 | 314752517 | 64656 | AccessShareLock | t | f
digitalmarketing_dbo.uq_dimuseraccount_xuser | relation | 21465 | 165594411 | 64656 | AccessShareLock | t | f
digitalmarketing_dbo.pk_dimuseraccount | relation | 21465 | 165594409 | 64656 | AccessShareLock | t | f
pg_database_oid_index | relation | 0 | 2672 | 54371 | AccessShareLock | t | f
digitalmarketing_dbo.ix_google_xerogoogleidlink | relation | 21465 | 314752522 | 64656 | AccessShareLock | t | f

pg_locks provides lots useful information, but here we just choose the following fields

  • locktype. Type of the lockable object: relation, extend, page, tuple, transactionid, virtualxid, object, userlock or advisory
  • database. OID of the database in which the lock target exists, or zero if the target is a shared object, or null if the target is a transaction ID
  • relation. OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation.
  • pid. Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction.
  • mode. Name of the lock mode held or desired by this process.
  • granted. True if lock is held, false if lock is awaited
  • fastpath. True if lock was taken via fast path, false if taken via main lock table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
psa.usename,
psa.query_start,
psa.wait_event_type,
psa.wait_event,
psa.state,
relation::regclass,
pl.locktype,
pl.database,
pl.relation,
pl.pid,
pl.mode,
pl.granted
FROM pg_locks pl
LEFT JOIN pg_stat_activity psa
ON pl.pid = psa.pid;

We could even use the pg_locks and pg_stat_activity tables to check the query age with the following statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
a.datname as db_name,
c.relname as relation_name,
relation::regclass as table_name,
-- l.transactionid,
l.mode,
l.GRANTED,
a.usename,
-- a.query,
a.query_start,
age(now(), a.query_start) AS query_age,
a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
JOIN pg_class c ON c.oid = l.relation
ORDER BY a.query_start;

Reference