18
play

18 Concurrency Control Intro to Database Systems Andy Pavlo AP - PowerPoint PPT Presentation

Nov 07, 2022 •363 likes •1.5k views

Timestamp Ordering 18 Concurrency Control Intro to Database Systems Andy Pavlo AP AP 15-445/15-645 Computer Science Carnegie Mellon University Fall 2019 2 CO N CURREN CY CO N TRO L APPROACH ES Two-Phase Locking (2PL) Pessimistic

  1. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE WRITE COMMIT W(A) VALIDATE WRITE COMMIT CMU 15-445/645 (Fall 2019)

  2. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE WRITE COMMIT W(A) VALIDATE WRITE COMMIT CMU 15-445/645 (Fall 2019)

  3. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT Object Value W-TS W(A) - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  4. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT Object Value W-TS W(A) A 123 0 - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  5. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE T 1 Workspace T 2 Workspace WRITE COMMIT Object Value W-TS Object Value W-TS W(A) A 123 0 - - - - - - VALIDATE - - - - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  6. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE T 1 Workspace T 2 Workspace WRITE COMMIT Object Value W-TS Object Value W-TS W(A) A 123 0 A 123 0 - - - - - - VALIDATE - - - - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  7. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ TS(T 2 )=1 R(A) TIM E VALIDATE T 1 Workspace T 2 Workspace WRITE COMMIT Object Value W-TS Object Value W-TS W(A) A 123 0 A 123 0 - - - - - - VALIDATE - - - - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  8. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ TS(T 2 )=1 R(A) TIM E VALIDATE T 1 Workspace T 2 Workspace WRITE COMMIT Object Value W-TS Object Value W-TS W(A) A 123 0 A 123 0 - - - - - - VALIDATE - - - - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  9. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ TS(T 2 )=1 R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT Object Value W-TS W(A) A 123 0 - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  10. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ TS(T 2 )=1 R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT Object Value W-TS W(A) A 456 123 456 0 ∞ 1 - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  11. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ TS(T 2 )=1 R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT TS(T 1 )=2 Object Value W-TS W(A) A 456 123 456 ∞ 0 1 - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  12. 20 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 456 0 2 READ - - - R(A) READ TS(T 2 )=1 R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT TS(T 1 )=2 Object Value W-TS W(A) A 123 456 456 ∞ 0 1 - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  13. 21 O CC VALIDATIO N PH ASE The DBMS needs to guarantee only serializable schedules are permitted. T i checks other txns for RW and WW conflicts and makes sure that all conflicts go one way (from older txns to younger txns). CMU 15-445/645 (Fall 2019)

  14. 22 O CC SERIAL VALIDATIO N Maintain global view of all active txns. Record read set and write set while txns are running and write into private workspace. Execute Validation and Write phase inside a protected critical section. CMU 15-445/645 (Fall 2019)

  15. 23 O CC READ PH ASE Track the read/write sets of txns and store their writes in a private workspace. The DBMS copies every tuple that the txn accesses from the shared database to its workspace ensure repeatable reads. CMU 15-445/645 (Fall 2019)

  16. 24 O CC VALIDATIO N PH ASE Each txn's timestamp is assigned at the beginning of the validation phase. Check the timestamp ordering of the committing txn with all other running txns. If TS(T i ) < TS(T j ) , then one of the following three conditions must hold… CMU 15-445/645 (Fall 2019)

  17. 25 O CC VALIDATIO N PH ASE When the txn invokes COMMIT , the DBMS checks if it conflicts with other txns. Two methods for this phase: → Backward Validation → Forward Validation CMU 15-445/645 (Fall 2019)

  18. 26 O CC BACKWARD VALIDATIO N Check whether the committing txn intersects its read/write sets with those of any txns that have already committed. Txn #1 COMMIT Txn #2 COMMIT Txn #3 COMMIT TIME CMU 15-445/645 (Fall 2019)

  19. 26 O CC BACKWARD VALIDATIO N Check whether the committing txn intersects its read/write sets with those of any txns that have already committed. Txn #1 COMMIT Txn #2 COMMIT Txn #3 COMMIT TIME CMU 15-445/645 (Fall 2019)

  20. 26 O CC BACKWARD VALIDATIO N Check whether the committing txn intersects its read/write sets with those of any txns that have already committed. Validation Scope Txn #1 COMMIT Txn #2 COMMIT Txn #3 COMMIT TIME CMU 15-445/645 (Fall 2019)

  21. 27 O CC FO RWARD VALIDATIO N Check whether the committing txn intersects its read/write sets with any active txns that have not yet committed. Txn #1 COMMIT Txn #2 COMMIT Txn #3 COMMIT TIME CMU 15-445/645 (Fall 2019)

  22. 27 O CC FO RWARD VALIDATIO N Check whether the committing txn intersects its read/write sets with any active txns that have not yet committed. Txn #1 COMMIT Txn #2 COMMIT Txn #3 COMMIT TIME CMU 15-445/645 (Fall 2019)

  23. 27 O CC FO RWARD VALIDATIO N Check whether the committing txn intersects its read/write sets with any active txns that have not yet committed. Txn #1 COMMIT Validation Scope Txn #2 COMMIT Txn #3 COMMIT TIME CMU 15-445/645 (Fall 2019)

  24. 30 O CC VALIDATIO N STEP # 1 T i completes all three phases before T j begins. CMU 15-445/645 (Fall 2019)

  25. 31 O CC VALIDATIO N STEP # 1 Schedule T 1 T 2 BEGIN READ VALIDATE WRITE TIM E COMMIT BEGIN READ VALIDATE WRITE COMMIT CMU 15-445/645 (Fall 2019)

  26. 32 O CC VALIDATIO N STEP # 2 T i completes before T j starts its Write phase, and T i does not write to any object read by T j . → WriteSet(T i ) ∩ ReadSet(T j ) = Ø CMU 15-445/645 (Fall 2019)

  27. 33 O CC VALIDATIO N STEP # 2 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) W(A) READ TIM E R(A) T 1 Workspace T 2 Workspace VALIDATE Object Value W-TS Object Value W-TS VALIDATE - A - 123 - 0 ∞ - A - 123 - 0 WRITE - - - - - - COMMIT CMU 15-445/645 (Fall 2019)

  28. 33 O CC VALIDATIO N STEP # 2 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) W(A) READ TIM E R(A) T 1 Workspace T 2 Workspace VALIDATE Object Value W-TS Object Value W-TS VALIDATE - A - 123 - 0 ∞ - A - 123 - 0 WRITE - - - - - - COMMIT T 1 has to abort even though T 2 will never write to the database. CMU 15-445/645 (Fall 2019)

  29. 34 O CC VALIDATIO N STEP # 2 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) W(A) READ TIM E R(A) T 1 Workspace T 2 Workspace VALIDATE VALIDATE Object Value W-TS Object Value W-TS WRITE - A - 456 - 0 ∞ - A - 123 - 0 COMMIT WRITE - - - - - - COMMIT CMU 15-445/645 (Fall 2019)

  30. 34 O CC VALIDATIO N STEP # 2 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) W(A) READ TIM E R(A) T 1 Workspace T 2 Workspace VALIDATE VALIDATE Object Value W-TS Object Value W-TS WRITE - A - 456 - 0 ∞ - A - 123 - 0 COMMIT WRITE - - - - - - COMMIT CMU 15-445/645 (Fall 2019)

  31. 34 O CC VALIDATIO N STEP # 2 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) W(A) READ TIM E R(A) T 1 Workspace T 2 Workspace VALIDATE VALIDATE Object Value W-TS Object Value W-TS WRITE - A - 456 - 0 ∞ - A - 123 - 0 COMMIT WRITE - - - - - - COMMIT Safe to commit T 1 because we know that T 2 will not write. CMU 15-445/645 (Fall 2019)

  32. 35 O CC VALIDATIO N STEP # 3 T i completes its Read phase before T j completes its Read phase And T i does not write to any object that is either read or written by T j : → WriteSet(T i ) ∩ ReadSet(T j ) = Ø → WriteSet(T i ) ∩ WriteSet(T j ) = Ø CMU 15-445/645 (Fall 2019)

  33. 36 O CC VALIDATIO N STEP # 3 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ B XYZ 0 R(A) W(A) READ TIM E R(B) T 1 Workspace T 2 Workspace VALIDATE WRITE Object Value W-TS Object Value W-TS COMMIT R(A) 123 0 XYZ 0 B - A - 456 - ∞ - - - VALIDATE - - - - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  34. 36 O CC VALIDATIO N STEP # 3 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ B XYZ 0 R(A) W(A) TS(T 1 )=1 READ TIM E R(B) T 1 Workspace T 2 Workspace VALIDATE WRITE Object Value W-TS Object Value W-TS COMMIT R(A) 123 0 XYZ 0 B - A - 456 - ∞ - - - VALIDATE Safe to commit T 1 because - - - - - - WRITE COMMIT T 2 sees the DB after T 1 has executed. CMU 15-445/645 (Fall 2019)

  35. 36 O CC VALIDATIO N STEP # 3 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 456 1 READ B XYZ 0 R(A) W(A) TS(T 1 )=1 READ TIM E R(B) T 1 Workspace T 2 Workspace VALIDATE WRITE Object Value W-TS Object Value W-TS COMMIT R(A) 123 0 XYZ 0 B - A - 456 - ∞ - - - VALIDATE - - - - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  36. 36 O CC VALIDATIO N STEP # 3 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 456 1 READ B XYZ 0 R(A) W(A) READ TIM E R(B) T 2 Workspace VALIDATE WRITE Object Value W-TS COMMIT R(A) XYZ 0 B - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  37. 36 O CC VALIDATIO N STEP # 3 Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 456 1 READ B XYZ 0 R(A) W(A) READ TIM E R(B) T 2 Workspace VALIDATE WRITE Object Value W-TS COMMIT R(A) XYZ 0 B - - - VALIDATE A 456 1 - - - WRITE COMMIT CMU 15-445/645 (Fall 2019)

  38. 37 O CC O BSERVATIO N S OCC works well when the # of conflicts is low: → All txns are read-only (ideal). → Txns access disjoint subsets of data. If the database is large and the workload is not skewed, then there is a low probability of conflict, so again locking is wasteful. CMU 15-445/645 (Fall 2019)

  39. 38 O CC PERFO RM AN CE ISSUES High overhead for copying data locally. Validation/Write phase bottlenecks. Aborts are more wasteful than in 2PL because they only occur after a txn has already executed. CMU 15-445/645 (Fall 2019)

  40. 39 O BSERVATIO N When a txn commits, all previous T/O schemes check to see whether there is a conflict with concurrent txns. → This requires latches. If you have a lot of concurrent txns, then this is slow even if the conflict rate is low. CMU 15-445/645 (Fall 2019)

  41. 40 PARTITIO N- BASED T/ O Split the database up in disjoint subsets called horizontal partitions (aka shards). Use timestamps to order txns for serial execution at each partition. → Only check for conflicts between txns that are running in the same partition. CMU 15-445/645 (Fall 2019)

  42. 41 DATABASE PARTITIO N IN G CREATE TABLE customer ( c_id INT PRIMARY KEY , c_email VARCHAR UNIQUE , ⋮ CREATE TABLE orders ( ); o_id INT PRIMARY KEY , o_c_id INT REFERENCES ⮱ customer (c_id), ⋮ CREATE TABLE oitems ( ); oi_id INT PRIMARY KEY, oi_o_id INT REFERENCES ⮱ orders (o_id), oi_c_id INT REFERENCES ⮱ orders (o_c_id), ⋮ ); CMU 15-445/645 (Fall 2019)

  43. 41 DATABASE PARTITIO N IN G CREATE TABLE customer ( c_id INT PRIMARY KEY , c_email VARCHAR UNIQUE , ⋮ CREATE TABLE orders ( ); o_id INT PRIMARY KEY , o_c_id INT REFERENCES ⮱ customer (c_id), ⋮ CREATE TABLE oitems ( ); oi_id INT PRIMARY KEY, oi_o_id INT REFERENCES ⮱ orders (o_id), oi_c_id INT REFERENCES ⮱ orders (o_c_id), ⋮ ); CMU 15-445/645 (Fall 2019)

  44. 42 H O RIZO N TAL PARTITIO N IN G Partitions CUSTOMERS Customers BEGIN 1-1000 ORDERS OITEMS Application Server Customers CUSTOMERS 1001-2000 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  45. 42 H O RIZO N TAL PARTITIO N IN G Partitions CUSTOMERS Customers COMMIT 1-1000 ORDERS OITEMS Application Server Customers CUSTOMERS 1001-2000 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  46. 43 PARTITIO N- BASED T/ O Txns are assigned timestamps based on when they arrive at the DBMS. Partitions are protected by a single lock: → Each txn is queued at the partitions it needs. → The txn acquires a partition’s lock if it has the lowest timestamp in that partition’s queue. → The txn starts when it has all of the locks for all the partitions that it will read/write. CMU 15-445/645 (Fall 2019)

  47. 44 PARTITIO N - BASED T/ O READS Txns can read anything that they want at the partitions that they have locked. If a txn tries to access a partition that it does not have the lock, it is aborted + restarted. CMU 15-445/645 (Fall 2019)

  48. 45 PARTITIO N - BASED T/ O WRITES All updates occur in place. → Maintain a separate in-memory buffer to undo changes if the txn aborts. If a txn tries to write to a partition that it does not have the lock, it is aborted + restarted. CMU 15-445/645 (Fall 2019)

  49. 46 PARTITIO N- BASED T/ O Partitions Txn Queue CUSTOMERS Customers 1-1000 ORDERS Server #1 OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  50. 46 PARTITIO N- BASED T/ O Partitions Txn Queue BEGIN Server1: 100 CUSTOMERS Customers Server2: 101 1-1000 ORDERS Server #1 BEGIN OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  51. 46 PARTITIO N- BASED T/ O Partitions Txn Queue Txn #100 BEGIN Server1: 100 CUSTOMERS Customers Server2: 101 1-1000 ORDERS Server #1 BEGIN OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  52. 46 PARTITIO N- BASED T/ O Partitions Txn Queue Txn #100 Get C_ID=1 Server1: 100 CUSTOMERS Customers Server2: 101 1-1000 ORDERS Server #1 BEGIN OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  53. 46 PARTITIO N- BASED T/ O Partitions Txn Queue Txn #100 COMMIT Server1: 100 CUSTOMERS Customers Server2: 101 1-1000 ORDERS Server #1 BEGIN OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  54. 46 PARTITIO N- BASED T/ O Partitions Txn Queue CUSTOMERS Customers Server2: 101 1-1000 ORDERS Server #1 BEGIN OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  55. 46 PARTITIO N- BASED T/ O Partitions Txn Queue Server2: 101 CUSTOMERS Customers 1-1000 ORDERS Server #1 BEGIN OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  56. 46 PARTITIO N- BASED T/ O Partitions Txn Queue Txn #101 Server2: 101 CUSTOMERS Customers 1-1000 ORDERS Server #1 BEGIN OITEMS Customers CUSTOMERS 1001-2000 Server #2 ORDERS OITEMS CMU 15-445/645 (Fall 2019)

  57. 47 PARTITIO N ED T/ O PERFO RM AN CE ISSUES Partition-based T/O protocol is fast if: → The DBMS knows what partitions the txn needs before it starts. → Most (if not all) txns only need to access a single partition. Multi-partition txns causes partitions to be idle while txn executes. CMU 15-445/645 (Fall 2019)

  58. 48 DYN AM IC DATABASES Recall that so far we have only dealing with transactions that read and update data. But now if we have insertions, updates, and deletions, we have new problems… CMU 15-445/645 (Fall 2019)

  59. 49 TH E PH AN TO M PRO BLEM Schedule CREATE TABLE people ( id SERIAL , T 1 T 2 name VARCHAR , BEGIN BEGIN age INT , SELECT MAX (age) status VARCHAR 72 FROM people ); WHERE status='lit' TIM E INSERT INTO people (age=96, status='lit') COMMIT SELECT MAX (age) 96 FROM people WHERE status='lit' COMMIT CMU 15-445/645 (Fall 2019)

  60. 50 WTF? How did this happen? → Because T 1 locked only existing records and not ones under way! Conflict serializability on reads and writes of individual items guarantees serializability only if the set of objects is fixed. CMU 15-445/645 (Fall 2019)

  61. 51 PREDICATE LO CKIN G Lock records that satisfy a logical predicate: → Example: status='lit' In general, predicate locking has a lot of locking overhead. Index locking is a special case of predicate locking that is potentially more efficient. CMU 15-445/645 (Fall 2019)

  62. 52 IN DEX LO CKIN G If there is a dense index on the status field then the txn can lock index page containing the data with status='lit' . If there are no records with status='lit' , the txn must lock the index page where such a data entry would be, if it existed. CMU 15-445/645 (Fall 2019)

Recommend

More recommend