"Hu, Patricia" <[email protected]> writes:
> 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.
Actually, if I understand you correctly, it *is* a deadlock, but one that
the database cannot detect because one of the waits-for relationships is
internal to the application. The database can see that session 2 is
waiting for session 1 to complete and release the tuple lock, but it has
no way to know that on the application side session 1 is waiting for
session 2. So no error is reported, and everything just sits.
AFAICS, this is simply broken application design. There's no such thing
as a second connection being able to update a row "within the context"
of a first connection's transaction.
regards, tom lane
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
