PostgreSQL – Are CREATE FUNCTION and DROP FUNCTION Locking in Different Transactions?

concurrencyfunctionspostgresqltransaction

I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables but writing on different output tables. Inside my transactions I have several DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION statements that declare some functions.

My guess is that DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION are locking. Am I right? I can solve the problem by moving the statement outside the transaction.

I've observed the following: two transactions A and B contains some DROP FUNCTION and CREATE OR REPLACE FUNCTION; if I execute transaction B while transaction A is still running, transaction B waits on the first DROP FUNCTION statements it encounters.

Best Answer

_{Community wiki answer:}

Yes of course the other transaction will wait. (Nearly) Every DDL statement takes an exclusive lock on the object being modified (dropped, created). See Explicit Locking in the documentation.

Related Solutions

PostgreSQL – How to Resolve Deadlock on CREATE TABLE

Let's assume that the target table of the INSERT ("TableD") has a column with a foreign key to "TableC". An INSERT to this table must acquire a shared lock on "TableC" and will keep that lock until the transaction ends.

Say two concurrent transactions, T1 and T2, start with such an INSERT. Each takes a shared lock on "TableC".

Later on, in order to do a CREATE TABLE involving a foreign key to "TableC", T1 must acquire an access exclusive lock on "TableC".

Since T2 has already a shared lock on "TableC", T1 will be put to wait until T2 commits or rollbacks.

At this point, if T2 tries to create a table also involving a foreign key to "TableC" (which again implies acquiring an access exclusive lock on "TableC"), it must wait for T1 to commit or rollback, because T1 holds a shared lock on "TableC".

So now T1 is waiting for T2 to finish and T2 is waiting for T1 to finish: there's the deadlock.

To avoid this situation, an explicit and exclusive lock may be taken at the beginning of the transaction, or at least at the beginning of the sequence of instructions that can't be run safely in parallel by concurrent transactions.

MySQL – Create Function for Transactions Using Multiple Databases

I believe I don't quite get what your question is about. The following function would insert those values, with the same values on fields like create_time, within a transaction, on multiple databases. Is this what you're looking for?

delimiter //
CREATE PROCEDURE yourdb.yourprocname (_first_name varchar(100), _last_name varchar(100), _create_time datetime, _update_time datetime,  _hash_id varchar(32),
  _id_status int, _id int, _criminal_status int, _day int, _month int, _year int, _state_id int, _state_id_status int)
begin
  START TRANSACTION;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
    VALUES (_first_name, _last_name, _create_time, _update_time, _hash_id);

  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_id_status, _id, _criminal_status, _create_time, _update_time, LAST_INSERT_ID());

  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_day, _month, _year, _state_id, _state_id_status, _create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Example call:

call yourdb.yourprocname ('Lola', 'High', CURRENT_TIMESTAMP, ... etc)

Best Answer

Related Solutions

PostgreSQL – How to Resolve Deadlock on CREATE TABLE

MySQL – Create Function for Transactions Using Multiple Databases

Example call:

Related Question