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

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 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.


SQL Server Exception Handling Interview Question and Answers

SQL Server Error Handling Interview Question and Answers

In this article I will share some frequently asked Error Handling interview questions and answers in SQL server.

1. How will you Handle Error in SQL SERVER ?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql.
Before SQL server 2012 release tray…catch block used to handle error with RAISERROR.
SQL Server 2012 introduced the THROW statement. You now have a replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data.

More.. http://codechef4u.com/post/2015/04/13/throw-t-sql

http://codechef4u.com/post/2015/04/06/raiserror

2. What is RAISEERROR?

Used to return error message back to application, system warning or error message generated by Sql server database engine.

Syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

 More detail… http://codechef4u.com/post/2015/04/06/raiserror

3. Explain Try...Catch with sql server?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql. Statement enclosed in catch block.

Syntax:

BEGIN TRY
     { T-Sql_ statement | statement_block }
END TRY
BEGIN CATCH
     [ { T-Sql _statement | statement_block } ]
END CATCH
[ ; ]

More info… http://codechef4u.com/post/2015/04/13/try-catch

4. Explain THROW statement in sql server 2008?

THROW statement raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server (SQL Server 2012+)

You don’t have to specify any parameters and the results are more accurate. You simply include the statement as is in the CATCH block.

 more info...http://www.codechef4u.com/post/2015/04/13/throw-t-sql.aspx

5. Explain following error properties?

ERROR_NUMBER():

The number assigned to the error.

ERROR_LINE():

The line number inside the routine that caused the error.

ERROR_MESSAGE():

The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY():

The error’s severity.

ERROR_STATE():

The error’s state number.

ERROR_PROCEDURE():

The name of the stored procedure or trigger that generated the error.

6. Explain Error and Transaction Handling in SQL Server?

You can use try-catch block for Error and Transaction Handling, inside try block write your sql statements or queries check "@@TRANCOUNT > 0"  if that’s is greater than "0" rollback transaction in catch block else commit transaction.

More… http://www.codechef4u.com/post/2015/04/06/raiserror

http://codechef4u.com/post/2015/04/13/try-catch

7. Can we use two or more catch block with associated one try block?

No that is not allowed.

8. Can we use TRY..CATCH block with transaction?

Yes ,TRY..CATCH blocks can be used with transactions.

9. Can we use try catch block inside user defined function?

No, TRY...CATCH construct cannot be used in user defined function.