SQL Tidbits #1 – Commit (Transaction)

I’ve been busy lately, but not with writing blog posts. I’ve started a new job at an awesome company called HomeAway, moved my family to Austin, and spent much of my “spare” time doing house renovation work. But I wanted to get something out there on the off chance that somebody will gain a useful tidbit from me.

So here is a little tidbit about transactions in SQL Server. I recently came across some code that was wrapped like this.

BEGIN TRANSACTION
…
COMMIT

I’ve always used a COMMIT TRANSACTION so I wondered if you really needed the TRANSACTION part. It turns out COMMIT is not exactly the same as COMMIT TRANSACTION. COMMIT by itself is equivalent to COMMIT WORK. The WORK word is optional. This syntax is SQL-92 compatible. The action of COMMIT by itself does the same thing as COMMIT TRANSACTION. The big difference is when you use named transactions like BEGIN TRANSACTION abc. If you do this you have to use COMMIT TRANSACTION abc. COMMIT by itself, indeed, will not work.

That’s my tidbit for the day.

Bookmark the permalink.

Leave a Reply

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