Question: What are the ACID properties of a database transaction? Atomicity, consistency, isolation and durability describe how a logical unit of work behaves. Expand the acronym, then explain what each property means for the same transaction.
This is one of the oldest questions in the series. I have often heard it when an interviewer is thinking of the next question. It is worth answering well anyway: an application can run on a transactional database and still use transactions incorrectly.
Follow one transfer from start to finish
Suppose a transfer debits one account by 100 and credits another by 100. The business rule is that the transfer does not create or lose money.
- Atomicity: Both updates belong to one transaction. If the operation rolls back, neither debit nor credit remains. Committing the debit separately would defeat that unit of work.
- Consistency: A valid starting state becomes another valid state under the defined rules. Constraints enforce some rules; the application must implement the transfer correctly. A transaction can atomically commit the wrong arithmetic.
- Isolation: Concurrent transactions have controlled interaction. The isolation level determines which changes can be observed and which anomalies are prevented. This does not mean every SQL Server isolation level makes concurrent work behave as fully serial execution.
- Durability: After a fully durable commit, its log records are persisted before success returns. Recovery can preserve committed work after a crash and undo incomplete work.
The distinctions matter
The original consistency definition repeated the all-or-nothing idea from atomicity. Consistency is about preserving the rules, not simply whether both statements finished. The original illustration also said committed data is never lost without stating the durability assumptions.
SQL Server supports delayed durability, where commit can return before the log is hardened. A crash before that flush can lose acknowledged changes. Durability also does not replace backups and recovery planning for damaged or lost storage.
For an interview, I would rather hear a clear transfer example and these boundaries than a memorized standards-section number. The earlier ACID article gives the series background.
Durability options explain the acknowledged-change exception.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
2 Comments. Leave new
Sir, How can I optimize this code? Please help.
if {condition1} = ‘0’
begin
if {condition2} = ‘Yes’
Begin
Set @SQLQuery = @SQLQuery + ‘ AND ‘
end
else
begin
Set @SQLQuery = @SQLQuery + ‘ AND ‘
end
end
==============================================
Optimized Answer :-
===============================================
if{condition1=0}
begin
Set @SQLQuery = @SQLQuery + ‘And’
end
In your if and else clause you are setting executing the same statement “Set @SQLQuery = @SQLQuery + ‘And’ ” so y should we check the the if(Condition = yes)
============================================