Database concurrency and transactions - Tal Olier

Database for Developers
Session 6: Transactions
Avishai Krepel & Tal Olier

Agenda
 Preface
 Database transactions and the ACID model
 Locking - basics
 Concurrency
 Locking - advanced

So many users, so little time…
 User A interacts with data X
 User B interacts with data Y
 User C interacts with data X and Y
Conflict?

Multi user applications has a parallel aspect…

Ideal world
 Suggestion - each user working on it’s own copy of the data,
and sees other users’ (copy of the) data when required,
updated to the correct time required.
 Concerns:
 How do we decide who owns the data?
 What is the correct time ?

Ideal world (cont.)
 Throughput compromise!
 User A works on Sundays, Tuesdays and Thursdays
 User B works on Mondays, Wednesdays and Fridays
 User C will have to work Saturdays…

What is a database transaction?
 Something we perform on our data?
 Will it keeps my data safe?
 How does it lets multiple users to do their work
simultaneously?
 From Babylon:
 “interaction between two parties, negotiation, settlement;
business deal”
 What is the contract?

The ACID model
 A set of rules for “keep our data safe”
 Acronym for
 Atomicity
 Consistency
 Isolation
 Durability

ACID - Atomicity

“All in one proposition”

ACID - Consistency
 Data’s state is kept consistent
 Data structures kept correct
 Failed transaction’s impact is canceled

ACID - Isolation
 Transaction separation until finished
 Modification's separation
 Data visibility separation

ACID - Durability
 Modification persistency
 Crash recovery
 Data recovery
 Log implementation

Locking
 Resource regulation
 Possible in several levels
 Object
 Page/Block
 Row
 Conceptual types:
 Exclusive lock
 Share lock

Exclusive lock
 Prevents sharing
 Obtained to modify data
 State assured until lock is removed

Share lock
 Allows limited sharing
 Used by data readers to block writers
 Share likes “to share” with share

… Consistency

• Data’s state is kept consistent
• Data structures kept correct
• Failed transaction’s impact is canceled …

Read consistency
 Data changes according to time
 Readers and writers conflicts
 Default level
 Oracle: statement
 SQL Server: none

Stmt level read consistency (Oracle)
 SELECT statement starts at
08:00 (SCN 10023)
 UPDATE statement update
1-
data in block #3 and block
2-
#5 at 09:00 (SCN
10024) 3-
 The SELECT gets to blocks
4-
#3 and #5 at 10:00 and 5-
reads them from the 6-
rollback segment (UNDO 7-
tablespace)

Concurrency side effects
 Dirty reads
 Nonrepeatable (fuzzy) reads
 Phantom reads (or phantoms)

Isolation Levels
The SQL standard defines four levels:
 Read uncommitted
 Read committed
 Repeatable read
 Serializable

Isolation Levels - Read uncommitted
 The only rule is – “no rules”
 One transaction may see uncommitted changes made by
some other transaction

Isolation Levels - Read committed
 The default isolation level
 Query sees only data committed before (what?)
 Good for conflicting transactions

Isolation Levels - Repeatable read
 Read data can not be changed
 The transaction require specific locks
 The transaction will see new data committed

Isolation Levels - Serializable
 Create the feeling that no other user changed
data
 Implementations difference
 Oracle: The transaction sees only changes
committed before it started
 SQL Server: The transaction sees the changes as if
all transactions are ordered by time
 Require extra developer work

Read phenomena by isolation levels

Isolation levels – Oracle and SQL
Server
 Oracle offers the following  SQL Server offers all 4
isolation levels isolation levels:
 Read committed  Read uncommitted
 Serializable isolation levels  Read committed
 Read-only mode that is not  Repeatable read
part of SQL92  Serializable
 Read committed is the  Read committed is the
default default

Setting isolation level
Oracle SQL Server
 SET TRANSACTION ISOLATION
LEVEL READ COMMITTED;
SET TRANSACTION
 SET TRANSACTION ISOLATION ISOLATION LEVEL
LEVEL SERIALIZABLE;  READ UNCOMMITTED
 SET TRANSACTION READ ONLY;
 ALTER SESSION SET  READ COMMITTED
ISOLATION_LEVEL  REPEATABLE READ
SERIALIZABLE;
 ALTER SESSION SET  SERIALIZABLE
ISOLATION_LEVEL READ
COMMITTED;

Readers and writers
Oracle SQL Server
 Data versioning based  Read-lock mechanism
 Readers do not block based
writers  Readers block writers
 Writers do not block  Writers block readers
readers

Locking - Oracle
Types of locks

Oracle DML locks
 Assure integrity of data being accessed by multiple users
 Both table and row locks are used
 Partition is considered a table

Row locks (Oracle DML locks)
A transaction acquires an exclusive row lock for each individual
row modified by one of the following statements:
 INSERT
 UPDATE
 DELETE
 SELECT (only with the FORUPDATE clause)

Table locks (Oracle DML locks)

LOCK TABLE <table name> IN EXCLUSIVE MODE

Oracle manual (explicit ) data locking
Possible via one of the following:
 The SET TRANSACTION ISOLATION LEVEL statement
 The LOCK TABLE statement
 The SELECT ... FOR UPDATE statement

Locks acquired by these statements are released after the
transaction commits or rolls back

Locking – SQL Server
Resources that can be locked by SQL Server

Locking explicitly – SQL Server
 Done via hints (proprietary syntax)
 Example:
select *
from t1
with (PAGLOCK ,HOLDLOCK)
where c1 between 17 and 32

Locking explicitly – SQL Server (cont.)
 Some more examples of hints
 NOLOCK/READUNCOMMITTED – dirty read
 PAGLOCK – takes page locks instead of row locks (statement
scoped)
 TABLOCK/X - takes page locks (share, exclusive) instead of
row locks (statement scoped)
 UPDLOCK/XLOCK – takes exclusive locks for read rows
(transaction scoped)

Locking and transactions
 Transaction modes are managed at the session level
 Connection is the object that encapsulates the session “state”

Transaction management - syntax
Oracle SQL Server
BEGIN BEGIN TRANSACTION
update emp set salary = update emp set salary =
salary * 0.95; salary * 0.95;
update emp set salary = update emp set salary =
salary * 0.95 where
salary * 0.95 where
manager_flag = 1;
manager_flag = 1;
COMMIT;
COMMIT;
END;
/ GO

Transaction – possible endings
 Commit – makes changes visible
 Rollback – cancel changes
 commit/rollback
 Remove all locks
 Start a new implicit transaction

Auto commit
 Every SQL statement is committed (upon completion)
 The default mode for ADO, OLE DB, ODBC, DB-Library
and Java

SQL tools behavior
 Notice the auto-commit setting prior to updating records
 SQL Server
 SQL Server management studio (SSMS)
 Every query window is a different connection

 Oracle
 Oracle SQL developer (until release 2)
 All windows of the same connection share the same transaction (and
locks)

Partial rollback
SQL Server “save transaction” Oracle “savepoint”

begin transaction trans1 begin

insert into t1 values (1, 'one');
insert into t1 values (1, 'one');
savepoint two;
save transaction two
insert into 1 values (2, 'two');
insert into 1 values (2, 'two');
rollback to savepoint two;

rollback transaction two commit;

commit end;

Partial rollback (cont.)

SQL Server “save transaction” Oracle “savepoint”

After a partial rollback After a partial rollback
transaction do not free transaction do free locks
locks held by roll backed held by roll backed
statements statements

Select for update (demo)
Oracle SQL Server
 SELECT ID FROM EMP  SELECT ID FROM EMP
FOR UPDATE WITH (UPD LOCK)

Locking example
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

…
101 102 103 104 105
106 107 108 109 110
111 112 113 114 115
116 117 118 119 120
121 122 123 124 125

Lock escalation
 Occurs when numerous locks are held at one level of
granularity
 Database decides to raise some/all of the locks to a higher
level
 Tradeoff between number of locks and restrictiveness

Lock escalation – Oracle

Oracle never escalate locks!

Lock escalation – SQL Server
 Escalation thresholds:
 T-SQL level – amount of locked rows (~5000)
 Instance level – amount of memory occupied by locks (40%)
 Thresholds can be changed
 SQL Sever instance ‘locks’ parameter
 Escalation can fail because of other locking transactions

Lock escalation – SQL Server (cont.)
Escalation success Escalation failure
 The full table lock is  Full lock cannot be
acquired acquired
 All heap or B-tree, page  Database Engine will
(PAGE), or row-level continue to acquire row,
(RID) locks held by the key, or page locks
transaction on the heap or  Database Engine will retry
index are released the lock escalation for each
additional ~1,250 locks
acquired by the transaction

Lock escalation – demo (SQL Server)
 Demo table

 Update of x rows from the
table
 Check the locks on the
table

Lock escalation – disable
 At instance level
 DBCC TRACEON (1211,-1) – disable memory or # of locks
escalations
 DBCC TRACEON (1224,-1) – disable # locks escalations
 At table level (SQL Server 2008 only)
 ALTER TABLE SET LOCK_ESCALATION = DISABLE
 At session level
 DBCC TRACEON (1211)
 DBCC TRACEON (1224)

Lock escalation – DEV pitfalls
Note:
 Cases when multiple updates/deletes from various clients
happen on the same table and spanned across many rows
 Select with updlock (select for update) is considered as update
 Oracle developers do not know this behavior

Database concurrency and transactions - Tal Olier

More Related Content

What's hot

Viewers also liked

Similar to Database concurrency and transactions - Tal Olier

More from sqlserver.co.il

Database concurrency and transactions - Tal Olier