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 seemed to be doing this:
Start a transaction, update row1 in table1, then spawn another process to update the same row in the same table (but within the context of this 1st transaction?). The result is that the 2nd process was blocked waiting for the lock on the 1st transaction to complete, but the 1st transaction can't complete either b/c the 2nd update was blocked. It wasn't a deadlock situation - neither was rolled back, just more and more locks lined up for that table, till manual intervention by killing the blocker or blocked pid. What I saw in the database when this blocking was happening seems pretty standard: the 1st update holds a RowExclusiveLock on the table, there is an ExclusiveLock on the tuple of the table, another ExclusiveLock on the transactionID of 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 from the database end: if I have 2 sessions doing update on a same row in same table, after session 1 commits/rolls back the blocking is resolved. In psql if 2 updates on the same row on the same table within the same transaction, on commit psql keeps 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 legally privileged information. If you are not an intended recipient or an authorized agent of an intended recipient, you are hereby notified that any dissemination, distribution or copying of the information contained in or transmitted with this e-mail is unauthorized and strictly prohibited. If you have received this email in error, please notify the sender by 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 part of the contents to any other person. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
