What if a connection closes in the middle of a commit?

Posted on

Question :

I have a few large CSV files to upload to an instance of SQL Server.(Nope, can’t use SSMS)
I’m using JDBC to read the CSVs line by line and insert the lines in a table in batches.
Supposing my code is as such:

val connection = DriverManager.getConnection(<urls and credentials>)
connection.setAutoCommit(false)
val stmt = connection.prepareStatement("insert into blah_table values(?,?,?,?,?,?,?)")
while(<csvReader has next>)
{
    //populate the stmt with values
    stmt.setObject(x,y)
    // add a batch
    stmt.addBatch()
}
stmt.executeBatch()
connection.commit()

Say the CSV file had some 20000 lines with some 50-60 or more columns.
I encountered an error by chance where the connection to SQL Server was lost while connection.commit() was being executed.
This resulted in ~11000/20000 lines being inserted.
I went through my application several times hoping to find some error I might have made. Everything appears to be fine.
My question is, is it possible that if the connection is lost in between committing, partial commits are left inserted?
If so, what could be a way out to save from partial commits?

Answer :

Short Answer

Add exception handling to ensure a rollback happens on error.

try {
    <code to execute>
} catch (SQLException ex) {
    connection.rollback();
} finally {
    connection.commit();
}

Explanation

if autocommit is set to false, and no connection.rollback() was issued before the program exits, the drivers call an automatic connection.commit() before it closes the connection.

Leave a Reply

Your email address will not be published. Required fields are marked *