Transaction Processing in ANSI SQL Dr Janusz R. Getta School of - - PowerPoint PPT Presentation

transaction processing in ansi sql
SMART_READER_LITE
LIVE PREVIEW

Transaction Processing in ANSI SQL Dr Janusz R. Getta School of - - PowerPoint PPT Presentation

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 CSCI235 Database Systems Transaction Processing in ANSI SQL Dr Janusz R. Getta School of Computing and


slide-1
SLIDE 1

CSCI235 Database Systems

Transaction Processing in ANSI SQL

Dr Janusz R. Getta

School of Computing and Information Technology - University of Wollongong

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 1 of 19 6/9/20, 9:53 pm

slide-2
SLIDE 2

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon Isolation levels Phenomena Isolation levels versus phenomena Setting isolation levels in ANSI SQL

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 2/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 2 of 19 6/9/20, 9:53 pm

slide-3
SLIDE 3

Dirty read phenomenon

A transaction reads uncommitted data created by a transaction that fails later on

Transaction 1 Transaction 2

Dirty read phenomenon

SELECT budget FROM DEPARTMENT WHERE name = 'SALES'; 2000 UPDATE DEPARTMENT SET BUDGET = BUDGET + 1000 WHERE NAME = 'Sales'; SELECT budget FROM DEPARTMENT WHERE name = 'SALES'; 3000 ROLLBACK; ???

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 3/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 3 of 19 6/9/20, 9:53 pm

slide-4
SLIDE 4

Reading only committed data

Transaction 1 Transaction 2

Reading only committed data

SELECT budget FROM DEPARTMENT WHERE name = 'SALES'; 2000 UPDATE DEPARTMENT SET BUDGET = BUDGET + 1000 WHERE NAME = 'Sales'; SELECT budget FROM DEPARTMENT WHERE name = 'SALES'; 2000 ROLLBACK;

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 4/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 4 of 19 6/9/20, 9:53 pm

slide-5
SLIDE 5

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon Isolation levels Phenomena Isolation levels versus phenomena Setting isolation levels in ANSI SQL

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 5/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 5 of 19 6/9/20, 9:53 pm

slide-6
SLIDE 6

Non-repeatable read phenomenon

A transaction reads the same data item few times and each a data item has a different value

Transaction 1 Transaction 2

Non-repeatable read phenomenon

SELECT budget FROM DEPARTMENT WHERE name = 'SALES'; 2000 UPDATE DEPARTMENT SET BUDGET = BUDGET + 1000 WHERE NAME = 'Sales'; COMMIT; SELECT budget FROM DEPARTMENT WHERE name = 'SALES'; 3000 ???

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 6/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 6 of 19 6/9/20, 9:53 pm

slide-7
SLIDE 7

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon Isolation levels Phenomena Isolation levels versus phenomena Setting isolation levels in ANSI SQL

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 7/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 7 of 19 6/9/20, 9:53 pm

slide-8
SLIDE 8

Phantom phenomenon

A transaction counts the total number of rows in the same table several times and each time the total number of rows is different

Transaction 1 Transaction 2

Phantom phenomenon

SELECT count(*) FROM DEPARTMENT 20 DELETE DEPARTMENT WHERE NAME = 'Sales'; COMMIT; SELECT count(*) FROM DEPARTMENT 19

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 8/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 8 of 19 6/9/20, 9:53 pm

slide-9
SLIDE 9

No phantoms

Transaction 1 Transaction 2

No phantoms

SELECT count(*) FROM DEPARTMENT 20 DELETE DEPARTMENT WHERE NAME = 'Sales'; COMMIT; SELECT count(*) FROM DEPARTMENT 20

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 9/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 9 of 19 6/9/20, 9:53 pm

slide-10
SLIDE 10

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon Isolation levels Phenomena Isolation levels versus phenomena Setting isolation levels in ANSI SQL

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 10/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 10 of 19 6/9/20, 9:53 pm

slide-11
SLIDE 11

Isolation levels

SQL provides four levels of isolation for database transactions Isolation levels are equivalent to correctness levels Isolation levels are defined in terms of several possible phenomena, or weird hard-to-explain occurrences of operations The following isolation levels are defined in ANSI SQL Isolation levels are defined in the terms of so called phenomena The following phenomena are considered

READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE

  • Dirty read phenomenon

Non-repeatable read phenomenon Phantom phenomenon

  • TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020

11/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 11 of 19 6/9/20, 9:53 pm

slide-12
SLIDE 12

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon Isolation levels Phenomena Isolation levels versus phenomena Setting isolation levels in ANSI SQL

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 12/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 12 of 19 6/9/20, 9:53 pm

slide-13
SLIDE 13

Phenomena

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon

Read operations may access dirty data, i.e. data written by uncommitted transactions

  • Different reads by a single transaction to the same data will not be repeatable,

i.e. they may return different values

  • A set of rows that transaction reads once might be a different set of rows if the

transaction attempts to read them again

  • TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020

13/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 13 of 19 6/9/20, 9:53 pm

slide-14
SLIDE 14

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon Isolation levels Phenomena Isolation levels versus phenomena Setting isolation levels in ANSI SQL

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 14/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 14 of 19 6/9/20, 9:53 pm

slide-15
SLIDE 15

Isolation levels versus phenomena

At READ UNCOMMITED isolation level a transaction may exhibit: At READ COMMITED isolation level a transaction may exhibit: At REPEATABLE READ isolation level a transaction may exhibit: At SERIALIZABLE isolation level a transaction may exhibit:

dirty read phenomenon, non-repeatable read phenomenon, phantom phenomenon

  • non-repeatable read phenomenon,

phantom phenomenon

  • phantom phenomenon
  • none of the phenomena
  • TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020

15/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 15 of 19 6/9/20, 9:53 pm

slide-16
SLIDE 16

Isolation levels versus phenomena

Level Dirty Read Nonrepeatable Read Phantom

  • READ UNCOMMITTED

Possible Possible Possible READ COMMITTED not possible Possible Possible REPEATABLE READ not possible not possible Possible SERIALIZABLE not possible not possible not possible

Isolation levels versus phenomena TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 16/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 16 of 19 6/9/20, 9:53 pm

slide-17
SLIDE 17

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon Non-repeatable read phenomenon Phantom phenomenon Isolation levels Phenomena Isolation levels versus phenomena Setting isolation levels in ANSI SQL

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 17/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 17 of 19 6/9/20, 9:53 pm

slide-18
SLIDE 18

Setting isolation levels in ANSI SQL

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 18/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 18 of 19 6/9/20, 9:53 pm

slide-19
SLIDE 19

References

Elmasri R. and Navathe S. B., Fundamentals of Database Systems, Chapter 20.6 Transaction Support in SQL, 7th ed., The Pearson Education Ltd, 2017

TOP Created by Janusz R. Getta, CSCI235 Database Systems, Spring 2020 19/19

Transaction Processing in ANSI SQL file:///Users/jrg/235-2020-SPRING/SLIDES/WEEK06/13transactionsinsql/13transactionsinsql.html#1 19 of 19 6/9/20, 9:53 pm