1 / 31

Concurrency and Transaction Processing

Concurrency and Transaction Processing. Concurrency models. 1. Pessimistic avoids conflicts by acquiring locks on data that is being read, so no other processes can modify that data

alaire
Download Presentation

Concurrency and Transaction Processing

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. Concurrency and Transaction Processing

  2. Concurrency models 1. Pessimistic • avoids conflicts by acquiring locks on data that is being read, so no other processes can modify that data • acquires locks on data being modified, so no other processes can access that data for either reading or modifying • readers block writers and writers block readers

  3. Concurrency models 2. Optimistic • use row versioning to allow data readers to see the state of the data before the modification occurs • process that modifies the data is unaffected by processes reading the data because the reader is accessing a saved version of the data rows • readers do not block writers and writers do not block readers

  4. Transaction Processing • Guarantees the consistency and recoverability of databases. • Ensures that all transactions are performed as a single unit of workeven in the presence of a hardware or general system failure. • Such transactions are referred to as having the ACID properties

  5. ACID Properties - 1 1.Atomicity Each transaction is treated as all or nothing - it either commits or aborts If a transaction commits, all its effects remain. If it aborts, all its effects are undone.

  6. ACID Properties - 2 2. Consistency A transaction won't allow the system to arrive at an incorrect logical state. The data must always be logically correct.

  7. ACID Properties - 3 3. Isolation Separates concurrent transactions from the updates of other incomplete transactions Accomplished automatically using locking or row versions

  8. ACID Properties - 4 4. Durability Ensures that the effects of the transaction persist even if a system failure occurs Accomplished usingwrite-ahead logging and automatic rollback and roll-forward of transactions during the recovery phase

  9. Dependency/Consistency problems 1. Lost updates Two processes read the same data and both manipulate the data, changing its value, and then both try to update the original data to the new value

  10. Dependency/Consistency problems 2. Dirty reads Process reads uncommitted data Process has changed data but not yet committed the change, another process reading the data will read it in an inconsistent state

  11. Dependency/Consistency problems 3. Non-repeatable reads (inconsistent analysis) A process might get different values when reading the same resource in two separate reads within the same transaction Can happen when another process changes the data in between the reads that the first process is doing

  12. Dependency/Consistency problems 4. Phantom reads When membership in a set changes (a query with a predicatesuch as WHERE col1 < 100 is involved) Two SELECT operations using the same predicate in the same transaction return a different number of rows

  13. Isolation Levels 1. Uncommitted Read (ANSI/ISO SQL-92) All the problems described previously except lost updates are possibleimplemented by allowing your read operations to not take any locks

  14. Isolation Levels 2. Read Committed (ANSI/ISO SQL-92) The default isolation levelCan be either optimistic or pessimistic de-pending on the READ_COMMITTED_SNAPSHOTdatabase setting (ON/OFF)

  15. Isolation Levels 3. Repeatable Read (ANSI/ISO SQL-92) Ensuring that if a transaction revisits data that is already read, the data will not have changed. (Phantoms are possible!) All the shared locks in a transaction must be held until the completion

  16. Attention • Exclusive locks must always be held until the endof a transaction, no matter what the isolation level or concurrency model • Thus a transaction can be rolled back if necessary. • Otherwise might be impossible to undo the work because other concurrent transactions might have changed the data

  17. Isolation Levels 5. Snapshot (non ANSI/ISO SQL-92) Allows processes to read older versions of committed data if the current version is locked. Not in the standard but useful! Google for:A Critique of ANSI SQL Isolation Levels Interesting too:The Third Manifesto, by C.J. Date and H. Darwen(or why SQL sucks)

  18. Isolation Levels 5. Serializable Ensuring that if a query is reissued, rows will not have been added in the interim (phantoms will not appear). All the shared locks in a transaction must be held until completion of the transaction Not only lock data that has been read, but also lock data that does not exist!!!

  19. HOW READ UNCOMMITTED ALLOWS DIRTY READ

  20. READ COMMITTED without READ_COMMITTED_SNAPSHOT(HOW READ COMMITTED DOES NOT ALLOW DIRTY READS)

  21. RESULT • NO DIRTY READS • GOOD! • WRITERS BLOCK READERS • COULD BE BAD! LOWER CONCURRENCY! • ANOTHER SOLUTION • USE READ_COMMITTED_SNAPSHOT

  22. How to enable READ_COMMITTED_SNAPSHOT EXECUTE: ALTER DATABASE<DATABASE_NAME> SETREAD_COMMITTED_SNAPSHOT ON Enables the so called: MULTI-VERSION CONCURRENCY CONTROL (row versions are stored in the tempdb database)

  23. READ COMMITTED with READ_COMMITTED_SNAPSHOT

  24. RESULT • NO DIRTY READS • GOOD! • WRITERS DON’T BLOCK READERS • GOOD! • TRADEOFF • ROW VERSIONS SHOULD BE MANAGED • NON-REPEATABLE READ HAPPENS • COULD BE A PROBLEM

  25. SAME with REPEATABLE READ

  26. RESULT • NO NON-REPEATABLE READS • GOOD! • READERS BLOCK WRITERS • COULD BE A PROBLEM BECAUSE OF THE LOWER CONCURRENCY CAN USE THE SNAPSHOT ISOLATION LEVEL TO HAVE BOTH REPEATABLE READS AND READERS THAT DON’T BLOCK WRITERS

  27. HOW TO USE SNAPSHOT ISOLATION LEVEL EXECUTE: ALTER DATABASE<DATABASE_NAME> SETALLOW_SNAPSHOT_ISOLATION ON

  28. NOW THE SAME with SNAPSHOT

  29. Update conflict in SNAPSHOT ISOLATION LEVEL

  30. PHANTOMS EXAMPLE

  31. SNAPSHOT vs. SERIALIZABLE

More Related