SQL transactions

Once you start learning SQL, no matter which source your take to get you knowledge it is certain that you will not hear about the sql transactions. Only later, when you expand your knowledge you will see that they are pushed in as a very important concept in databases.

This is really odd. Since transactions are actually “embedded” into queries known as implicit sql transactions and are hardly mentioned anywhere when getting basic and even advanced knowledge of sql and databases. In my opinion this is not appropriate because later on you are trying to understand what transactions are but you just don’t get it because everything you can do with transactions can also  be achieved “without them”. Let’s start with some basic explanation on sql transactions, by asking ourself some questions.

What is the accepted and most used definition for an sql transaction?

“sequence of operations performed as a single logical unit of work ” source: http://www.sqlteam.com/article/introduction-to-transactions

What are sql transactions?

A sql transaction is nothing else than sql statements (UPDATE, INSERT, DELETE, SELECT) which are encompassed within some transactional keywords (keywords are later explained). You can look at them as you look at some programming code within the curly braces in programming. For example, code which is put in a function (curly braces between the code) or a code which is put between the class curly braces.

As you can see, transactions are nothing else then pure sql statements which are used to accomplish an action against the database and to store, modify, retrieve or delete some data in the database.

Once again you can consider this equality although there are more things to it but still it is fine to express it in this way: SQL TRANSACTIONS = DATABASE DATA AFFECTED/CHANGED/MODIFIED

Why do we need sql transactions?

(Have a look to the 4 points at the end of the text before reading further explanation)

First, remember that using “plain” sql statements (UPDATE, INSERT, DELETE, SELECT) also represent transactions, the only thing is that this transactions are called implicit transactions (See types of sql transactions below).

We need sql transactions to ensure the 4 points mentioned below which are abbreviated as ACID or to expand a little bit this abbreviation:

  • Atomic – an sql transactions will pass (execute) only if every sql statements inside the transactions passes (executes). A sql transaction will fail only if one statement within the transaction fails. So, to short it out. If only one error (whatever the error is) occurs everything will be considered an error and the transactions gets rejected.
  • Consistency – implies to the changes inside the database. The change happened because of the sql transaction which was executed.
  • Isolation- means that the sql transaction is 100% independent. No prior condition has to be met in order for a transaction to happen.
  • Durability – once the sql transaction executes it stays “forever”. No matter what kind of negative event happens to the database.

I’m still not sure how can I benefit from an sql transaction?

When encapsulating sql statements in an sql transaction you can put the sql statements on “freeze”. This means  that you can execute statements when you want by applying the command COMMIT.  After that, your transaction is done and the data has changed in the database (based on the sql statements).

Now, the most valuable thing what transactions offer is that you can ROLLBACK your action! The ROLLBACK command is used if you want to undo your changes to the database. Using an sql statement without an sql transaction such as INSERT, UPDATE ect.. does not allow you to somehow reverse the changes that happened to the database.

How does an sql transaction look like?

example (mysql)

start transaction
UPDATE Money
SET Purpose='bought a new car'
WHERE ID=7

commit;

example (MSSQL)

Begin Transaction
UPDATE Money
SET Purpose='bought a new car'
WHERE ID=7

Commit Transaction

Which types of sql transactions exist?

There exists two types of sql transaction.

  1. Implicit sql transaction – the one that does not need any special keyword between the sql statement. It actually represents the sql statements itself.  By default an sql transaction is actually the sql statement.
  2. Explicit sql transaction – the one that needs special keywords (depends on the database itself, MySql, SqlServer) which surrounds the sql statements. Use them if you have an intention of reversing the changes which you make

When to use sql transactions?

Use them if you have multiple sql statements which are going to modify the data in the database. Don’t forget that sql transaction are nothing more than plain sql statements (implicit transactions). Based on your needs, decide whether you should use explicit or implicit sql transactions.

When to not use sql transactions?

You really don’t need to use sql transactions if you have ONE sql statements only which should go into your transaction.

What happens with an sql transaction behind the scenes?

First when you execute the sql transaction. The log file is getting info (data is written inside the log file) that a transaction will occur shortly. After that the log file get’s the actual data written to it that an sql statement is in process (executing). Finally the log file get’s the data written to it that the transaction finished.

So 3 things or states are written to the log file:

  • notifying that the transaction will occur/is about to occur
  • sql statement in progress
  • completed sql statement in the transaction

What else should I know about sql transactions?

If you start/begin an sql transaction and you do not specify the finishing command at the end (ROLLBACK COMMIT) others (other connections established) will not see the new change in the database because the data is not in this case committed yet. This rule is covered in the isolation property.

Keywords which are often used in sql transactions :

source:(http://www.tutorialspoint.com/sql/sql-transactions.htm)

  • Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
  • Consistency: ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation: enables transactions to operate independently of and transparent to each other.
  • Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Sql transactions commands:

  • COMMIT – executes the transaction
  • ROLLBACK – reverses the transaction
  • SAFE-POINT – makes a safe-point on an sql statement and once your decide to rollback to a specific safe-point the data will get to the state where it was once the safe-point was created! You can look at it the way Windows System restore function: You decide to create a system restore point today and after 10 months you want to get to the state you were 10 months ago (the day you created the system restore point) You go to this program and comeback to your settings and files and programs which were installed up to that day(of-course it is not really how system restore functions, this more a idealistic example of a system restore).
Advertisements