Benefits Of Explicit Transactions

Kendra Little talks about explicit transactions and when they’re useful for single-statement operations:

If you do not enable implicit transactions, and you don’t start an explicit transaction, you are in the default “autocommit” mode.

This mode means that individual statements are automatically committed or rolled back as whole units. You can’t end up in a place where only half your statement is committed.

Our question is really about whether there are unseen problems with this default mode of autocommit for single-statement units of work.

By force of habit, I wrap data modification operations in an explicit transaction.  They let me test my changes before committing and the time you’re most likely to spot an error seems to be right after hitting F5.

Related Posts

Phantom Reads

Arun Sirpal sees not-quite-there-yet transactions: With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction. I will show you what this looks like with an example. Please […]

Read More

Schema-Only Optimized Tables Can Still Roll Back

Chris Adkin investigates whether schema-only memory-optimized tables are logged and whether they support transactions the way other tables do: The statement “There is zero logging when DURABILITY=SCHEMA_ONLY” is not factually correct, its more like a minimally logged operation. What is surprising is the fact that logged as advertised for the in-memory engine should result in […]

Read More


February 2018
« Jan Mar »