Understanding Atomic Data Transaction in SQL

Understanding Atomic Data Transaction in SQL

1.What is Atomicity in SQL?

In atomic transactions, the operations that comprise the transaction are either all committed or all aborted.

Atomicity is one of the ACID (atomicity, consistency, isolation, durability) transaction properties.

The atomicity of a transaction is achieved by wrapping the individual operations within the transaction in a begin-commit block, or in the case of a failure, a begin-rollback block.

For example, consider a banking transaction that transfers $100 from account A to account B. This transaction has four steps:

- Read A
- Decrement A
- Increment B
- Write B

If the transaction is interrupted after step 2, then account A is debited but account B is not credited. The transaction is left in an inconsistent state.

The atomicity property ensures that the transaction will either complete all four steps or none of them, leaving the database in a consistent state.

# Types of Atomicity

There are two types of atomicity are available in SQL.

- Statement-level Atomicity
- Transaction-level Atomicity

* Statement-level Atomicity:

Statement-level Atomicity is the only level of atomicity that is supported by
the SQL standard.
A statement-level atomic transaction is one where each SQL statement is treated
as a single atomic transaction.
If a statement fails, the entire transaction fails and all changes are rolled
back.
For example, if you have an INSERT statement that tries to insert a duplicate
primary key, the entire statement will fail and no rows will be inserted.
Statement-level atomicity is the minimum level of atomicity that you will get
with a database.

* Transaction-level Atomicity:

Transaction-level atomicity is where a group of statements is treated as a
single atomic transaction.
If one statement fails, the entire transaction fails and all changes are rolled
back.
For example, if you have a transaction that consists of two INSERT statements
and the second one fails, then both statements will be rolled back.
Transaction-level atomicity requires that your database have support for
transactions.

How does SQL Server implement Atomicity?

SQL Server implements atomicity using the write-ahead log.

The write-ahead log is a feature of SQL Server that logs every change that is made in the database.SQL Server only logs the changes, not the entire row. This improves performance. The write-ahead log is stored in the transaction log.

The transaction log is managed by SQL Server. You can accidentally remove all the data from a database, but you can't remove the transaction log.SQL Server also maintains a checkpoint file.The checkpoint file records the last transaction that was committed and applied to the database.When SQL Server starts, it uses the checkpoint file to restore the database to the last committed state.

When SQL Server starts, the SQL Server engine reads the changes in the transaction log and applies them to the database files.When a transaction is committed, the transaction is written to the transaction log and the checkpoint file is updated.

The checkpoint file and transaction log are also used to recover the database after a crash.If SQL Server crashes, the SQL Server engine will restore the database during the startup process.If the last committed transaction is still in the transaction log, the SQL Server engine will apply it to the database.

If the last committed transaction is not in the transaction log, the SQL Server engine will restore the database using the last backup and all the transaction log backups.

3.How we can implement Atomicity in MicroSoft SQL?

MS SQL uses the concept of transactions to ensure atomicity. A transaction is a unit of work which is either completed in its entirety or not completed at all.

To implement atomicity, MS SQL uses a system known as the Transaction Log. This is a record of all the transactions that have been performed, and can be used to undo any transactions that were unsuccessful.

In order to begin a transaction, the following command can be used:
BEGIN TRANSACTION

Once a transaction is started, it needs to be completed by either rolling back or committing the transaction.

To roll back a transaction:
ROLLBACK TRANSACTION

To commit a transaction:
COMMIT TRANSACTION

When a transaction is committed, the changes performed by the transaction are written to the database. When a transaction is rolled back, the changes are undone.

If a transaction fails, the transaction is rolled back automatically.

There is also a BEGIN TRAN statement which is equivalent to BEGIN TRANSACTION.

Here are some examples of transactions:

BEGIN TRAN
INSERT INTO TableName VALUES ('Value1', 'Value2')
COMMIT TRAN

BEGIN TRANSACTION
INSERT INTO TableName VALUES ('Value1', 'Value2')
ROLLBACK TRANSACTION

BEGIN TRAN
INSERT INTO TableName VALUES ('Value1', 'Value2')
ROLLBACK TRAN

BEGIN TRAN
INSERT INTO TableName VALUES ('Value1', 'Value2')
ROLLBACK

The last example will roll back the transaction as the ROLLBACK statement does not include the transaction name.

If you want to set the transaction name, you can use the following example:
SET TRANSACTION NAME 'TransactionName'

You can also set the isolation level of a transaction, which is the way that data is read and locked during the transaction.

The isolation level can be set using the following example:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

A transaction can also have a savepoint. This is a point in the transaction that can be rolled back to.

To set a savepoint, the following can be used:
SAVE TRAN 'SavepointName'

To rollback to a savepoint, the following can be used:
ROLLBACK TRAN 'SavepointName'

To rollback a transaction to the beginning, the following can be used:
ROLLBACK TRAN

4.How we can implement Atomicity in MySQL?

We can implement atomicity in MySQL using following ways :

1. By using transactions :
We can use transactions to ensure atomicity.

Syntax :
BEGIN TRANSACTION;
// operations
COMMIT;
or
ROLLBACK;

When we execute above code, either all operations take place or none.
If any error occurs while executing operations then ROLLBACK will be
executed otherwise COMMIT will be executed.

2. By using Savepoints :
We can use savepoints to ensure atomicity.

Syntax :
BEGIN TRANSACTION;
// operations
SAVEPOINT sp1;
// operations
ROLLBACK TO sp1;
COMMIT;

In above code, either all operations take place or none.
If any error occurs while executing operations after SAVEPOINT then
ROLLBACK TO sp1 will be executed.

3. By using SET AUTOCOMMIT command :
By default, MySQL has AUTOCOMMIT enabled.
We can change this behavior by using SET AUTOCOMMIT command.

Syntax :
SET AUTOCOMMIT = 0;
// operations
COMMIT;
or
ROLLBACK;

4. By using ACID model :
We can use ACID model to ensure atomicity.

Syntax :
// connection
$connection = mysqli_connect(host,user,password,database);

// if connection fails
if(!$connection){
echo "Connection failed";
}
else{
// disable autocommit
mysqli_autocommit($connection,FALSE);

// write queries
$query = "INSERT INTO table_name VALUES('value1','value2')";
$result = mysqli_query($connection,$query);

// commit if all queries are executed successfully
if($result){
mysqli_commit($connection);
}
else{
// rollback if any query fails
mysqli_rollback($connection);
}

// enable autocommit
mysqli_autocommit($connection,TRUE);
}

5. How we can implement Atomicity in Oracle SQL?

how oracle implement atomicity we will discuss this given an example .
Suppose, we have a table **employee** and it has columns **empid**, **empname** and **empsalary**.
We want to update the **empsalary** of an employee whose **empid** is 100 from 10000 to 20000.
Now, if we want to make this update operation as atomic, then we can do this in two ways:

**(1)**

begin
update employee set empsalary = 20000 where empid = 100;
commit;
end;

**(2)**

begin
update employee set empsalary = 20000 where empid = 100;
if(sql%rowcount != 1) then
raise_application_error(-20001,'Error: Updated Salary is not equal to 1');
end if;
commit;
end;

In the first method, we just update the **empsalary** of the employee whose **empid** is 100 and then commit the transaction.
In the second method, we update the **empsalary** then we check whether the number of updated rows is 1 or not.

If the number of updated rows is not 1, then we raise an exception and the transaction is rolled back.

If the number of updated rows is 1, then the transaction is committed.

Now, if we run the following query:

select empsalary from employee where empid = 100;

then the result will be 20000 in both cases.

6. how to achieve Application level vs database level atomic transaction?

Application level atomic transaction

An application-level atomic transaction consists of several database transactions that are combined into a single logical unit of work.
To the end user (or another application), the entire set of transactions appears to be a single application-level transaction.
When an error occurs, all of the work performed by the unit of work is discarded.
An application-level atomic transaction is implemented by using the savepoint, rollback, and commit statements.

For example, an online banking application might allow a user to transfer money from one account to another.
This transfer requires two statements: one to subtract the amount from the sender's account, and one to add the amount to the recipient's account.
The entire transaction must appear to the user (or another application) as a single unit of work.
If the subtract operation succeeds but the add operation fails, the transfer must be reversed.

 

Database Level Atomic Transaction

Database transactions are groups of SQL statements that are executed as a single unit.
The statements within the transaction may be DML statements (INSERT, UPDATE, and DELETE), DDL statements (CREATE, ALTER, and DROP), or DCL statements (GRANT and REVOKE).

A transaction is made up of two or more SQL statements that must be executed as a group.
For example, a transaction may consist of a credit operation and a debit operation.
Either both operations are performed or neither operation is performed.

The following example illustrates a transaction that consists of two INSERT statements:

BEGIN TRANSACTION;

INSERT INTO sales_person (id, first_name, last_name, address, phone)
VALUES (1, 'John', 'Smith', '123 Main St.', '(123) 456-7890');

INSERT INTO sales_person (id, first_name, last_name, address, phone)
VALUES (2, 'Jane', 'Smith', '256 Main St.', '(123) 456-7890');

COMMIT TRANSACTION;

7.Conclusion :
In this article, We discussed the basics of atomicity, types of atomicity and how SQL Server implements atomicity.Atomicity is very important in data transactions. It ensures data integrity.

In today's distributed application environment, many of the databases that we use have support for transactions and full atomicity in transactions.

Support for transactions and transactions ensures data integrity.

Thank you for reading.