Sample Header Ad - 728x90

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

0 votes
1 answer
1057 views
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() connection.setAutoCommit(false) val stmt = connection.prepareStatement("insert into blah_table values(?,?,?,?,?,?,?)") while() { //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?
Asked by Kartik Sayani (1 rep)
Apr 23, 2018, 10:41 AM
Last activity: Apr 23, 2018, 11:58 AM