MIS 325 Lecture Notes - Lecture 16: Sql, Stored Procedure
2 May 2018
School
Department
Course
Professor
â—Ź SAVEPOINT: Used when a transaction contains so many statements that rolling back
the entire transaction would be inefficient
â—‹ SAVEPOINT statement;
â—‹ Rolling back: ROLLBACK TO SAVEPOINT statement;
â—Ź Concurrency:
○ When two or more users have access to the same database, it’s possible for
them to be working with the same data at the same time
â—‹ Problem when one user updates the data that other users are also viewing or
updating
â—‹ Lock:
â– Stops the execution of another transaction if it conflicts with a transaction
that is already running
â– Oracle default locking behavior prevents concurrency
â—‹ Concurrency problems
â– Lost updates
â—Ź Occur when two transactions select the same row and update the
row based on the values originally selected
â—Ź Later update overwrites earlier update
â– Dirty reads
â—Ź Occur when a transaction selects data that hasn't been committed
by another transaction
â—Ź Transaction selects row that doesn't exist when other transaction
rolls back the change
â– Nonrepeatable reads
â—Ź Occur when two SELECT statements that try to get the same data
get different values
â—Ź Another transaction updated in the time between the two
statements
â– Phantom reads: Occur when you perform an update or delete on a set of
rows at the same time another transaction is performing an insert or
delete
â—Ź Transaction Isolation levels
â—‹ Controls the degree to which transactions are isolated from one another
â—‹ Setting transaction isolation levels: SET TRANSACTION ISOLATION LEVEL
READ COMMITTED/SERIALIZABLE;
â– READ UNCOMMITTED: Allows all concurrency problems
â– READ COMMITTED (Default): Allows all concurrency problems but dirty
reads
â– REPEATABLE READ: Prevents all concurrency problems but phantom
reads
â– SERIALIZABLE (Supported by Oracle): Prevents all concurrency
problems
â– SNAPSHOT: Prevents all concurrency problems
Document Summary
Savepoint: used when a transaction contains so many statements that rolling back the entire transaction would be inefficient. When two or more users have access to the same database, it"s possible for them to be working with the same data at the same time. Problem when one user updates the data that other users are also viewing or updating. Stops the execution of another transaction if it conflicts with a transaction that is already running. Occur when two transactions select the same row and update the row based on the values originally selected. Occur when a transaction selects data that hasn"t been committed by another transaction. Transaction selects row that doesn"t exist when other transaction rolls back the change. Occur when two select statements that try to get the same data get different values. Another transaction updated in the time between the two statements.
