Transaction interview questions answers
In this article I will share some frequently asked
transaction interview questions and answers in SQL server.
1. What is transaction?
Transactions provide a mechanism for grouping a series
of database changes into one logical operation. After changes are made to the
database, these changes can be committed or canceled as a single unit.
2. What is save
transaction and save point?
Creates points within groups of transactions in which
to ROLLBACK.Save points offer a mechanism to roll back portions of
transactions. A user can decide and set a savepoint, within a transaction.
The
savepoint defines a location to which a transaction can return if part of the
transaction is conditionally canceled.
In Detail… http://codechef4u.com/post/2015/04/13/save-transaction-and-savepoint
3. Explain
Transaction Isolation levels in SQL Server?
To ensure quality of your data in RDBMS transaction
isolation level is important.
There are five isolation levels - READ UNCOMMITTED, READ COMMITTED (locking), READ COMMITTED
(snapshot), REPEATABLE READ and SERIALIZATION.
4. What is read
committed?
This
is the default isolation level and means selects will only return committed
data. This prevents dirty reads.
For more
info ….http://www.codechef4u.com/post/2015/04/05/read-committed
5. What is read
UNCMMITED?
Specifies that statements can read rows that have been
modified by other transactions but not yet committed.
For more info …
http://www.codechef4u.com/post/2015/04/05/read-uncommitted
6. What is
snapshot?
Except when a database is being recovered, SNAPSHOT
transactions do not request locks when reading data. SNAPSHOT transactions
reading data do not block other transactions from writing data. Transactions
writing data do not block SNAPSHOT transactions from reading data.
7. What is
REPEATABLE READ?
This
is the default isolation level and means selects will only return committed
data. This prevents dirty reads.
For more
info ….http://www.codechef4u.com/post/2015/04/05/read-committed
8. What is
SERIALIZABLE?
This
isolation level takes Repeatable Read and adds the guarantee that no new data
will be added eradicating the chance of getting Phantom Reads.
For more
info ….http://www.codechef4u.com/post/2015/04/07/serializable
9. What are
Different Types of Locks?
Following are the list of locks used in sql server:
SHARED :
SQL Server uses shared locks for all read operations.
For example a select statement.
UPDATE :
This lock applied on those resources that can be
updated. This lock prevents the common form of dead lock that occurs when
multiple sessions are locking the data so that they can update it later.
EXCLUSIVE :
Used for data-modification operations, such as
INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the
same resource at the same time.
INTENT:
Used to establish a lock hierarchy. The different
types of intent locks are: intent shared, intent exclusive, and shared with
intent exclusive.
SCHEMA:
Used when an operation dependent on the schema of a
table is executing. The different types of schema locks are: schema
modification and schema stability.
BULK UPDATE :
This lock is applied when there is a bulk copying of
data and the TABLOCK is applied.
KEY RANGE :
Protects the range of rows read by a query when using
the serializable transaction isolation level. Ensures that other transactions
cannot insert rows that would qualify for the queries of the serializable
transaction if the queries were run again.
10. What are
Pessimistic Lock and Optimistic Lock?
Pessimistic:
In pessimistic locking a record or page is locked
immediately when the lock is requested.
The disadvantage of pessimistic locking is that a
resource is locked from the time it is first accessed in a transaction until
the transaction is finished, making it inaccessible to other transactions
during that time
Optimistic:
In an optimistic lock the record or page is only locked when the changes
made to that record are updated.
11. What is
Dirty Read?
Reading uncommitted modifications are call Dirty
Reads. Values in the data can be changed and data is changing in the data set
before the end of the transaction, thus getting you incorrect or wrong data.
12. What is
Difference between Commit and Rollback when Used in Transactions?
Commit:
Commit
marks the end of a successful implicit or explicit transaction. When we use Commit in any query then the change made
by that query will be permanent and visible. We can't Rollback after the
Commit.
Rollback (MSDN details):
Rolls
back an explicit or implicit transaction to the beginning of the transaction,
or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to
erase all data modifications made from the start of the transaction or to a
savepoint. It also frees resources held by the transaction.
13. What is
NOLOCK?
When user accessing data, data get locks so that other
user cannot modify or delete data that someone is reading.
WITH (NOLOCK)
Do not issue shared locks and do not honor exclusive locks.
When this option is in effect, it is possible to read an uncommitted
transaction or a set of pages that are rolled back in the middle of a read.
Dirty reads are possible. Only applies to the SELECT statement.
14. What is the
Difference between Update Lock and Exclusive Lock?
Exclusive lock ensures that multiple updates cannot be
made to the same resource at the same time.
Update lock prevents the common form of dead lock that
occurs when multiple sessions are locking the data so that they can update it
later.
15. What is
explicit mode in SQL Server?
An explicit transaction is one in which you explicitly
define both the start and end of the transaction.
16. What is
implicit mode in SQL Server?
When a connection is operating in implicit transaction
mode, the instance of the SQL Server Database Engine automatically starts a new
transaction after the current transaction is committed or rolled back.
17. What is
autocommit mode in SQL Server?
Autocommit mode is the default transaction management
mode of the SQL Server Database Engine. Every Transact-SQL statement is
committed or rolled back when it completes. If a statement completes successfully,
it is committed; if it encounters any error, it is rolled back.
18. What are
“Unrepeatable reads”?
Non repeatable read is a situation where a session
finds itself in when it perform multiple read.
It is possible to perform the query in the same
transaction more than one and show different result.
19. What are
“Phantom rows”?
Phantom row is rows that douse not appear in the
initial read, but appear when same data is read again during the same
transaction.
20. What are
“Lost Updates”?
Lost updates occur when two or more transactions
select the same row and then update the row based on the value originally
selected. Each transaction is unaware of other transactions. The last update
overwrites updates made by the other transactions, which results in lost data.
21. What are
“Lock” hints?
Lock hints are used to customize the locking behavor
of SQL Server from 3 main perspectives, granularity, mode, and duration.
Available hints are HoldLock, NoLock, NoWait, PagLock, ReadCommtted,
ReadCommittedLock, ReadPast, ReadUncommitted, RepeatableRead, RowLock,
Serializable, TabLock, TabLockX, UpdLock, and XLock. A full understanding of
them is important before using them.
Reference … http://www.sqlnotes.info/tag/lock-hints/
22. What is a
“Deadlock”?
This is situation where both transactions in a
deadlock will wait forever unless the deadlock is broken by an external
process.
A deadlock occurs when two or more processes are
waiting on the same resource and each process is waiting on the other process
to complete before moving forward.
23. What are
the steps you can take to avoid “Deadlocks”?
Some
Suggestions:
1. Implementing an error handler that traps error
message 1205 allows an application to handle the deadlock situation.
2. Ensure the database is normalized properly because
bad database design can increase the number of deadlocks to occur inside
database.
3. Avoid cursors if
possible .
4. Implement proper locking or avoid it.
5. Consider using the NOLOCK hint where possible
6. Determine whether a transaction can run at a lower
isolation level.
24. What is
Concurrency?
25. How can we
solve concurrency problems?
27. What kind
of problems occurs if we do not implement proper locking strategy?
1. Deadlock issue.
2. Concurrency problems.