:: Home Page            
 Advertise your Business Here     
Browse  |   Placement Papers  |   Company  |   Code Snippets  |   Certifications  |   Visa Questions
Post Question  |   Post Answer  |   My Panel  |   Search  |   Articles  |   Topics  |   ERRORS new
   Refer this Site  Refer This Site to Your Friends  Site Map  Bookmark this Site  Set it as your HomePage  Contact Us     Login  |  Sign Up                      
Categories >> Software >> Databases >> SQL Server


 Oracle interview questions  Oracle Interview Questions (2041)
 SQL Server interview questions  SQL Server Interview Questions (1082)
 MS Access interview questions  MS Access Interview Questions (25)
 MySQL interview questions  MySQL Interview Questions (194)
 Postgre interview questions  Postgre Interview Questions (6)
 Sybase interview questions  Sybase Interview Questions (22)
 DB Architecture interview questions  DB Architecture Interview Questions (5)
 DB Administration interview questions  DB Administration Interview Questions (213)
 DB Development interview questions  DB Development Interview Questions (54)
 SQL PLSQL interview questions  SQL PLSQL Interview Questions (776)
 Databases AllOther interview questions  Databases AllOther Interview Questions (107)
What is ACID Property of Transaction?
 Question Submitted By :: SQL-Server
I also faced this Question!!     Answer Posted By  
# 1
Is This Answer Correct ?    77 Yes 9 No
# 2
Basically a Transaction is a Series of work..which either
complete successfully or failed in Group.
there is ACID proerty in this
ACID stands for
A - Atomicity
C - Consistancy
I - Isolation
D - Durability
Is This Answer Correct ?    48 Yes 2 No
Dharmendra K. Dixit
# 3
Atomicity:The work cannot be broken into smaller
parts.Although a transaction might contain many SQL
statements,it must be run as all-or-nothing
proposition,which means that,if a transaction is only
partially complete when an error occurs,the work revertss
to its state prior to the start of the transaction.

Consistency:A transaction must operate on a consistent view
of the data and also leave the data in a consistency
state.Any work in progress must not be visible to other
transactions until the transaction has been committed.

Isolation:A transaction should appear to be running by
itself,the effects of other ongoing transactions must be
invisible to this transaction,and the effects of this
transaction must be invisible to other ongoing transaction.

Durability:When the transaction is committed,it must be
persisted so it is not lost in the event of a power
failure.Only committed transaction are recovered during
power-up and crash recovery;uncommitted work is roll back.
Is This Answer Correct ?    30 Yes 6 No
Rahul Mankumare
# 4

Atomicity requires that database modifications must follow
an all or nothing rule. Each transaction is said to be
atomic if when one part of the transaction fails, the entire
transaction fails and database state is left unchanged. It
is critical that the database management system maintains
the atomic nature of transactions in spite of any
application, DBMS, operating system or hardware failure.

An atomic transaction cannot be subdivided, and must be
processed in its entirety or not at all. Atomicity means
that users do not have to worry about the effect of
incomplete transactions.

Transactions can fail for several kinds of reasons:

1. Hardware failure: A disk drive fails, preventing some
of the transaction's database changes from taking effect
2. System failure: The user loses their connection to the
application before providing all necessary information
3. Database failure: E.g., the database runs out of room
to hold additional data
4. Application failure: The application attempts to post
data that violates a rule that the database itself enforces,
such as attempting to create a new account without supplying
an account number

[edit] Consistency

The consistency property ensures that the database remains
in a consistent state; more precisely, it says that any
transaction will take the database from one consistent state
to another consistent state.

The consistency property does not say how the DBMS should
handle an inconsistency other than ensure the database is
clean at the end of the transaction. If, for some reason, a
transaction is executed that violates the database’s
consistency rules, the entire transaction could be rolled
back to the pre-transactional state - or it would be equally
valid for the DBMS to take some patch-up action to get the
database in a consistent state. Thus, if the database schema
says that a particular field is for holding integer numbers,
the DBMS could decide to reject attempts to put fractional
values there, or it could round the supplied values to the
nearest whole number: both options maintain consistency.

The consistency rule applies only to integrity rules that
are within its scope. Thus, if a DBMS allows fields of a
record to act as references to another record, then
consistency implies the DBMS must enforce referential
integrity: by the time any transaction ends, each and every
reference in the database must be valid. If a transaction
consisted of an attempt to delete a record referenced by
another, each of the following mechanisms would maintain

* abort the transaction, rolling back to the consistent,
prior state;

* delete all records that reference the deleted record
(this is known as cascade delete); or,

* nullify the relevant fields in all records that point
to the deleted record.

These are examples of Propagation constraints; some database
systems allow the database designer to specify which option
to choose when setting up the schema for a database.

Application developers are responsible for ensuring
application level consistency, over and above that offered
by the DBMS. Thus, if a user withdraws funds from an account
and the new balance is lower than the account's minimum
balance threshold, as far as the DBMS is concerned, the
database is in a consistent state even though this rule
(unknown to the DBMS) has been violated.
[edit] Isolation

Isolation refers to the requirement that other operations
cannot access or see data that has been modified during a
transaction that has not yet completed. The question of
isolation occurs in case of concurrent transaction, (i.e.
transaction occurring at the same time) and with the same
database. To preserve the database consistency, the need of
isolation courses. Each transaction must remain unaware of
other concurrently executing transactions, except that one
transaction may be forced to wait for the completion of
another transaction that has modified data that the waiting
transaction requires. If the isolation system does not
exist, then the database may remain in an inconsistent
state. This may happen as in case of concurrent transaction,
one transaction may leave some data-items in mid process and
at the same time another concurrent transaction may try to
access/alter the same data-item which may cause data
inconsistency and may leave the database in an inconsistent
[edit] Durability

Durability is the ability of the DBMS to recover the
committed transaction updates against any kind of system
failure (hardware or software). Durability is the DBMS's
guarantee that once the user has been notified of a
transaction's success, the transaction will not be lost. The
transaction's data changes will survive system failure, and
that all integrity constraints have been satisfied, so the
DBMS won't need to reverse the transaction. Many DBMSs
implement durability by writing transactions into a
transaction log that can be reprocessed to recreate the
system state right before any later failure. A transaction
is deemed committed only after it is entered in the log.

Durability does not imply a permanent state of the database.
A subsequent transaction may modify data changed by a prior
transaction without violating the durability principle.
Is This Answer Correct ?    19 Yes 3 No
# 5
Transaction is a logical unit of sql commands which are
performed in a singlge step.

If any of the steps is failed then the transaction is not
completed and all the steps are rolled back automatically.

Howeveer a transaction must follow the ACID properties.

A: Atomicity: All the steps an a transaction must execute
in a single group.

C: Consistency: Transaction should be consistence in the
database i.e. database after the transaction should be in
consistence state, it should not damage the DB.

I:Isolation: one transaction should not be affected by
other transactions.

D;Durability: Transaction should be durable even after
system crash...
Is This Answer Correct ?    11 Yes 1 No
# 6
Transcation is a sequene of operations performaed a single
logical unit of work .it must be excuted acid properties.
Atomicity : set of t-sql statements act as a batch
constitancy : ---
isolation : to reduce the concurrency
durabality : once chages are commied database permentaly
Is This Answer Correct ?    14 Yes 6 No
# 7
Atomicity means either the transaction is complete or it it will go on its previous condition
consistency :- it means that after and before the transaction process the data remains in consistent manner;
Isolation :- if two transaction process than each transaction is unaware to both of them
Durability :-means when the transaction is committed .it must be durable even the system is crash..
Is This Answer Correct ?    5 Yes 2 No
# 8
Adding to what POO said

Automicity : Group of T-SQL stmts act in a batch .

Consistency : All or none concept.

Isolation: Visibility of resources used in one transaction
to concurrent transactions
Durability : Once changes are commited to database they are
permanent .
Is This Answer Correct ?    36 Yes 37 No
# 9
All changes to data are performed as if they are a
single operation. That is, all the changes are performed, or
none of them are.

For example, in an application that transfers funds from
one account to another, the atomicity property ensures that,
if a debit is made successfully from one account, the
corresponding credit is made to the other account.

Data is in a consistent state when a transaction starts
and when it ends.

For example, in an application that transfers funds from
one account to another, the consistency property ensures
that the total value of funds in both the accounts is the
same at the start and end of each transaction.

The intermediate state of a transaction is invisible to
other transactions. As a result, transactions that run
concurrently appear to be serialized.

For example, in an application that transfers funds from
one account to another, the isolation property ensures that
another transaction sees the transferred funds in one
account or the other, but not in both, nor in neither.

After a transaction successfully completes, changes to
data persist and are not undone, even in the event of a
system failure.

For example, in an application that transfers funds from
one account to another, the durability property ensures that
the changes made to each account will not be reversed.
Is This Answer Correct ?    2 Yes 3 No
Patil Madhav
# 10
Transaction is a one or more database operation that is
execute logical unit of work
Acid properties
Is This Answer Correct ?    7 Yes 14 No
Sandeep Kumar

Other SQL Server Interview Questions
  Question Asked @ Answers
What is a view?   6
What are different types of Keys? Please explain all the keys with a suitable example.   4
Write a query to get all details of employee who has maximum salary from employee table Genpact 3
What is a trigger?   4
Can we have more than one NULL in a column having unique constraint? 247Customer 11
hi friends please answer this question ASAP:- how to count the no. of employee in a each department or no. of employee in each location by using emp/dept table Indecomm 6
1.what is stored procedure?Its significance with example? 2.Explain about index with syntax and example? plz do reply.........   2
What will you do If the low Performance occurs?   2
Diff. b/w Full Outer Join And Cross Join?   3
What does the INSTEAD OF trigger do?   3
What is mean by "fill factor" ? and what is mean by "Index "in sql? Logica-CMG 1
Get Current System Date Time   2
For more SQL Server Interview Questions Click Here 

Copyright Policy  |  Terms of Service  |  Articles  |  Site Map  |  RSS Site Map  |  Contact Us
Copyright © 2013  All Rights Reserved.   ::