Committing a transaction early, within a transaction ?

[khkiley]

For similar reasons to #1517, I need to acquire a single connection.

I have a large number of inbound streaming records I need to upsert, and would like to commit them at intervals so if something fails along the way (like on record 999999 of 1000000) the process doesn't need to start from scratch.

Effectively, I'm looking to do something like this:

const transaction = new sql.Transaction(/* [pool] */)
await transaction.begin()

request = new Request(transaction)

await request.query('create the #tmp table')
await request.query('COMMIT TRAN')
await request.query('OPEN TRAN')
await request.query('Insert some stufff into #tmp table')
await request.query('Upsert stuff from #tmp table to destination table')
await request.query('COMMIT TRAN')
await request.query('OPEN TRAN')
await request.query('Insert some more stufff into #tmp table')
await request.query('Upsert more stuff from #tmp table to destination table')
await request.query('COMMIT TRAN')
await request.query('OPEN TRAN')
await request.query('drop the #tmp table')
await request.query('COMMIT TRAN')
await request.query('OPEN TRAN')

await transaction.commit()

I'm assuming hoping, the connection remains steading through all those manual commit/opens, and as long as there is an open transaction when I issue the transaction.commit(), everything should be ok.

What could go wrong with this approach ?

Thanks,

Kurt

[dhensby]

What a novel approach, yep - that should work. I can't think of any immediate issues with doing that.

[khkiley]

@dhensby Excellent! Thank you for the fast reply.

Does the connection remain with the transaction after an error? I would need to clean up and drop the temporary table.

Or is there a level of cleanup that naturally happens when a connection is returned, and reissued from the pool?

Thanks,

Kurt

[dhensby]

The connection will remain until commit or rollback is called unless you've manually set automatic rollback on error on the connection.

[khkiley]

This looks like it is working well!