CSD 305 Advanced Databases Transactions and Concurrency 1

Problems of concurrent access The transaction concept The ACID properties A simple locking scheme Isolation levels Locking granularity An optimistic approach Concurrency in MS SQL Server Designing for concurrency CSD 305 Advanced Databases Agenda 2

Problems of concurrent access • • Buried Update Dirty Read Unrepeatable Read Phantoms CSD 305 Advanced Databases • A range of problems can arise when multiple users are updating and reading a shared database. • There are well known categories of problems known as 3

Buried Update CSD 305 Advanced Databases • This problem arises when the effect of one user's action overwrites (buries) the effect of another • The following scenario demonstrates a buried update in the context of a flight booking system. 4

CSD 305 Advanced Databases Buried Update problem 5

Analysis of the buried update problem CSD 305 Advanced Databases • The problem arises because two units of work (transactions), operating concurrently, interfere with each other's activity. • The problem would be solved if the two transactions were to run in series rather than concurrently. 6

CSD 305 Advanced Databases Concurrent vs. serialized transactions 7

CSD 305 Advanced Databases The transaction concept 8

Other concurrency problems • Dirty Read (uncommitted dependency) • Unrepeatable Read (inconsistent analysis) • Phantoms we'll look at each in turn CSD 305 Advanced Databases • We have established that buried update is a serious concurrency problem • Other problems are: 9

CSD 305 Advanced Databases Dirty Read 10

CSD 305 Advanced Databases Unrepeatable read 11

CSD 305 Advanced Databases Phantoms 12

Summary of the problems • Dirty read • A transaction reads data written by another transaction, but not yet committed • A transaction reads data it has previously read and finds that it has been updated or deleted • Phantom read • A transaction reissues a select statement and discovers new data has been inserted that meets the select criteria CSD 305 Advanced Databases • Unrepeatable read 13

The ACID properties • All the problems we have examined can be solved by making our transactions: • Atomic • Consistent • Take the database from one consistent state to another • Isolated • No mutual interference between transactions • Durable • Once completed, their effects persist CSD 305 Advanced Databases • Indivisible, all or nothing 14

Isolation and serializability • All of the concurrent access problems outlined so far can be avoided if • The effect of full isolation is that, although transactions are being processed in parallel, they give the same effects as if they were processed in series i. e. they are serializable CSD 305 Advanced Databases • The ACID properties are maintained • Full isolation between transactions is maintained 15

CSD 305 Advanced Databases Serializability 16

A pessimistic approach to concurrency • The pessimist expects things to go wrong • Therefore the pessimistic approach to concurrency involves avoiding concurrency problems CSD 305 Advanced Databases • Problems are avoided through the use of locking 17

• Before a transaction reads a row, it must gain a READ lock on that row • Before a transaction updates a row, it must gain a WRITE lock on that row • At any moment in time any given row can have at most • One WRITE lock OR • Many READ locks • If a transaction has the only READ lock on a row, the lock can be promoted to a WRITE lock CSD 305 Advanced Databases A simple locking regime 18

CSD 305 Advanced Databases Two-phase locking 19

CSD 305 Advanced Databases The lock table 20

CSD 305 Advanced Databases Example effect of locking regime 21

• When a transaction requests a lock that is unavailable it has to wait • From the user’s perspective the transaction appears to “hang” • A “timeout” limit can be set so that a transaction backs out (rollback) if it has to wait too long • The transaction can be re-tried later on (in the expectation that locks have been released) CSD 305 Advanced Databases Hanging transactions and timeouts 22

CSD 305 Advanced Databases Deadlock 23

Compromising on isolation • Therefore, most DBMSs provide facilities to specify ISOLATION LEVELS for transactions • These should be chosen to provide appropriate levels of isolation for each type of transaction CSD 305 Advanced Databases • Strict isolation ensures data integrity and that transactions see consistent views of the data. • However, strict isolation reduces the potential for parallel processing of transactions 24

Setting Isolation Levels • • Serializable (highest) Repeatable read – phantoms allowed Read committed – nonrepeatable read also allowed Read uncommited (lowest) – dirty read also allowed Example SET TRANSACTION ISOLATION LEVEL READ COMMITTED CSD 305 Advanced Databases • SQL provides four levels of isolation This is the default in SQL Server 2008 25

Locking granularity • Row-level locking can result in large lock tables and system throughput can suffer • Locking coarser granules (e. g. tables) of data will reduce the size of lock tables and thus improve throughput • However coarser granules can also mean reduced concurrency • MS SQL Server 2008 can lock: • • rows, pages, keys, ranges of keys, indexes, tables, or databases CSD 305 Advanced Databases • Locking is a resource-consuming activity 26

CSD 305 Advanced Databases Dynamic locking granularity 27

An optimistic approach to concurrency • users do not lock data when they read it. • When data is updated, the system checks to see if another user changed the data after it was read. • If another user updated the data, an error is raised and the transaction is rolled back. CSD 305 Advanced Databases • The optimist expects that things will not go wrong • Therefore the optimistic approach to concurrency control is to allow things to go wrong – but put them right when they do! • Principles 28

Pessimistic vs. Optimistic • In general terms • Pessimistic concurrency control works best when there is a relatively high level of contention CSD 305 Advanced Databases • Optimistic concurrency control works best when there is relatively little contention 29

Concurrency in MS SQL Server • Setting transaction isolation levels for a connection • Setting concurrency options on cursors • See the SQL Server 2008 documentation for details CSD 305 Advanced Databases • SQL Server 2008 provides a wide range of concurrency options, both pessimistic and optimistic • The options can be controlled from SQL by 30

CSD 305 Advanced Databases Investigating the lock table in SQL Server 31

Designing for concurrency • Concurrency is a complex issue, but the following general principles should be considered • Keep transactions short • Don’t wait for user input mid-transaction • Because locks could be held for long periods • Use the lowest isolation level that meets your transactions integrity needs • To increase concurrency CSD 305 Advanced Databases • So that resources are not locked for long periods 32

• A range of problems arise from concurrent database activity • Database Management Systems provide a set of facilities to address these problems • The database administrator needs a good understanding of these facilities to ensure optimal performance of a multi-user system. CSD 305 Advanced Databases Summary 33