what transaction behavior should we expect if the client dies?

BEGIN;

UPDATE saving SET balance = balance - 100.00 WHERE name = 'Alice';

UPDATE checking SET balance = balance + 100.00 WHERE name = 'Alice';

COMMIT

During this transaction, my client dies before commit. what would I expect the DB's state?

Let me use a concrete example from http://java.sun.com/developer/onlineTraining/Programming/JDCBook/bmp4.html

The example shown at the end of the page has the logic of either "commit" or "rollback" 开发者_如何学Pythonexplicitly. But what would happen if the client process dies right after the operation:

" AuctionItem ai= home.create(seller, description, auctiondays, startprice, summary);"

the client has not got the chance to catch exception to rollback, nor has it chance to do commit.

Eventually the tcp keep alive mechanism in the OS will kill the connection. when it does the database will roll back the transaction, as it's the only possible choice without an explicit commit. While the connection is alive and idle, you will have a lock on the data and an entry in the pg_stat_activity table indicating "idle in transaction". Any updates by another connection to the rows your transaction has a lock on will wait for it to commit / rollback.

Default tcp_keepalive timeout is 2 hours and some odd minutes for retries.

If there is no auto commit, then the values won't have changed if the DBMS doesn't do a commit