4

I'm getting this deadlock error in my database:

    2017-02-22 20:57:48 GMT ERROR:  deadlock detected
2017-02-22 20:57:48 GMT DETAIL:  Process 7372 waits for ExclusiveLock on relation 18856 of database 18347; blocked by process 5724.
    Process 5724 waits for ExclusiveLock on relation 18856 of database 18347; blocked by process 7372.
    Process 7372: insert into Transaction (attribute1, attribute2, attribute3) values ($1, $2, $3) RETURNING *
    Process 5724: insert into Transaction (attribute1, attribute2, attribute3) values ($1, $2, $3) RETURNING *
2017-02-22 20:57:48 GMT HINT:  See server log for query details.
2017-02-22 20:57:48 GMT CONTEXT:  SQL statement "LOCK TABLE niv IN EXCLUSIVE MODE"
    PL/pgSQL function updateniv() line 8 at SQL statement
2017-02-22 20:57:48 GMT STATEMENT:  insert into Transaction (attribute1, attribute2, attribute3) values ($1, $2, $3) RETURNING *
2017-02-22 20:57:48 GMT ERROR:  current transaction is aborted, commands ignored until end of transaction block
2017-02-22 20:57:48 GMT STATEMENT:  SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE 'PROBABLYNOT' AND (false  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME 

I have a trigger which is called on insert in table Transaction, that lock table Niv (Other table in my database) in exclusive mode. This is the trigger:

CREATE OR REPLACE FUNCTION public.updateniv()
  RETURNS trigger AS
$BODY$
DECLARE
    c numeric; -- Creditos
    d numeric; -- Debitos
    credb numeric; -- Creditos bloqueados
    debtb numeric; -- Debitos bloqueados
BEGIN
    LOCK TABLE niv IN EXCLUSIVE MODE;

    //do some actions

    END IF;   

    RETURN NEW;
END;  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Looking in I can see that the deadlock ocurrs when have two inserts into table transaction, which calls the the trigger updateniv(), which lock table NIV in exclusive mode. But I can't understand the cause of the deadlock, if a insert locks the table NIV in exclusive mode, the other insert should wait the first lock finish correct? How table NIV is locked in exclusive mode two times same time? Can anyone help me?

I tried Postgresql 9.5 and 9.2 on windows server

Here more details from the log:

    2017-03-03 01:48:20 GMT LOG:  process 7256 still waiting for RowShareLock on relation 21748 of database 21239 after 1000.000 ms
2017-03-03 01:48:20 GMT STATEMENT:  insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:20 GMT LOG:  process 7244 still waiting for ExclusiveLock on relation 21748 of database 21239 after 1000.000 ms
2017-03-03 01:48:20 GMT CONTEXT:  SQL statement "LOCK TABLE niv IN EXCLUSIVE MODE"
    PL/pgSQL function updateniv() line 25 at SQL statement
2017-03-03 01:48:20 GMT STATEMENT:  update Transaction set adminUpdateBonusSponsor=$1, blocked=$2, currencyType=$3, dateCompleted=$4, dateTransfered=$5, dateUpdateBonusSponsor=$6, depositLinked=$7, establishmentApi_id=$8, ewctokenWithdrawn=$9, exchangeRate=$10, extraInfo=$11, extraRate=$12, idSponsor=$13, invoiceNumber=$14, oid=$15, paymentUserInfo_id=$16, protocol=$17, qtdCardPurchaseThisSponsor=$18, qtdParcel=$19, rates_id=$20, receiver_id=$21, receiverBtcAddress=$22, relatedInvestment_id=$23, sender_id=$24, sourceRef=$25, status=$26, statusBonusSponsor=$27, tax=$28, tokenCryptocurrencyApi=$29, transactionServiceType=$30, transactionSource=$31, transactionSourceDepositLinked=$32, transactionSourceMethod=$33, transactionSourceMethodAdditional=$34, transactionStatus=$35, uniqueAccountUser=$36, value=$37, valueBitcoin=$38, valueEwc=$39, valueEwcVoucher=$40, valueGtc=$41, wireInformed=$42 where id=$43
2017-03-03 01:48:22 GMT LOG:  process 7256 acquired RowShareLock on relation 21748 of database 21239 after 2797.000 ms
2017-03-03 01:48:22 GMT STATEMENT:  insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:22 GMT LOG:  execute <unnamed>: insert into TransactionStatusUpdate (date, resendNeeded, responseCode, transaction_id, transactionStatus) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:22 GMT DETAIL:  parameters: $1 = '2017-03-02 22:48:36.444', $2 = 'f', $3 = '200', $4 = '59196831', $5 = '0'
2017-03-03 01:48:22 GMT LOG:  execute <unnamed>: insert into PurchaseDistribution (date, establishment_id, processed, protocol, transaction_id) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:22 GMT DETAIL:  parameters: $1 = '2017-03-02 22:48:36.459', $2 = '1', $3 = 'f', $4 = '003284227102302012', $5 = '59196831'
2017-03-03 01:48:23 GMT LOG:  execute <unnamed>: insert into Message (datePosted, fromUser_id, message, read, receiverDeleted, reply_id, senderDeleted, sent, title, toUser_id, type) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING *
2017-03-03 01:48:23 GMT DETAIL:  parameters: $1 = '2017-03-02 22:48:36.459', $2 = NULL, $3 = 'You received a transaction (003284227102302012)  in the amount of <strong>BIT$ 500,00</strong>. <br /><br /><a href=''https://coinexpay.com/panel#!balance_consultations'' target="_blank"> Ckick here </a> to view transaction details', $4 = 'f', $5 = 'f', $6 = NULL, $7 = 'f', $8 = 'f', $9 = 'Transaction received (003284227102302012)', $10 = '6', $11 = '1'
2017-03-03 01:48:23 GMT LOG:  execute <unnamed>: insert into Mail (message, name, sendTo, subject, usr) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:23 GMT DETAIL:  parameters: $1 = 'You received a transaction (003284227102302012)  in the amount of <strong>BIT$ 500,00</strong>. <br /><br /><a href=''https://coinexpay.com/panel#!balance_consultations'' target="_blank"> Ckick here </a> to view transaction details', $2 = 'Allan  Smith', $3 = 'coinexpay@gmail.com', $4 = 'Transaction received (003284227102302012)', $5 = NULL
2017-03-03 01:48:23 GMT LOG:  execute <unnamed>: insert into Message (datePosted, fromUser_id, message, read, receiverDeleted, reply_id, senderDeleted, sent, title, toUser_id, type) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING *
2017-03-03 01:48:23 GMT DETAIL:  parameters: $1 = '2017-03-02 22:48:36.459', $2 = NULL, $3 = 'Your transaction (003284227102302012) in the amount of <strong>BIT$ 500,00</strong> was completed. <br /><br /><a href=''https://coinexpay.com/panel#!balance_consultations'' target="_blank">Ckick here</a> to view transaction details', $4 = 'f', $5 = 'f', $6 = NULL, $7 = 'f', $8 = 'f', $9 = 'Transaction completed (003284227102302012)', $10 = '6', $11 = '1'
2017-03-03 01:48:23 GMT LOG:  execute <unnamed>: insert into Mail (message, name, sendTo, subject, usr) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:23 GMT DETAIL:  parameters: $1 = 'Your transaction (003284227102302012) in the amount of <strong>BIT$ 500,00</strong> was completed. <br /><br /><a href=''https://coinexpay.com/panel#!balance_consultations'' target="_blank">Ckick here</a> to view transaction details', $2 = 'Allan  Smith', $3 = 'coinexpay@gmail.com', $4 = 'Transaction completed (003284227102302012)', $5 = NULL
2017-03-03 01:48:24 GMT LOG:  execute <unnamed>: insert into Log (date, niv_id, refId, refText, type) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:24 GMT DETAIL:  parameters: $1 = '2017-03-02 22:48:37.632', $2 = '10', $3 = '59196831', $4 = NULL, $5 = '1'
2017-03-03 01:48:24 GMT LOG:  execute <unnamed>: insert into Log (date, niv_id, refId, refText, type) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:24 GMT DETAIL:  parameters: $1 = '2017-03-02 22:48:37.616', $2 = '2', $3 = '59196831', $4 = NULL, $5 = '2'
2017-03-03 01:48:24 GMT LOG:  execute <unnamed>: insert into TransferOrder (description, transaction_id, value) values ($1, $2, $3) RETURNING *
2017-03-03 01:48:24 GMT DETAIL:  parameters: $1 = 'Resgate de Bonus bicoinex', $2 = NULL, $3 = '50.00'
2017-03-03 01:48:24 GMT LOG:  execute <unnamed>: insert into TransactionRates (dynamicRate, notRatedValue, rateCompany, ratePRS, total) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:24 GMT DETAIL:  parameters: $1 = '0.00', $2 = '50.00', $3 = '0', $4 = '0', $5 = '0.00'
2017-03-03 01:48:24 GMT LOG:  execute <unnamed>: insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:24 GMT DETAIL:  parameters: $1 = NULL, $2 = 'f', $3 = '3', $4 = '2017-03-02 22:48:38.708', $5 = '2017-03-02 22:48:38.693', $6 = NULL, $7 = 'f', $8 = NULL, $9 = 'f', $10 = NULL, $11 = NULL, $12 = NULL, $13 = NULL, $14 = '0', $15 = '154779', $16 = NULL, $17 = '00338422710230203158', $18 = NULL, $19 = NULL, $20 = '197007', $21 = '3153', $22 = NULL, $23 = NULL, $24 = '5', $25 = NULL, $26 = NULL, $27 = NULL, $28 = NULL, $29 = NULL, $30 = '21', $31 = '0', $32 = NULL, $33 = NULL, $34 = NULL, $35 = '0', $36 = 'f', $37 = '50.00', $38 = NULL, $39 = NULL, $40 = NULL, $41 = NULL, $42 = 'f'
2017-03-03 01:48:25 GMT LOG:  process 7244 acquired ExclusiveLock on relation 21748 of database 21239 after 5640.000 ms
2017-03-03 01:48:25 GMT CONTEXT:  SQL statement "LOCK TABLE niv IN EXCLUSIVE MODE"
    PL/pgSQL function updateniv() line 25 at SQL statement
2017-03-03 01:48:25 GMT STATEMENT:  update Transaction set adminUpdateBonusSponsor=$1, blocked=$2, currencyType=$3, dateCompleted=$4, dateTransfered=$5, dateUpdateBonusSponsor=$6, depositLinked=$7, establishmentApi_id=$8, ewctokenWithdrawn=$9, exchangeRate=$10, extraInfo=$11, extraRate=$12, idSponsor=$13, invoiceNumber=$14, oid=$15, paymentUserInfo_id=$16, protocol=$17, qtdCardPurchaseThisSponsor=$18, qtdParcel=$19, rates_id=$20, receiver_id=$21, receiverBtcAddress=$22, relatedInvestment_id=$23, sender_id=$24, sourceRef=$25, status=$26, statusBonusSponsor=$27, tax=$28, tokenCryptocurrencyApi=$29, transactionServiceType=$30, transactionSource=$31, transactionSourceDepositLinked=$32, transactionSourceMethod=$33, transactionSourceMethodAdditional=$34, transactionStatus=$35, uniqueAccountUser=$36, value=$37, valueBitcoin=$38, valueEwc=$39, valueEwcVoucher=$40, valueGtc=$41, wireInformed=$42 where id=$43
2017-03-03 01:48:25 GMT LOG:  process 4312 still waiting for RowShareLock on relation 21748 of database 21239 after 1000.000 ms
2017-03-03 01:48:25 GMT STATEMENT:  insert into Log (date, niv_id, refId, refText, type) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:25 GMT LOG:  process 4904 still waiting for RowShareLock on relation 21748 of database 21239 after 1000.000 ms
2017-03-03 01:48:25 GMT STATEMENT:  insert into Log (date, niv_id, refId, refText, type) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:25 GMT LOG:  process 7832 still waiting for RowShareLock on relation 21748 of database 21239 after 1000.000 ms
2017-03-03 01:48:25 GMT STATEMENT:  insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:26 GMT LOG:  execute <unnamed>: update TransferOrder set description=$1, transaction_id=$2, value=$3 where id=$4
2017-03-03 01:48:26 GMT DETAIL:  parameters: $1 = 'Transferência BIT para BitTradeCash para distribuição - 00333422710230201086', $2 = '59196833', $3 = '48.78', $4 = '154778'
2017-03-03 01:48:26 GMT LOG:  execute <unnamed>: insert into TransferOrder (description, transaction_id, value) values ($1, $2, $3) RETURNING *
2017-03-03 01:48:26 GMT DETAIL:  parameters: $1 = 'Distribuição - 00333422710230201086', $2 = NULL, $3 = '1.829250'
2017-03-03 01:48:26 GMT LOG:  execute <unnamed>: insert into TransactionRates (dynamicRate, notRatedValue, rateCompany, ratePRS, total) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:26 GMT DETAIL:  parameters: $1 = '0.000000', $2 = '1.829250', $3 = '0', $4 = '0', $5 = '0.00'
2017-03-03 01:48:26 GMT LOG:  execute <unnamed>: insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:26 GMT DETAIL:  parameters: $1 = NULL, $2 = 'f', $3 = '3', $4 = '2017-03-02 22:48:40.171', $5 = '2017-03-02 22:48:40.171', $6 = NULL, $7 = 'f', $8 = NULL, $9 = 'f', $10 = NULL, $11 = NULL, $12 = NULL, $13 = NULL, $14 = '0', $15 = '154780', $16 = NULL, $17 = '003484227102302010', $18 = NULL, $19 = NULL, $20 = '197008', $21 = '8', $22 = NULL, $23 = NULL, $24 = '2', $25 = '00333422710230201086', $26 = NULL, $27 = NULL, $28 = NULL, $29 = NULL, $30 = '21', $31 = '0', $32 = NULL, $33 = NULL, $34 = NULL, $35 = '0', $36 = 'f', $37 = '1.829250', $38 = NULL, $39 = NULL, $40 = NULL, $41 = NULL, $42 = 'f'
2017-03-03 01:48:27 GMT LOG:  process 4312 acquired RowShareLock on relation 21748 of database 21239 after 2844.000 ms
2017-03-03 01:48:27 GMT STATEMENT:  insert into Log (date, niv_id, refId, refText, type) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:27 GMT LOG:  process 7832 acquired RowShareLock on relation 21748 of database 21239 after 2672.000 ms
2017-03-03 01:48:27 GMT STATEMENT:  insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:27 GMT LOG:  process 4904 acquired RowShareLock on relation 21748 of database 21239 after 2797.000 ms
2017-03-03 01:48:27 GMT STATEMENT:  insert into Log (date, niv_id, refId, refText, type) values ($1, $2, $3, $4, $5) RETURNING *
2017-03-03 01:48:27 GMT ERROR:  deadlock detected
2017-03-03 01:48:27 GMT DETAIL:  Process 7832 waits for ExclusiveLock on relation 21748 of database 21239; blocked by process 7256.
    Process 7256 waits for ExclusiveLock on relation 21748 of database 21239; blocked by process 7832.
    Process 7832: insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
    Process 7256: insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:27 GMT HINT:  See server log for query details.
2017-03-03 01:48:27 GMT CONTEXT:  SQL statement "LOCK TABLE niv IN EXCLUSIVE MODE"
    PL/pgSQL function updateniv() line 25 at SQL statement
2017-03-03 01:48:27 GMT STATEMENT:  insert into Transaction (adminUpdateBonusSponsor, blocked, currencyType, dateCompleted, dateTransfered, dateUpdateBonusSponsor, depositLinked, establishmentApi_id, ewctokenWithdrawn, exchangeRate, extraInfo, extraRate, idSponsor, invoiceNumber, oid, paymentUserInfo_id, protocol, qtdCardPurchaseThisSponsor, qtdParcel, rates_id, receiver_id, receiverBtcAddress, relatedInvestment_id, sender_id, sourceRef, status, statusBonusSponsor, tax, tokenCryptocurrencyApi, transactionServiceType, transactionSource, transactionSourceDepositLinked, transactionSourceMethod, transactionSourceMethodAdditional, transactionStatus, uniqueAccountUser, value, valueBitcoin, valueEwc, valueEwcVoucher, valueGtc, wireInformed) values ($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, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING *
2017-03-03 01:48:27 GMT ERROR:  current transaction is aborted, commands ignored until end of transaction block
2017-03-03 01:48:27 GMT STATEMENT:  SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE 'PROBABLYNOT'  AND (false  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME 
4

It looks to me like both transactions acquired a weaker lock (probably ROW EXCLUSIVE (but now clarified in a comment to be the more rare ROW SHARE)) on NIV earlier in their transaction. Now both want to upgrade that lock to EXCLUSIVE, but neither can do so until the other gives up their weaker lock. But neither is willing to give up weaker lock until they obtain the stronger one.

The error messages only tell you the current query of each process (because that info is what is available to the logging system) but you really need to know all queries that were previously run in the currently-open transaction to get a full picture.

In your comment, you say the weaker lock is actually a ROW SHARE. This is automatically acquired by the commands SELECT FOR UPDATE and SELECT FOR SHARE see documentation. You would have to identify where in your code these types of queries are issued (in the same transaction as the other queries which later trigger the deadlock are issued) and pre-emptively take an EXCLUSIVE lock before issuing those queries.

  • What type of queries can acquire a weaker lock? – user3078383 Mar 2 '17 at 17:37
  • Thank you for your answer @jjanes, you were correct. I edited adding more details from the log (see above) and I can see that the two process acquired a weaker lock (ROW SHARE LOCK) and both want to upgrade to EXCLUSIVE LOCK. Now my questions is: How they aquired ROW SHARE LOCK and what can I do to solve this problem and avoid this deadlock? – user3078383 Mar 3 '17 at 2:27
  • @user3078383 you should look at using SERIALIZEABLE mode and then retrying rather than requesting exclusive locks. – Evan Carroll Mar 3 '17 at 2:28
  • @user3078383 don't forget to mark this answer as chosen if it works for you – Evan Carroll Mar 3 '17 at 2:29
  • Thanks for you help @EvanCarroll, but to use SERIALIZABLE mode instead exclusive locks I need to do many changes in my SQL Code and application. And at this time I can't do it, cause my system have tens of thousands transactions per day, and these changes can do unknown problems. – user3078383 Mar 3 '17 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.