SQL : Transactions and ACID properties.

Transacation
Transacation

SQL : Transactions and ACID properties.

Welcome to CodeSpread!

There are always two point of views of transactions. First, the user point of view, for example, customer went to an ATM to retrieve some amount. For him, the transaction might look like,

Transacation

For a developer or admin, the transaction will be,

DevTransaction

But did we estimate any failure condition in both of the cases shown above?

No.

So these cases do not follow the basic expectation of transaction that is to save from any kind of failure. Now, with our experience, we can define that Transaction is an ‘Expectation’ of a user and a developer. ‘Expectation of success’.

We also have a theoretical definition, which is very good for interview purpose and available everywhere on different sites.

“ Database transaction is collection of SQL queries which are fired sequentially to perform a single unit of work. To succeed, all queries should successfully run. If the sequences breaks with a failed query, then the unit of work will not happen and all the previous successfully run queries will also revert back.”

Here the aim is to successfully run the task or not run it at all.

To achieve this operation, all the best practices/concepts available were merged and laid the foundation of ACID rules, which should be followed to achieve any kind of successful transaction.

What are ACID properties or concepts?

First of all, they are concepts and not implementations. There are three part of implementation: Transaction, Commit and RollBack.

A=Atomicity : A single unit of work has to be atomic, either all steps of transaction completes so its a success or none of them like it never began. Failure is not an option.

To achieve a successful transaction, Commit statement should fire. or if any step fails, then a RollBack statement should fire, like the transaction never began.

C=Consistency : Consistency is more a related term and requires for a valid state of the database. A unit of work forms a transaction but there might be dependency which follows after a successful transaction or are beyond that unit of work. For example, a customer having multiple accounts, transfers money from his one account to other. In one case, the account was deducted the requested amount but other account was not updated with the new amount. It will leave the database in half finished state, which is not a desirable state. So database should remain consistent after a transaction.

I=Isolation : This concept is more related to exclusivity of transactions. When a transaction is happening, this transaction should have exclusive rights to perform the transaction. No other transaction should interfere with this transaction. To implement the concept, we can use Isolation levels in database.

D=Durability : This concept guarantees the availability of data after the transaction. It can be use of database backups and transaction logs that facilitate the restoration of committed transactions or hardware fail safe methods. The aim is clear which is ‘Never lose any data’.

Transaction Implementation Examples:

There are following commands used to control transactions.

COMMIT: Successful Transaction.


BEGIN TRANSACTION

UPDATE Savings SET min_Amount=500

COMMIT TRANSACTION

ROLLBACK: No transaction if any step fails.


BEGIN CATCH

<..........>

ROLLBACK TRANSACTION

END CATCH

SAVEPOINT: Points to save the transaction before any ROLLBACK. No rollback of whole transaction.


SAVEPOINT SAVEPOINT_NAME;

//and

ROLLBACK TO SAVEPOINT_NAME; // to rollback till savepoint, created earlier.

SET TRANSACTION: Sets characteristics of a transaction.


SET TRANSACTION [ READ WRITE | READ ONLY ];

Sample:

A sample format:


BEGIN TRY

BEGIN TRANSACTION

<..........>

COMMIT TRANSACTION

END TRY

BEGIN CATCH

<..........>

ROLLBACK TRANSACTION

END CATCH

Next in line: Isolation Levels

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

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

More from CodeSpread:

  • SQL fry: PIVOT and UNPIVOTSQL fry: PIVOT and UNPIVOTWhat is PIVOT? In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIV...
  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • SQL:Target a SubQuerySQL:Target a SubQueryPeople ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery. Before discussing SubQuery, there are few things which we should know lik...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • Styles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptStyles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptDevelopers take months to write beautiful pieces of code and reviewer gets only few hours or I say, minutes to provide suggestions and improvisations. Then it becomes a hard deal for a developer to...
  • Transact-SQL : Try-Catch BlockTransact-SQL : Try-Catch BlockSimilar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors. A TRY-CA...