1 / 12

Locking and Deadlocks

Locking and Deadlocks. Presented by: janardan pandey Venkatesh nadar. Why locking is needed??. Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.

mireya
Télécharger la présentation

Locking and Deadlocks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Locking and Deadlocks Presented by: janardanpandey Venkateshnadar

  2. Why locking is needed?? Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time. Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data.

  3. The transaction does this by requesting a lock on the piece of data. The lock mode defines the level of dependency the transaction has on the data. No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another transaction. All locks held by a transaction are released when the transaction completes (either commits or rolls back).

  4. Types of Locks shared exclusive update Intent Schema Bulk update Key Range

  5. DEADLOCKS A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources. Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system. A thread in a multi-threaded system may acquire one or more resources (for example, locks).

  6. If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. The waiting thread is said to have a dependency on the owning thread for that particular resource.

  7. If the owning thread wants to acquire another resource that is currently owned by the waiting thread, the situation becomes a deadlock: both threads cannot release the resources they own until their transactions are committed or rolled back, and their transactions cannot be committed or rolled back because they are waiting on resources the other owns.

  8. Minimizing deadlocks… Access objects in the same order. Avoid user interaction in transactions. Keep transactions short and in one batch. Use a lower isolation level.

  9. Deadlock Detection Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The following points describe the search process: The default interval is 5 seconds. If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks. If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.

  10. Thank You

More Related