ACID Properties
A transaction is a unit of work in a database that typically contains
several commands that read from and write to the database. The most well-known
feature of a transaction is that it must complete all of the commands in their
entirety or none of them. This feature,
called atomicity, is just one of four properties defined in the early days
of database theory as requirements for a database transaction, collectively
known as ACID properties.
Transaction:
Set of T-SQL statements which have some dependencies, whenever changes
happened this changes should effect on all dependencies of data. Transaction should process all ACID
properties. Then only changes move from
ldf to mdf files.
The four
required properties of a database transaction are atomicity, consistency,
isolation, and durability.
Atomicity means that all the effects of the transaction must complete
successfully or the changes are rolled back. A classic example of an atomic
transaction is a withdrawal from an ATM machine; the machine must both dispense
the cash and debit your bank account. Either of those actions completing
independently would cause a problem for either you or the bank.
(Atomicity is a
feature of databases systems dictating where a transaction must be
all-or-nothing. That is, the transaction must either fully happen, or not
happen at all. It must not complete partially.)
(SQL Server should execute all
statements inside transaction at once. There is no partial execution of transactions.
Whatever statement given has to be executed full or none of the statements has
to be executed.)
The consistency requirement
ensures that the transaction cannot break the integrity rules of the database; it must leave the database in a consistent state. For example, your
system might require that stock levels cannot be a negative value, a spare part
cannot exist without a parent object, or the data in a sex field must be male
or female. In order to be consistent, a transaction must not break any of the
constraints or rules defined for the data.
(Whenever
any change (modification) happened on parent table it automatically reflect on
dependent child table.)
Isolation refers to keeping the changes of incomplete transactions
running at the same time separate from one another. Each transaction must be
entirely self-contained, and changes it makes must not be readable by any other
transaction, although SQL Server does allow you to control the degree of
isolation in order to find a balance between business and performance
requirements.
(Isolation
means independent. One transaction do not depend on other transaction. Users
able to access multiple transactions at a time.)
Once a transaction is committed, it must persist
even if there is a system failure — that is, it must be durable. In SQL Server,
the information needed to replay changes made in a transaction is written to
the transaction log before the transaction is considered to be committed
(Once any transaction is updated it should be a permanent
change in .mdf file.)
No comments:
Post a Comment