What happens if we kill the JVM process during transaction execution?

Question

I'm using PostgreSQL 9.4.

What happens if someone kills the JVM process during exectuion transactional batch-update (batch size = 50) when some of the queries in the batch are already executed?

What will be in the database?

Answer 1

Expected behaviour is that the transactions that are not committed, time out on the DB side and are rolled back. Transactions that are committed are, well, committed.

Integrating answers from various comments:

If some have actually executed but not others then it's not a batch. In a transactional batch, either all of them are executed in reality, or none of them are. – Peter Lawrey

Another

Try to imagine this: 1- jvm starts the transaction; 2- database do the process; 3 - jvm sends the commit to end transaction if you kill the JVM no matters what happens in the database it will not run the commit therefore it will rollback – Jorge Campos

Lastly, for PostgreSQL

What will be in the database? -> Everything and only what was successfully committed. Everything else won't make it. (If your "batch" process doesn't use transactions then your DB will probably be in an inconsistent state - at least from the business perspective, because from a strict data perspective, PostgreSQL, as a decent ACID RDBMS, is able to guarantee durability [aka what was committed/inserted remains committed/inserted].) – acdcjunior

Answer 2

There are two situations in which the connection can be: either in auto-commit mode or not in auto-commit mode (by calling Connection#setAutoCommit(false)).

In the first case, when executing a batch of update SQL commands, there could be partial execution of commands, i.e. some commands may be committed and others still not executed. See this quote from the documentation of Statement#executeBatch():

If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands.

When a connection is not in auto-commit mode, then only when a call to Connection#commit is returned can we assume that all submitted commands are committed. Either all or none are committed after this call.