DOC PREVIEW
UW CSE 444 - Serializable Isolation for Snapshot Databases

This preview shows page 1-2-3 out of 10 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 10 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 10 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 10 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 10 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

IntroductionContributionsBackgroundSnapshot IsolationWrite SkewPhantomsRelated WorkSerializable Snapshot IsolationThe AlgorithmCorrectnessFalse positivesDetecting phantomsImplementationGeneralizing to other database enginesTestingEvaluationEvaluation SetupPerformance with Short TransactionsPerformance with Long TransactionsOverhead EvaluationConclusions and Future WorkRepeatability assessment resultReferencesSerializable Isolation for Snapshot DatabasesMichael J. Cahill∗[email protected][email protected] D. [email protected] of Information TechnologiesUniversity of SydneyNSW 2006 AustraliaABSTRACTMany popular database management systems offer snap-shot isolation rather than full serializability. There are well-known anomalies permitted by snapshot isolation that canlead to violations of data consistency by interleaving trans-actions that individually maintain consistency. Until now,the only way to prevent these anomalies was to modify theapplications by introducing artificial locking or update con-flicts, following careful analysis of conflicts between all pairsof transactions.This paper describes a modification to the concurrency con-trol algorithm of a database management system that auto-matically detects and prevents snapshot isolation anomaliesat runtime for arbitrary applications, thus providing serial-izable isolation. The new algorithm preserves the proper-ties that make snapshot isolation attractive, including thatreaders do not block writers and vice versa. An implementa-tion and performance study of the algorithm are described,showing that the throughput approaches that of snapshotisolation in most cases.Categories and Subject DescriptorsH.2.4 [Database Management]: Systems—TransactionprocessingGeneral TermsAlgorithms, Performance, ReliabilityKeywordsMultiversion Concurrency Control, Serializability Theory,Snapshot Isolation∗The author is also an employee of Oracle Corporation. Thiswork was done while at the University of Sydney.Permission to make digital or hard copies of all or part of this work forpersonal or classroom use is granted without fee provided that copies arenot made or distributed for profit or commercial advantage and that copiesbear this notice and the full citation on the first page. To copy otherwise, torepublish, to post on servers or to redistribute to lists, requires prior specificpermission and/or a fee.SIGMOD’08, June 9–12, 2008, Vancouver, BC, Canada.Copyright 2008 ACM 978-1-60558-102-6/08/06 ...$5.00.1. INTRODUCTIONSerializability is an important property when transactionsexecute because it ensures that integrity constraints are main-tained even if those constraints are not explicitly declared tothe DBMS. If a DBMS enforces that all executions are seri-alizable, then developers do not need to worry that inconsis-tencies in the data might appear as artifacts of concurrencyor failure. It is well-known how to use strict two-phase lock-ing (and various enhancements such as escrow locking andmultigranularity locking) to control concurrency so that se-rializable executions are produced [11]. Some other concur-rency control algorithms are known that ensure serializableexecution, but these have not been adopted in practice, be-cause they usually perform worse than a well-engineered im-plementation of strict two-phase locking (S2PL).Snapshot isolation (SI) [3] is an alternative approach toconcurrency control, taking advantage of multiple versionsof each data item. In SI, a transaction T sees the databasestate as produced by all the transactions that committed be-fore T starts, but no effects are seen from transactions thatoverlap with T. This means that SI never suffers from Incon-sistent Reads. In a DBMS using SI for concurrency control,reads are never delayed because of concurrent transactions’writes, nor do reads cause delays in a writing transaction.In order to prevent Lost Update anomalies, SI does aborta transaction T when a concurrent transaction commits amodification to an item that T wishes to update. This iscalled the “First-Committer-Wins” rule.Despite the nice properties of SI, it has been known sinceSI was formalized in [3] that SI allows non-serializable ex-ecutions. In particular, it is possible for an SI-based con-currency control to interleave some transactions, where eachtransaction preserves an integrity constraint when run alone,but where the final state after the interleaved executiondoes not satisfy the constraint. This occurs when concur-rent transactions modify different items that are related bya constraint, and it is called the Write Skew anomaly.Example 1: Suppose that a table Duties(DoctorId, Shift,Status) represents the status (“on duty”or“reserve”) for eachdoctor during each work shift. An undeclared invariant isthat, in every shift, there must be at least one doctor onduty. A parametrized application program that changes adoctor D on shift S to “reserve” status, can be written as inFigure 1.This program is consistent, that is, it takes the databasefrom a state where the integrity constraint holds to anotherstate where the integrity constraint holds. However, sup-pose there are exactly two doctors D 1 and D2 who are on729BEGIN TRANSACTIONUPDATE Duties SET Status = ’reserve’WHERE DoctorId = :DAND Shift = :SAND Status = ’on duty’SELECT COUNT(DISTINCT DoctorId) INTO tmpFROM DutiesWHERE Shift = :SAND Status = ’on duty’IF (tmp = 0) THEN ROLLBACK ELSE COMMITFigure 1: Example parameterized application ex-hibiting write skewduty in shift S. If we run two concurrent transactions, whichrun this program for parameters (D1, S) and (D2, S) respec-tively, we see that using SI as concurrency control will allowboth to commit (as each will see the other doctor’s statusfor shift S as still unchanged, at “on duty”). However, thefinal database state has no doctor on duty in shift S, violat-ing the integrity constraint.Despite the possibility of corrupting the state of the database,SI has become popular with DBMS vendors. It often givesmuch higher throughput than strict two-phase locking, es-pecially in read-heavy workloads, and it also provides userswith transaction semantics that are easy to understand. Manypopular and commercially important database engines pro-vide SI, and some in fact use SI when serializable isolationis requested [13].Because SI allows data corruption, and is so common,there has been a body of work on how to ensure serializableexecutions


View Full Document

UW CSE 444 - Serializable Isolation for Snapshot Databases

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Serializable Isolation for Snapshot Databases
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Serializable Isolation for Snapshot Databases and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Serializable Isolation for Snapshot Databases 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?