Batch insert behaviour with/without auto commmit?

[JDBC-0]

Question

How does the driver execute batch INSERTs if the connection auto commit is true? The JDBC specification has no standard rule and this "is a driver specific" topic. The documentation https://learn.microsoft.com/en-us/sql/connect/jdbc/performing-batch-operations?view=sql-server-ver16 has no hint for this - so how can we be sure that our batch inserts are performed as expected (as batches - and not as single INSERTs) - if auto commit is true?

[divang]

When autocommit is true:

• Each statement in the batch is treated as an individual transaction.
• As soon as executeBatch() is called, each SQL statement is automatically committed after execution.
• If one statement fails, it won't affect the already executed statements since they are committed immediately.

When autocommit is false:

• The batch is treated as a single transaction.
• None of the statements will be committed until you explicitly call commit().
• If one statement fails, you can roll back the entire transaction, which means none of the statements in the batch will take effect.

Summary: True: Immediate commits for each statement. False: All statements in the batch are treated as one transaction, allowing for rollbacks on failure.

[JDBC-0]

@divang : thanks - but I guess this is not always true, is it? Because it might depend on the URL Parameters like the bulkCopy feature. I guess even if autocommit is true, the bulk copy feature won't commit each single INSERT statement ... right? Does the bulk copy feature always commits all inserted rows at once with 1 single transaction? If not, the bulk copy feature wouldn't have any advantage if every single INSERT statement would be commited. Or am I missing a point?

[machavan]

Batch inserts done using prepared statements with URL parameter useBulkCopyForBatchInsert=true also adhere to transactional semantics. The inserted records are committed upon committing the transaction.

The behavior mentioned above by @divang is consistent with useBulkCopyForBatchInsert=true also

[Jeffery-Wasty]

Hi @JDBC-0,

Please let us know if you have additional questions/comments, or else we will move forward with closing this issue.

[JDBC-0]

Thank you for the clarification. To avoid the same questions again, it would be very helpful to have this info in the driver documentation.

[Jeffery-Wasty]

We'll look into updating docs for the next release.