Masking PostgreSQL - Hangs, Blocks, and Deadlocks (KBA10089)

Possible Scenarios

There are a few possible scenarios for why locks and deadlock happens: 

• The record order of the fetch is scattered (i.e. due to the order in an index, SORT, JOIN, or WHERE) resulting in scattered locks on SELECT. 
• Due to the above, the updated pages are also scattered. 
• The number of locks or limited resources on the databases causes lock escalations. 
• The number of pages locked increases when WHERE is used. 
• External processes (queries) are running while the database is masked.

Execution hung

There are no errors or warnings when the database is blocked. The masking engine will just wait for the database to return the status from the commit (Update) which is not happening (as it is waiting for a lock to release).
 

That the database queries have become unresponsive can be seen on the masking engine in these three areas: 

...
ThroughputLogger:interval 0.000 MB (0 rows) counted in 30.000 sec; throughput = 0.000 MB/sec
...

Execution deadlocked 

A deadlock is when the database deadlock monitor thread has identified an unresolvable block and terminates one of the processes. This will resolve the lock but also terminate the masking job.

The error seen in the Masking Logs is something like this: 

ERROR:  deadlock detected
Detail: Process [nnn] waits for [Lock] on transaction [mmm]; blocked by process [ppp].

INDEX on Masked column

Indexes are reference tables created to locate data more efficiently. When an indexed column is masked, both the table and the index need to be updated. Since the index is a reference table (frequently a b-tree or hash table), this table needs to be updated and re-arranged. As such, indexes can take more locks than the masked table.

CONSTRAINT on Masked column

Many Constraints use Indexes to improve performance and for checking data in the constraint. Constraints will therefore also take locks. 

WHERE clause used in Custom SQL 

Where clauses can change the Execution Plan and the order of the fetched data. This is especially true if there is an Index on the column in the predicate (where). If an index scan is used, the SELECT might access records in a 'non-logical' order, which will increase the number of locks.

Note: the best order is a Full Table Scan. 

ORDER BY in the Rule Set SQL 

Order By will change the order in which the data is fetched. This will result in scattered and fragmented reads and increase the number of locks.  Do not use ORDER BY.

UNION in the Rule Set SQL 

Union will cause multiple passes through the data and hence cause the SELECT and UPDATE locks to intersect. Do not use UNION.

Row count executed while table being masked 

Before the masking job starts, the masking engine will execute a SELECT COUNT query on all tables in the masked Rule Set. Most of the time, the result of this is returned before the job starts, but for large tables the locks on SELECT COUNT can cause issues. 

The SQL for SELECT is the same here, so all points listed above do apply to the SELECT COUNT as well. 

Parallel processing on SELECT 

It is best to not use parallel processing on SELECT. PostgreSQL suggests using this feature with caution. Furthermore, the SELECT is not the slow part in masking - UPDATE is. 

There are likely other possible causes as to why locks will cause the masking job to hang. To understand why, the first step is to understand what type of locks and what objects and transactions are locking. How to do that, coming up...

1: Get current active processes  

This SQL lists active processes. Make note of the PID.

SELECT pid, datname, usename, state, query FROM pg_stat_activity WHERE state = 'active';

-- Query in multiple lines
-- ------------------------
SELECT pid, datname, usename, state, query 
FROM pg_stat_activity 
WHERE state = 'active';

2: Get lock status on current processes

This SQL provides all current locks on the database (note that this list can be long on a busy database). Check the relevant PIDs from the query above.

SELECT pid, locktype, relation::regclass, mode, transactionid AS tid, virtualtransaction AS vtid, granted FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db ON db.oid=l.database WHERE db.datname IS NULL AND NOT pid = pg_backend_pid();

-- Query in multiple lines
-- ------------------------
SELECT 
   pid, 
   locktype, 
   relation::regclass,
   mode, 
   transactionid AS tid, 
   virtualtransaction AS vtid, 
   granted
FROM pg_catalog.pg_locks l 
LEFT JOIN pg_catalog.pg_database db ON db.oid=l.database 
WHERE db.datname IS NULL AND NOT pid = pg_backend_pid();

3: Find process in PG_LOGS  

More details might be found in the PG_LOGS.

• Get process ID from queries above.
• Replace '<pid of the process>' with the process ID.
• Look in the latest log file.

grep p:<pid of the process> [latest log].log