Contents

• Field creation explained
• Identify blocking queries
• References
• Appendix A - Blocking activity query
• Appendix B - Blocking activity query result
• Appendix C - Identifying the query in the logs

Field creation explained

Adding new fields in Flow Production Tracking is a powerful feature that allows users to dynamically customize their Flow Production Tracking Instance data model.

This capability comes at a cost. In the database world, adding a field corresponds to adding a new column in a table (ALTER TABLE). The lock that needs to be obtained to add a field to a column is an ACCESS EXCLUSIVE lock on the table. This mode guarantees that the holder is the only transaction accessing the table in any way, including reads. Concretely, it means that once the field creation request is submitted, all previous requests on the table must complete before the field creation happen. This also means that all subsequent requests are blocked until the field creation is completed.

If a very long running request is in progress and a field is created, this could theoretically lead to a very long period where requests on that table are blocked. For a user, it would look as if the site is slow or unresponsive. To prevent this, Flow Production Tracking only waits for the acquisition of the lock for a small period of time, after which the lock request is cancelled, unblocking the subsequent requests waiting (lock acquisition timeout).

Identify blocking queries

If you really can’t succeed in creating a field, it’s probably because you have too many long running requests on the table on which the field is to be created. The lock acquisition timeout should be long enough so this situation cannot happen.

If it’s the case however, the best thing to do is to identify which queries are blocking the lock acquisition from happening. This article explains how to troubleshoot such issues.

1. Open a psql command line on the database (from the database server or an application server).

   psql -h localhost -d com_mystudio_shotgun -U shotgun

2. Copy and paste the Blocking Activity Query (see Appendix A).

3. Start the field creation in the UI/API.

4. Continuously run the Blocking Activity Query during the field creation.

5. The blocked ALTER statement should appear, along with the statement preventing the ALTER to obtain a lock (see Appendix B for an example).

6. Search in the production logs to identify the user or API making the request.

   grep “SELECT * FROM custom_entity02s" /var/rails/com_mystudio_shotgun/shared/log/production.log

7. Take action, either by stopping the script for the duration of the creation or by optimizing the query.

References

PostgreSQL Explicit Locking

https://www.postgresql.org/docs/12/explicit-locking.html

Lock Monitoring - PostgreSQL wiki

https://wiki.postgresql.org/wiki/Lock_Monitoring

Appendix A - Blocking activity query

  SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

Appendix B - Blocking activity query result

  blocked_pid   |   blocked_user  | blocking_pid |   blocking_user   |   blocked_statement                                        | current_statement_in_blocking_process

    94496 | int_shotgun_anim |      53876 | carbine               | ALTER TABLE custom_entity02s ADD COLUMN sg_step_lighting text | select custom_entity02_id from custom_entity02_sg_shots_lighting_connections where shot_id = 4031 and retirement_date is null

blocked_pid                             94496
blocked_user                            com_mystudio_shotgun
blocking_pid                            53876
blocking_user                           shotgun
blocked_statement                       ALTER TABLE custom_entity02s ADD COLUMN sg_step_lighting text
current_statement_in_blocking_process   SELECT * FROM custom_entity02s

Appendix C - Identifying the query in the logs

 2017-05-24T15:26:26.619831+00:00 INFO shotgun rails[94991]: SQL.find_native: 1.0706ms db=com_mystudio_shotgun shotgun_version="7.0.8.1" shotgun_build=ebfd78d request_id=5a75fc6b290b319732b6bce8e8f43f30 api_method=read **api_user=evil_script** -- SELECT * FROM custom_entity02s