Row lock conflicts

There is one tricky part left here. At the point the preceding transaction stopped, the first session has an open transaction trying to update the row WHERE s=1. What happens if we try to do something similar in the second session before that's either been committed or rolled back?

$ psql –c "UPDATE t SET i=i+1 WHERE s=1;"

Guess what? This statement will hang! The reason is that we've reached the limits of how much sessions can be isolated from one another safely. Once two sessions are both trying to update the same row, some more complicated rules come into play.

Whenever you try to grab a lock on a specific row, which includes UPDATE, DELETE, and the locks SELECT FOR UPDATE / SELECT FOR DELETE obtain, those have to wait for anyone that already has a lock on that row. Once the original locker completes its work, then some decisions have to be made.

If the transaction holding the lock rolled back, no harm done; the newer one continues...