1 / 19

Database Modelling

Database Modelling. Lecture 8 (b): Transactions. Learning Objectives. To appreciate the need for transactions for updating with integrity. To understand how Oracle satisfies the ACID requirements for transactions. Overview. This week’s lecture will cover:

sun
Download Presentation

Database Modelling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Modelling Lecture 8 (b): Transactions

  2. Learning Objectives • To appreciate the need for transactions for updating with integrity. • To understand how Oracle satisfies the ACID requirements for transactions.

  3. Overview This week’s lecture will cover: • The need for transaction management • Transaction control commands • Requirements of transactions (ACID) • Use of locks in Oracle database

  4. Transactions - Rationale • Consider two clients booking airline tickets • There are 2 seats left on a flight • Client A wants 2 seats: • time 12:02 makes initial request • 12:06 confirms purchase through booking form • 12:08 authorises credit card payment • Client B wants 2 seats: • time 12:03 makes initial request • 12:05 confirms purchase through booking form • 12:09 authorises credit card payment • Situation needs careful control

  5. Some Possibilities • Clients A and B are both told 2 seats are free in initial enquiries • B confirms purchase before A • But A may still proceed • A attempts credit card debit first • If successful A secures tickets at 12:08 • B then attempts credit card debit • If successful B secures tickets at 12:09 • potentially over-writing A’s tickets • A has paid for tickets no longer his/hers

  6. Requirements 1 • When client A beats B in the initial enquiry: • they should form a queue (serialisability) • B must wait for A to finish • Different kinds of finish for A: • successful • completes booking form • makes credit card debit • store results (commit) • number of seats available is now zero • write transaction log and finish • B cannot proceed with purchase as no tickets left

  7. Requirements 2 • unsuccessful • may not complete booking form • may not have funds on credit card • undo any database changes (rollback) and finish • number of seats available is still 2 • B can now proceed to attempt to purchase the 2 tickets left • Techniques required to emulate business practice

  8. Transactions in SQL • A transaction is a logical unit of work on a database. • A group of related operations that • typically comprises a collection of individual actions • e.g. in SQL INSERT, UPDATE, DELETE, SELECT • must be performed successfully • before any changes to the database are finalised. • Variable size: • entire run on SQL*Plus • e.g. spend 2 hours inserting data • single command in SQL*Plus • e.g. one insert command • one execution of a procedure • e.g. one run of add_patient

  9. SQL Transaction commands • DBMS does not have an inbuilt way of knowing which commands are grouped to form a single logical transaction. • Some commands, e.g. COMMIT and ROLLBACK can provide boundaries of transaction. • Commit; • saves current database state • releases resources, locks & savepoints held • equivalent to Save and Exit in MS Word • Rollback; • returns database state to that at start of transaction • releases resources, locks & savepoints held • equivalent to dismiss/ do not save changes in MS Word

  10. Use of Transaction Commands • Commit/Rollback • explicitly entered in: • SQL*Plus window interactively • PL/SQL code including procedures • implicitly entered • on normal EXIT from Oracle (via a commit) • on abnormal exit from Oracle e.g. dismiss (rollback) • after each DML (except SELECT) command in SQL*Plus (commit) • ONLY when autocommit is ON (or IMMEDIATE) • After a DDL command, e.g. alter/drop/create table (commit) • whatever the autocommit setting

  11. SQL approach may be informal • No explicit • BEGIN transaction, END transaction • With autocommit OFF • SET AUTOCOMMIT OFF/ON • Implicit BEGIN transaction by: • start of SQL*Plus session • Implicit END transaction by: • end of SQL*Plus session

  12. Transactions - ACID • Transactions must satisfy ACID requirements: • Atomicity • Collection of operations is viewed as a single data process • Consistency • Data integrity is preserved • Isolation • No interaction between one transaction and another • Intermediate results not viewable by others • Durability • Once completed successfully (committed), effect of transaction is guaranteed to persist, is permanently recorded in the database and should not be lost due to database failure.

  13. ACID in Oracle (1) • Atomicity • all commands in a transaction form a single indivisible logical unit. The unit should either be performed in its entirety or not performed at all • Consistency • Transactions should transform the database from one consistent state to another, although consistency can be violated mid-transaction. • integrity checks within transaction.

  14. ACID in Oracle (2) • Isolation • transactions execute independently of one another. The partial effects of incomplete transactions should not be visible to other transactions. • data modified by transaction is not visible to others until end of transaction

  15. ACID in Oracle (4) • Durability If a failure occurs during the transaction, the database could be left inconsistent. - Recovery manager - ensures that the database is restored to the state it was in before the start of the transaction. - either all the effects of a given transaction are permanently recorded in the database or none at all (atomicity and durability).

  16. ACID in Oracle (5) Durability (continued) - log records written before the corresponding database write. - the transaction commit record in the log file shows which transactions have been committed. - any records without transaction commit were active at the time of failure and need to be undone - On Commit • database state is first saved • transaction log file is then updated • this log file may be held in several locations • confirmation of log file writes ends transaction • If crash (e.g. of disk) after commit • restore last save of database file • run transaction log on database forward to reapply the committed transactions from the log file.

  17. Partial Rollbacks • Savepoints can be declared in SQL*Plus window or PL/SQL: SAVEPOINT label;(label is a character string) • The command ROLLBACK to label; undoes changes back to the label in the program or window • Many different savepoints can be declared

  18. Locks • Resources are held by locks • In SQL lock management is done: • automatically with COMMIT and ROLLBACK • Users and programmers can rely on defaults • However, some knowledge is useful for: • tuning in production systems using LOCK command for efficiency • understanding problems in running concurrent transactions

  19. Summary Following this lecture, you should be able to discuss: • Why database transactions are needed • The use of transaction control commands such as COMMIT and ROLLBACK • The ACID properties of transactions • (Briefly) Database locks

More Related