Database Transactions & Isolation: Ensuring Consistency & Preventing Interleaving, Slides of Database Management Systems (DBMS)

Download Database Transactions & Isolation: Ensuring Consistency & Preventing Interleaving and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Transactions

Serializability Isolation Levels Atomicity

The Setting

• Database systems are normally being accessed by many users or processes at the same time. - Both queries and modifications.
• Unlike operating systems, which support interaction of processes, a DMBS needs to keep processes from troublesome interactions.

ACID Transactions

• A DBMS is expected to support “ ACID transactions ,” processes that are: - Atomic : Either the whole process is done or none is. - Consistent : Database constraints are preserved. - Isolated : It appears to the user as if only one process executes at a time. - Durable : Effects of a process do not get lost if the system crashes.

Transactions in SQL

• SQL supports transactions, often behind the scenes. - Each statement issued at the generic query interface is a transaction by itself. - In programming interfaces like Embedded SQL or PSM, a transaction begins the first time a SQL statement is executed and ends with the program or an explicit transaction-end.

ROLLBACK

• The SQL statement ROLLBACK also causes the transaction to end, but by aborting. - No effects on the database.
• Failures like division by 0 or a constraint violation can also cause rollback, even if the programmer does not request it.

An Example: Interacting Processes

• Assume the usual Sells(store,candy,price) relation, and suppose that Joe’s Market sells only Twizzlers for $2.50 and Kitkats for $3.00.
• Sally is querying Sells for the highest and lowest price Joe charges.
• Joe decides to stop selling Twizzlers and Kitkats, but to sell only Smarties at $3.50.

Joe’s Program

• At about the same time, Joe executes the following steps, which have the mnemonic names (del) and (ins).

(del) DELETE FROM Sells

WHERE store = ’Joe’’s Market’;

(ins) INSERT INTO Sells

VALUES(’Joe’’s Market’, ’Smarties’,3.50);

Interleaving of Statements

• Although (max) must come before (min), and (del) must come before (ins), there are no other constraints on the order of these statements, unless we group Sally’s and/or Joe’s statements into transactions.

Fixing the Problem by Using

Transactions

• If we group Sally’s statements (max)(min) into one transaction, then she cannot see this inconsistency.
• She sees Joe’s prices at some fixed time.
  • Either before or after he changes prices, or in the middle, but the MAX and MIN are computed from the same prices.

Another Problem: Rollback

• Suppose Joe executes (del)(ins), not as a transaction, but after executing these statements, thinks better of it and issues a ROLLBACK statement.
• If Sally executes her statements after (ins) but before the rollback, she sees a value, 3.50, that never existed in the database.

Isolation Levels

• SQL defines four isolation levels = choices about what interactions are allowed by transactions that execute at about the same time.
• How a DBMS implements these isolation levels is highly complex, and a typical DBMS provides its own options.

Choosing the Isolation Level

• Within a transaction, we can say:

SET TRANSACTION ISOLATION LEVEL X

where X =

• SERIALIZABLE or
• REPEATABLE READ or
• READ COMMITTED or
• READ UNCOMMITTED

Isolation Level Is Personal Choice

• Your choice, e.g., run serializable, affects only how you see the database, not how others see it.
• Example: If Joe runs serializable, but Sally doesn’t, then Sally might see no prices for Joe’s Market. - i.e., it looks to Sally as if she ran in the middle of Joe’s transaction.

Read-Commited Transactions

• If Sally runs with isolation level READ COMMITTED, then she can see only committed data, but not necessarily the same data each time.
• Example: Under READ COMMITTED, the interleaving (max)(del)(ins)(min) is allowed, as long as Joe commits. - Sally sees MAX < MIN.