I recently came across an interesting locking/blocking situation in a Postgres database(9.5.4, RDS but that shouldn't
matter).The application is java/hibernate/springboot with connection pooling. The developers pushed in some code that
seemedto be doing this:
Start a transaction, update row1 in table1, then spawn another process to update the same row in the same table (but
withinthe context of this 1st transaction?). The result is that the 2nd process was blocked waiting for the lock on the
1sttransaction to complete, but the 1st transaction can't complete either b/c the 2nd update was blocked. It wasn't a
deadlocksituation - neither was rolled back, just more and more locks lined up for that table, till manual intervention
bykilling the blocker or blocked pid.
What I saw in the database when this blocking was happening seems pretty standard: the 1st update holds a
RowExclusiveLockon the table, there is an ExclusiveLock on the tuple of the table, another ExclusiveLock on the
transactionIDof the 1st update, the 2nd update unable to acquire a ShareLock on the transactionID (granted=f).
I am trying to understand how could the application have caused this forever blocking.. I wasn't able to reproduce it
fromthe database end: if I have 2 sessions doing update on a same row in same table, after session 1 commits/rolls back
theblocking is resolved. In psql if 2 updates on the same row on the same table within the same transaction, on commit
psqlkeeps the value of the 2nd update. The developers couldn't explain thoroughly how the code triggered this either.
I'd like to see if anyone has insight/explanation how this could happen beyond the database boundary in the application
layer.Any input is appreciated!
Thanks,
Patricia
Confidentiality Notice:: This email, including attachments, may include non-public, proprietary, confidential or
legallyprivileged information. If you are not an intended recipient or an authorized agent of an intended recipient,
youare hereby notified that any dissemination, distribution or copying of the information contained in or transmitted
withthis e-mail is unauthorized and strictly prohibited. If you have received this email in error, please notify the
senderby replying to this message and permanently delete this e-mail, its attachments, and any copies of it
immediately. You should not retain, copy or use this e-mail or any attachment for any purpose, nor disclose all or any
partof the contents to any other person. Thank you.
