SQL Optimized Locking
A mechanism in SQL databases that prevents multiple transactions from updating the same data simultaneously to ensure data integrity and consistency. It utilizes a combination of shared (S) locks for read operations and exclusive (X) locks for data modification operations. Transaction locking is crucial for maintaining data integrity but can impact performance and scalability. Optimized locking methods like Lock After Qualification (LAQ) with Transaction ID (TID) provide benefits such as reduced lock memory consumption and improved concurrency.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SQL Optimized Locking Prashanth Purnananda
What is Transactional Locking in SQL? A mechanism that prevents multiple transactions from updating the same data simultaneously, in order to protect data integrity and consistency. It uses a combination of page and row locks like: Shared (S) locks for read operations and Exclusive (X) locks for data modification operations like insert, update, and delete. Transaction Locking is essential in databases for data integrity and consistency Transaction Locking can impact performance and scalability Uses too much lock memory Causes unnecessary blocking Auto escalates fine-grain locks into fewer coarse-grain locks based on thresholds (E.g., row to table lock) Workarounds (E.g, disable lock escalations etc.) never fully solve these problems
Locking mechanism in SQL Server (without OL) ALTER DATABASE [db1] SET READ_COMMITTED_SNAPSHOT OFF; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,10), (2,20), (3,30); -- TID2: Increase b by 10 BEGIN TRAN UPDATE t1 SET b=b+10; Lock Manager p1: Data Page for t1 Lock Mode Lock Type Lock Resource IX OBJECT t1 r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 r1: 1 | 20 | TID2 IU IX PAGE PAGE p1 p1 r2: 2 | 20 | TID1 r2: 2 | 20 | TID1 r2: 2 | 30 | TID2 U X RID RID r1 r1 r3: 3 | 30 | TID1 r3: 3 | 30 | TID1 r3: 3 | 40 | TID2 U X RID RID r2 r2 U X RID RID r3 r3
Introducing Optimized Locking An improved transaction locking mechanism that reduces lock memory consumption and blocking amongst concurrent transactions. Lock After Qualification (LAQ) Transaction ID (TID) Locking 1 2 Each row is labeled with the last transaction ID (TID) that modified it. Instead of potentially many key or row identifier locks during updates, a single lock on the TID is used. Predicates of a query are evaluated on the latest committed version of the row without acquiring a lock. E.g., Update queries on different rows in the same table will not block each other. Benefits: Reduced lock memory and lock escalations, resulting in COGS improvements and improved concurrency. Benefits: Reduced blocking resulting in improved concurrency.
Transaction ID (TID) Locking Without TID One lock per row is a significant resource drain when large transactions are involved. Lock escalation does not solve all the cases: Fails with conflicting transactions Creates blocking whole table is now blocked Does not solve the issue with a transaction with multiple small statements Updating 1 million rows can take 1 million X row locks and hold them until EOT. With TID Each row is labeled with the last transaction ID (TID) that modified it. A lock on this TID resource will protect the row. Every write transaction X locks its own TID Transactions take S lock on TID on the row to synchronize with writers Page and row locks are still taken, but only for the duration of individual row update. Updating 1 million rows will take 1 million X row locks but none of it will be held until EOT. Note that they're still held until EOT when using strict isolation levels or when honoring lock hints. Only 1 TID lock will be held until EOT.
Locking mechanism in SQL Server (with OL) ALTER DATABASE [db1] SET READ_COMMITTED_SNAPSHOT OFF; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,10), (2,20), (3,30); -- TID2: Increase b by 10 BEGIN TRAN UPDATE t1 SET b=b+10; Lock Manager p1: Data Page for t1 Lock Mode Lock Type Lock Resource X XACT TID2 r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 r1: 1 | 20 | TID2 IX OBJECT t1 r2: 2 | 20 | TID1 r2: 2 | 20 | TID1 r2: 2 | 30 | TID2 IU IX PAGE PAGE p1 p1 r3: 3 | 30 | TID1 r3: 3 | 30 | TID1 r3: 3 | 40 | TID2 U X U X U X RID RID RID RID RID RID r1 r1 r2 r2 r3 r3
Lock After Qualification (LAQ) Without LAQ Predicates are applied row by row after taking a U row lock. If satisfied, X row lock is taken for update. With LAQ (only for RCSI isolation level) Predicate is applied on latest committed version without taking any row locks. If the predicate does not satisfy, we will move to the next row If the predicate is satisfied, we take X lock to update If we had to wait for the X lock, the row might have changed. We will retry the predicate evaluation on the same row .
Locking mechanism in SQL Server (without OL) ALTER DATABASE [db1] SET READ_COMMITTED_SNAPSHOT ON; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,10), (2,20), (3,30); -- TID2 [SESSION 1]: Increase b by 10 where a=1 BEGIN TRAN UPDATE t1 SET b=b+10 where a=1; BEGIN TRAN UPDATE t1 SET b=b+10 where a=1; -- TID2 [SESSION 1]: Increase b by 10 where a=1 -- TID3 [SESSION 2]: Increase b by 10 where a=2 BEGIN TRAN UPDATE t1 SET b=b+10 where a=2; Lock Manager p1: Data Page for t1 Row version store Row qualifies Row does not qualify Lock Mode Lock Type Resource Owner Status IX IX OBJECT OBJECT t1 t1 TID2 TID2, TID3 GRANT GRANT r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 r1: 1 | 20 | TID2 r1: 1 | 10 | TID1 IU IX PAGE PAGE p1 p1 TID2 TID2 GRANT GRANT r2: 2 | 20 | TID1 r2: 2 | 20 | TID1 U X RID RID r1 r1 TID2 TID2 GRANT GRANT r3: 3 | 30 | TID1 r3: 3 | 30 | TID1 U U IU RID RID PAGE r2 r3 p1 TID2 TID2 TID3 GRANT GRANT GRANT U RID r1 TID3 WAIT Session 2 is blocked waiting for Session 1 to commit
Locking mechanism in SQL Server (with OL) ALTER DATABASE [db1] SET READ_COMMITTED_SNAPSHOT ON; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,10), (2,20), (3,30); -- TID2 [SESSION 1]: Increase b by 10 where a=1 -- TID2 [SESSION 1]: Increase b by 10 where a=1 BEGIN TRAN UPDATE t1 SET b=b+10 where a=1; BEGIN TRAN UPDATE t1 SET b=b+10 where a=1; -- TID3 [SESSION 2]: Increase b by 10 where a=2 BEGIN TRAN UPDATE t1 SET b=b+10 where a=2; Lock Manager p1: Data Page for t1 Row version store Row qualifies Row does not qualify Row does not qualify Lock Mode Lock Type Resource Owner Status X XACT TID2 TID2 GRANT r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 r1: 1 | 20 | TID2 r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 IX IX OBJECT OBJECT T1 t1 TID2 TID2, TID3 GRANT GRANT r2: 2 | 20 | TID1 r2: 2 | 20 | TID1 r2: 2 | 30 | TID3 r2: 2 | 20 | TID1 X IX XACT PAGE TID3 p1 TID3 TID2 GRANT GRANT r3: 3 | 30 | TID1 r3: 3 | 30 | TID1 X IX RID PAGE r1 p1 TID2 TID3 GRANT GRANT X RID r2 TID3 GRANT Session 2 is not blocked by Session 1
Locking mechanism in SQL Server (Row requalification with OL) ALTER DATABASE [db1] SET READ_COMMITTED_SNAPSHOT ON; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; Another transaction TID3 modified the row before TID2 could modify it CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,10), (2,20), (3,30); -- TID3 [SESSION 2]: Increase b by 10 where a=1 -- TID3 [SESSION 2]: Increase b by 10 where a=1 BEGIN TRAN UPDATE t1 SET b=b+10 where a=1; BEGIN TRAN UPDATE t1 SET b=b+10 where a=1; -- TID2 [SESSION 1]: Increase b by 10 where a=1 BEGIN TRAN UPDATE t1 SET b=b+10 where a=1; Requalify Requalify Lock Manager p1: Data Page for t1 Row version store TID3 committed, Row Row qualifies Row qualifies Row does not qualify needs to be requalified Lock Mode Lock Type Resource Owner Status X XACT TID2 TID2 GRANT r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 r1: 1 | 10 | TID1 r1: 1 | 20 | TID3 r1: 1 | 30 | TID2 IX IX OBJECT OBJECT T1 T1 TID2 TID2, TID3 GRANT GRANT r2: 2 | 20 | TID1 r2: 2 | 20 | TID1 r1: 1 | 20 | TID3 IX X PAGE XACT p1 TID3 TID2 TID3 GRANT GRANT r3: 3 | 30 | TID1 r3: 3 | 30 | TID1 S X XACT RID TID3 r1 TID2 TID2 WAIT GRANT
