ACID Properties

Property of transaction

There are 4 property of Transaction (Also known as ACID properties)

1.       Atomicity – All or Nothing transaction

2.       Consistency – Guarantees committed transaction state

3.       Isolation – Transactions are independents

4.       Durability – Committed data never lost

Atomicity

The phrase "all or nothing" succinctly describes the first ACID property of atomicity. All statement in the transaction either completed successfully or they were all rolled back. The task is that the set of operation represent either accomplished or not, but in any case not left half done.

Ø  Transaction is a unit of operation - either all the transaction's actions are completed or none are

Ø  It is maintained in the presence of deadlocks

Ø  It is maintained in the presence of database software failures

Ø  It is maintained in the presence of application software failures

Ø  It is maintained in the presence of CPU failures

Ø  It is maintained in the presence of disk failures

Ø  It can be turned off at the system level

Ø  It can be turned off at the session level

Atomicity is maintain by Transaction management component

Transaction Control

Below is the transaction control command

1.       Commit – To save the changes

2.       Rollback – To Rollback the changes

3.       Save point – Creates points within groups of transactions in which to ROLLBACK

4.       Set transaction – Place the name of the transaction

See the example

I have a table and want to update the records. Here I am using transaction if my sql command is executed successfully then the transaction will be committed else it will be the rollback.

Now I am using the Transaction

Begin try Begin transaction trans update Emp set name='Ramesh' where ID=2 Commit Print 'Transaction Committed successfully' End Try Begin Catch Rollback transaction print 'Transaction Rollback' End catch

The update statement executed successfully so the transaction is committed.

Now I am changing the update statement and it is incorrect update statement which is cause of error.

ID is Int but I am passing the string, it is cause of failure.

Begin try Begin transaction trans update Emp set name='Ramesh kumar' where ID='Raj' Commit Print 'Transaction Committed successfully' End Try Begin Catch Rollback transaction print 'Transaction Rollback' End catch

I mean record is not updated.

Consistency

Consistency is the ACID property that ensures that any changes to values in an instance are consistent with changes to other values in the same instance. A consistency constraint is a predicate on data which serves as a precondition, post-condition, and transformation condition on any transaction.

Isolation

The isolation portion of the ACID Properties is needed when there are concurrent transactions. Concurrent transactions are transactions that occur at the same time, such as shared multiple users accessing shared objects. This situation is illustrated at the top of the figure as activities occurring over time. The safeguards used by a DBMS to prevent conflicts between concurrent transactions are a concept referred to as isolation.

Isolation is managed by Concurrency Control Management.

Concurrency Control Management

Ø  Lock Based Protocol

o   Binary lock

o   Shared(read)/Executive(write) lock

Ø  Time based lock

Durability

Maintaining updates of committed transactions is critical. These updates must never be lost. The ACID property of durability addresses this need. Durability refers to the ability of the system to recover committed transaction updates if either the system or the storage media fails. Features to consider for durability

Durability is managed by Recovery Management.

Ø  Recovery to the most recent successful commit after a database software failure

Ø  Recovery to the most recent successful commit after an application software failure

Ø  Recovery to the most recent successful commit after a CPU failure

Ø  Recovery to the most recent successful backup after a disk failure

Ø  Recovery to the most recent successful commit after a data disk failure