/SQL

SQL Transactions

Each time I want to write a SQL query that modifies the database I find myself having to look up how to correctly make use of transactions. What better way for me to look this up next time than from my own blog.

A transaction is a unit of work for SQL queries, this allows for ACID (Atomi, Consistent, Isolated, Durable) operations that either fully commit or get rolled back to mantain data integrity. Imagine a couple of statements modifying data in multiple tables that is related. If one of those statements fails you will want to roll back everything and likewise if all succeed you would want to commit.

A simple pattern for this unit of work is as follows

BEGIN TRANSACTION [transaction_1]

  BEGIN TRY

      -- SQL statements

      COMMIT TRANSACTION [transaction_1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [transaction_1]

  END CATCH

Subscribe to Thulani S. Chivandikwa

Get the latest posts delivered right to your inbox

Thulani S. Chivandikwa

Thulani S. Chivandikwa

husband, dad, .net developer, tech and tea lover

Read More