Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

SQL Server Concurrency

In this article I will share sql server concurrency and concurrency issues in detail.

SQL Server Concurrency

Database concurrency ensures that when multiple operations are occurring at once,then different operations will play nicely together.

Main aim of concurrency is improving database performance.

Sql server provided set of rules and constraints that coordinate the behaviours of transactions, making sure that different operations will play nicely together.

Concurrency control

When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control.

Types:

Pessimistic concurrency control:

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 concurrency control:

In an optimistic lock the record or page is only locked when the changes made to that record are updated.

There are four types of concurrency issues

1.  Lost Update

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.

2. Dirty Reads

Reading uncommitted modifications are calling 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.

Protecting transactions from dirty reads:

a. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

b. The SNAPSHOT isolation level.

Example:

---Query1
BEGIN TRAN 
UPDATE Employee SET CreatedDate = getdate()
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05' 
ROLLBACK  
 
---Query 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Employee 

 

Query 1 changes the row, but actually query 1 will roll-back all changes after wait time completion.
Read uncommitted isolation level allows user to read uncommitted (wrong) data updated by query 2

3.  Nonrepeatable Reads

Nonrepeatable reads occur when a second transaction accesses the same row several times and reads different data every time. This involves multiple reads of the same row. Every time, the information is changed by another transaction.

Example:

Open Microsoft SQL Management Studio. Open 2 tabs and start a transaction in both.
Select data in first window and modify it in second and commit changes.
Then, re run previous select. Data has been changed. This is non repeatable read issue.

 4. Phantom Reads

Phantom row is rows that douse not appear in the initial read, but appear when same data is read again during the same transaction.

This problem occurs when UPDATE/DELETE is happening on one set of data and INSERT/UPDATE is happening on the same set of data leading inconsistent data in earlier transaction when both the transactions are over.

Example:

--Query1
SET TRANSACTION ISOLATION
LEVEL REPEATABLE
READ   
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:10' 
SELECT * FROM Employee 
ROLLBACK 
 
--Query2
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
 
VALUES
('shourya kendre','England','English','London',5) 
Query one first select statement returns actual data, wait time completion sql query two inserts wrong data and select query 1 returns wrong data.

 

Tips to avoid deadlocks in sql server

Steps to avoid Deadlocks

In this article I will share some list of suggestions that avoids deadlock situations in SQL server or minimizing deadlocks in SQL Server.

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.

Suggestions:

1. Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation.

2. Avoid user interaction in transactions.

Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application.

3. Keep transactions short and in one batch.

A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

4. Ensure the database is normalized properly because bad database design can increase the number of deadlocks to occur inside database.

5. Avoid cursors if possible.

select statement in a cursor definition is subject to the same transaction locking rules that apply to any other select statement. In cursors, however, an additional set of scroll locks can be acquired based on the specification of a cursor concurrency level.

More information.. https://msdn.microsoft.com/en-us/library/aa172580(v=SQL.80).aspx

6. Access objects in the same order.

 In this situation if all concurrent transactions access objects in the same order, deadlocks are less likely to occur.

7. Use a row versioning-based isolation level.

Implement these isolation levels to minimize deadlocks that can occur between read and write operations.
Snapshot isolation also uses row versioning, which does not use shared locks during read operations.

8. Choose Appropriate Isolation Level or Implement proper locking

For example consider using lowers Isolation Level read committed for applications like blog,

Where multiple user’s access data and limited users update it.

If we use READ COMMITED isolation level then our share locks will be held for shorter duration as compared to higher isolation level such as SERIALIZABLE.

9. Consider using the NOLOCK hint where possible

When we try to execute select statement on table default isolation level locks the entire table when other queries or transaction updating it, with this scenario other queries, transactions will have to wait for the lock to be released.

This is fine if we need accurate results, what if your table data is not frequently updated? Or updated daily two or three times then NOLOCK would be a better option. Carefully analyses your database environment and if appropriate, consider if we can use NOLOCK hint where possible.


10. Determine whether a transaction can run at a lower isolation level.

Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.

11. Use bound connections.

Using bound connections, two or more connections opened by the same application can cooperate with each other. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Therefore they do not block each other.

Reference used:

https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/aa172580(v=SQL.80).aspx


 

Sql Server transaction interview questions answers

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 detailed explanation with example….visit

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.