Re: application generated an eternal block in the database

"Hu, Patricia" <Patricia(dot)Hu(at)finra(dot)org> 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