Postgres Advisory Locks By Lloyd Albin Sea PUG

Postgres Advisory Locks By Lloyd Albin Sea. PUG is Hosted by: Seattle Postgres Users Group 1 st Tuesday of Every Month @ 7 PM 1100 Eastlake, Seattle, WA http: //www. Sea. PUG. org http: //www. meetup. com/Seattle-Postgres-User-Group-SEAPUG/ Postgres Advisory Locks 1/6/2015 1

Mutex Postgres Advisory Locks 1/6/2015 2

Mutex § Mutex is short mutual exclusion referring to the requirement of ensuring that there are no two concurrent process. § What is this good for? Keeping two processes from using the same object at the same time such as: § Ram § Database Object § Files Postgres Advisory Locks 1/6/2015 3

Examples § Some examples where you might use a mutex are: § Importing data into a database once an hour. Something holds up the import process and now the next process starts while the first one is still running. Use the mutex to prevent the second import from starting. § Backing up the database and the backup takes so long that another backup starts while the first backup is still working. Use the mutex to prevent the second backup from starting. § Script A modified the database before Script B can use the data. By using the mutex, Script B will know when it can start using the new data. Postgres Advisory Locks 1/6/2015 4

Exclusive Locks Postgres Advisory Locks 1/6/2015 5

Postgres Advisory Locks § Postgres supports mutexes in the form of advisory locks. § Some problems with files mutexes are that some Scripts fail to remove the file mutexes when the database is rebooted or the front end application that the Script is talking to is stopped and the connections to the database are dropped. § The benefit to using advisory locks are that they are automatically removed when the database connection is dropped or the database is rebooted. Postgres Advisory Locks 1/6/2015 6

pg_advisory_lock/unlock § Here is an example of two scripts where you want each script to wait for the other script to finish before running. § This type of lock can span multiple transactions. (Starting Script #1) SELECT pg_advisory_lock(1000); (Actions) SELECT pg_advisory_unlock(1000); (Finished Script #1) (Starting Script #1) SELECT pg_advisory_lock(1000); (Waiting) (Actions) SELECT pg_advisory_unlock(1000); (Finished Script #1) (Starting Script #2) SELECT pg_advisory_lock(1000); (Waiting) (Actions) SELECT pg_advisory_unlock(1000); (Finished Script #2) Postgres Advisory Locks 1/6/2015 7

pg_try_advisory_lock/unlock § Here is an example of one scripts where you don’t want it to execute if the script is already running. (Starting Script #1) SELECT pg_try_advisory_lock(1000); (Returns True, Perform Actions) (Starting Script #1) SELECT pg_try_advisory_lock(1000); (Returns False, Exiting Script #1) SELECT pg_advisory_unlock(1000); (Finished Script #1) (Starting Script #1) SELECT pg_try_advisory_lock(1000); (Returns True, Perform Actions) SELECT pg_advisory_unlock(1000); (Finished Script #1) Postgres Advisory Locks 1/6/2015 8

Exclusive Transaction Locks Postgres Advisory Locks 1/6/2015 9

Transaction Locks § There also Advisory Locks that you can apply per transaction, so that the other connection has to wait until your transaction is finished before it runs. Postgres Advisory Locks 1/6/2015 10

pg_advisory_xact_lock § With the Transaction Locking version, the COMMIT/ROLLBACK is used instead of the unlock command. In fact there is no explicit unlock command. § Here is an example of two scripts where you want each script to wait for the other script to finish before running. (Starting Script #1) SELECT pg_advisory_xact_lock(1000); (Actions) COMMIT; (Finished Script #1) (Actions) (Starting Script #1) SELECT pg_advisory_xact_lock(1000); (Waiting) (Actions) ROLLBACK; (Finished Script #1) (Starting Script #2) SELECT pg_advisory_xact_lock(1000); (Waiting) COMMIT; (Finished Script #2) Postgres Advisory Locks 1/6/2015 11

pg_try_advisory_xact_lock § Here is an example of one scripts where you don’t want it to execute if the script is already running. (Starting Script #1) SELECT pg_try_advisory_xact_lock(1000); (Returns True, Perform Actions) (Starting Script #1) SELECT pg_try_advisory_xact_lock(1000); (Returns False, Exiting Script #1) COMMIT; (Finished Script #1) (Starting Script #1) SELECT pg_try_advisory_xact_lock(1000); (Returns True, Perform Actions) COMMIT; (Finished Script #1) Postgres Advisory Locks 1/6/2015 12

Shared Locks Postgres Advisory Locks 1/6/2015 13

Shared (Not Exclusive) Locks § All the previous examples have been exclusive locks. § You may add _shared to the end of any of the previous commands and then it will become a shared lock command. § With shared locks, all shared locks must be released before an exclusive lock can be obtained. Any exclusive lock must be released before any shared locks can be obtained. Postgres Advisory Locks 1/6/2015 14

Other Information Postgres Advisory Locks 1/6/2015 15

(Big Int) vers (Int, Int) § All of the Postgres Advisory commands allow either (Big Int) or (Int, Int). § Here is a case for using the (Int, Int) version: § You have a backup script that reads from a central database and then performs individual database backups. The double integer version allows you to lock based on both application and database. (Database Integer, Application Integer). Postgres Advisory Locks 1/6/2015 16

Cross Database § Postgres Advisory Locks are viewable across all databases on a server even though they only apply to the database they are connection is connected to. Postgres Advisory Locks 1/6/2015 17

How to query the locks table § Here is a query that will show you all the advisory locks. § Objsubid = 1 if you used the (bigint) and 2 if you used the (int, int). § Mode = Exclusive. Lock or Shared. Lock § Granted = False if the lock is waiting on another lock to be unlocked. § Please Note: These are unsigned integers in this view and the id that you passed were signed integers. We will address this issue on slide 23. SELECT pg_locks. locktype, pg_stat_activity. datname, pg_stat_activity. application_name, pg_stat_activity. usename, pg_locks. classid AS lock_class_id, pg_locks. objid AS lock_id, pg_locks. objsubid, pg_locks. pid, pg_locks. mode, pg_locks. granted FROM pg_locks LEFT JOIN pg_stat_activity ON pg_locks. pid = pg_stat_activity. pid WHERE locktype = 'advisory'; locktype datname application_name usename lock_class_id lock_id objsubid pid mode granted advisory main fw db-sync production postgres 74 1000 2 27946 Exclusive. Lock True Postgres Advisory Locks 1/6/2015 18

Gotcha’s Postgres Advisory Locks 1/6/2015 19

Gotcha’s § If you perform a SET ROLE in your code, the Advisory Lock will be owned by the connecting user NOT the role you have become via the SET ROLE and can be unlocked by any ROLE used in the same connection session. § Locks are per Database, so you may use the same Lock ID more than once on the same server within different databases. § You are allowed to create a exclusive lock after creating a shared lock using the same lock id and using the same database connection if there are no other shared locks using the same shared lock id in other database connections. Normally you have to release all the shared locks before the exclusive lock can be established. § Creating a Transaction Level lock outside of a transaction will not create any lock and also not fail, this is because every command outside of a transaction is in and of itself it's own transaction. Postgres Advisory Locks 1/6/2015 20

Our Rules § Here are the rules that we use in house. § If you need only one lock per application, use the single number advisory lock method. § If you need more than one exclusive lock per application use the two number advisory lock method. The first number can be any integer the developer of the application wish’s. The second number is the Application Lock ID. Postgres Advisory Locks 1/6/2015 21

Signed vers Unsigned Integers/Big Integers § Signed Integer § -2, 147, 583, 648 to 2, 147, 483, 647 § Signed Big Integer § -9, 223, 372, 036, 854, 775, 808 to 9, 223, 372, 036, 854, 775, 807 § Unsigned Integer / OID § 0 to 4, 294, 967, 295 § Unsigned Big Integer § 0 to 18, 446, 744, 073, 709, 551, 615 Once of the big gotcha’s is that the you pass signed integers into the advisory locks and the pg_locks view shows OID’s which are unsigned integers. This makes the pg_locks view hard to compare to the advisory lock commands you executed. Postgres Advisory Locks 1/6/2015 22

How to find the original signed lock_id’s SELECT pg_locks. locktype, pg_stat_activity. datname, pg_stat_activity. application_name, pg_stat_activity. usename, CASE WHEN pg_locks. objsubid = 1 THEN NULL WHEN pg_locks. classid <= 2147483647 THEN pg_locks. classid: : bigint ELSE 4294967295: : bigint << 32 | pg_locks. classid: : bigint END AS lock_class_id, Signed Integer 0 1 2 … 2147183646 2147183647 -2147183648 -2147183647 … -2 -1 Unsigned Integer 0 1 2 … 2147183646 2147183647 2147183648 2147183649 … 4294967294 4294967295 CASE WHEN pg_locks. objsubid = 1 THEN pg_locks. classid: : bigint << 32 | pg_locks. objid: : bigint WHEN pg_locks. objid <= 2147483647 THEN pg_locks. objid: : bigint ELSE 4294967295: : bigint << 32 | pg_locks. objid: : bigint END AS lock_id, sql pg_locks. objsubid, pg_locks. pid, pg_locks. mode, pg_locks. granted FROM pg_locks LEFT JOIN pg_stat_activity ON pg_locks. pid = pg_stat_activity. pid WHERE locktype = 'advisory'; SELECT pg_advisory_lock(0); SELECT pg_advisory_lock(1); SELECT pg_advisory_lock(-2); SELECT pg_advisory_lock(2147483647); SELECT pg_advisory_lock(-2147483648); SELECT pg_advisory_lock(0, 2147483647); SELECT pg_advisory_lock(1, 0); SELECT pg_advisory_lock(9223372036854775000); When converting signed integers to unsigned integers, once you get to the largest signed integer, then next number higher will be the largest negative number and then you will continue higher with the unsigned integer while counting down to -1 with the signed integer. classid 0 0 4294967295 0 1 2147483647 objid 0 1 4294967295 4294967294 2147483647 2147483648 2147483647 0 4294966488 lock_class_id lock_id Postgres Advisory Locks 0 1 -1 -2 2147483647 -2147483648 0 2147483647 1 0 9223372036854775000 objsubid 1 1 1 2 2 1 1/6/2015 23

Convert Unsigned Int (OID) to Signed Big. Int -- lock_class_id CASE -- 1 Big. Int as 2 OIDS (Null Value) WHEN pg_locks. objsubid = 1 THEN NULL -- 2 Int's as 2 OIDS ELSE pg_locks. classid: : bigint: : bit(32): : integer: : bigint END AS lock_class_id, -- lock_id CASE -- 1 Big. Int as 2 OIDS (Null Value) WHEN pg_locks. objsubid = 1 THEN pg_locks. classid: : bigint << 32 | pg_locks. objid: : bigint -- 2 Int's as 2 OIDS ELSE pg_locks. objid: : bigint: : bit(32): : integer: : bigint END AS lock_id, sql SELECT pg_advisory_lock(0); SELECT pg_advisory_lock(1); SELECT pg_advisory_lock(-2); SELECT pg_advisory_lock(2147483647); SELECT pg_advisory_lock(-2147483648); SELECT pg_advisory_lock(0, 2147483647); SELECT pg_advisory_lock(1, 0); SELECT pg_advisory_lock(9223372036854775000); classid 0 0 4294967295 0 1 2147483647 objid 0 1 4294967295 4294967294 2147483647 2147483648 2147483647 0 4294966488 lock_class_id lock_id Postgres Advisory Locks 0 1 -1 -2 2147483647 -2147483648 0 2147483647 1 0 9223372036854775000 objsubid 1 1 1 2 2 1 1/6/2015 24

The Math sql classid objid lock_class_id lock_id objsubid 4294967295 4294967294 -2 1 SELECT pg_advisory_lock(-2); 9223372036854775000 1 SELECT pg_advisory_lock(9223372036854775000); 2147483647 4294966488 pg_locks. classid: : bigint << 32 | pg_locks. objid: : bigint = signed bigint 4294967295: : bigint << 32 | pg_locks. objid: : bigint = negative signed integer 1111 1111 = 4294967295 unsigned - classid 1111 1111 1110 = 4294967294 unsigned - objid 1111 1111 << 32 ======================================== 1111 1111 0000 0000 0000 0000 1111 1111 1110 | ======================================== 1111 1111 1111 1111 1110 = -2 signed << bitwise shift left | bitwise OR Signed Integer = Dword Signed Big Integer = Qword § § Negative Number 0111 1111 1111 = 2147483647 unsigned - classid 1111 1111 1100 1101 1000 = 4294966488 unsigned - objid 0111 1111 1111 << 32 ======================================== 0111 1111 1111 0000 0000 0000 0000 1111 1111 1100 1101 1000 | ======================================== 0111 1111 1111 1111 1100 1101 1000 = 9223372036854775000 signed Positive Number § For more information on this math, please see: http: //en. wikipedia. org/wiki/Two%27 s_complement Postgres Advisory Locks 1/6/2015 25

SQL Manager for Postgres § Currently SQL Manager for Postgre. SQL will only show the positive side of the signed integer in the pg_locks view. The negative side of the signed integer will show up as 0 instead of the unsigned values of 2, 147, 483, 648 through 4, 294, 967, 295. § PG Admin III, db Visualizer, psql all work properly. § I have a bug ticket (32428) submitted. Their response is: § § We have managed to reproduce the described incorrect behavior of the program. We are going to fix it in one of the nearest version of SQL Manager. Bug Ticket (32429) will also fix an upgrade failure if people download the latest version and have both new and old keys in their registry due to the length of time they have used the product. § We are going to fix it in the next release of SQL Management Studio installation package. Postgres Advisory Locks 1/6/2015 26