CS计算机代考程序代写 database concurrency SQL 2021/4/28 Transaction Processing

程序代写 CS代考 / concurrency, database, SQL代写代考

2021/4/28 Transaction Processing
Transaction Processing
Transaction Processing Transaction Terminology Schedules
Transaction Anomalies
>>
COMP9315 21T1 ♢ Transaction Processing ♢ [0/12]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
1/14

2021/4/28 Transaction Processing
❖ Transaction Processing A transaction (tx) is …
a single application-level operation
performed by a sequence of database operations
A transaction effects a state change on the DB
∧ >>
COMP9315 21T1 ♢ Transaction Processing ♢ [1/12]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
2/14

2021/4/28 Transaction Processing
❖ Transaction Processing (cont) Transaction states:
<< ∧ >>
COMMIT⇒allchangespreserved, ABORT⇒database unchanged
COMP9315 21T1 ♢ Transaction Processing ♢ [2/12]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
3/14

2021/4/28 Transaction Processing
❖ Transaction Processing (cont) Concurrent transactions are
desirable, for improved performance (throughput)
problematic, because of potential unwanted interactions
To ensure problem-free concurrent transactions:
Atomic … whole effect of tx, or nothing
Consistent … individual tx’s are “correct” (wrt application) Isolated … each tx behaves as if no concurrency Durable … effects of committed tx’s persist
COMP9315 21T1 ♢ Transaction Processing ♢ [3/12]
<< ∧ >>
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
4/14

2021/4/28 Transaction Processing
❖ Transaction Processing (cont) Transaction processing:
the study of techniques for realising ACID properties
Consistency is the property:
a tx is correct with respect to its own specication
a tx performs a mapping that maintains all DB constraints
Ensuring this must be left to application programmers. Our discussion focusses on: Atomicity, Durability, Isolation
COMP9315 21T1 ♢ Transaction Processing ♢ [4/12]
<< ∧ >>
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
5/14

2021/4/28 Transaction Processing
<< ∧ >>
❖ Transaction Processing (cont)
Atomicity is handled by the commit and abort mechanisms
commit ends tx and ensures all changes are saved abort ends tx and undoes changes “already made”
Durability is handled by implementing stable storage, via redundancy, to deal with hardware failures
logging/checkpoint mechanisms, to recover state
Isolation is handled by concurrency control mechanisms possibilities: lock-based, timestamp-based, check-based various levels of isolation are possible (e.g. serializable)
COMP9315 21T1 ♢ Transaction Processing ♢ [5/12]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
6/14

2021/4/28 Transaction Processing
<< ∧ >>
❖ Transaction Processing (cont) Where transaction processing ts in the DBMS:
COMP9315 21T1 ♢ Transaction Processing ♢ [6/12]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
7/14

2021/4/28 Transaction Processing
❖ Transaction Terminology
To describe transaction effects, we consider:
READ – transfer data from “disk” to memory WRITE – transfer data from memory to “disk” ABORT – terminate transaction, unsuccessfully COMMIT – terminate transaction, successfully
Relationship between the above operations and SQL: SELECT produces READ operations on the database UPDATE and DELETE produce READ then WRITE operations INSERT produces WRITE operations
COMP9315 21T1 ♢ Transaction Processing ♢ [7/12]
<< ∧ >>
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
8/14

2021/4/28 Transaction Processing
❖ Transaction Terminology (cont) More on transactions and SQL
BEGIN starts a transaction
the begin keyword in PLpgSQL is not the same thing
COMMIT commits and ends the current transaction some DBMSs e.g. PostgreSQL also provide END as a synonym the end keyword in PLpgSQL is not the same thing
ROLLBACK aborts the current transaction, undoing any changes
some DBMSs e.g. PostgreSQL also provide ABORT as a synonym In PostgreSQL, tx’s cannot be dened inside functions (e.g. PLpgSQL)
COMP9315 21T1 ♢ Transaction Processing ♢ [8/12]
<< ∧ >>
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
9/14

2021/4/28 Transaction Processing
❖ Transaction Terminology (cont) The READ, WRITE, ABORT, COMMIT operations:
occur in the context of some transaction T involvemanipulationofdataitemsX,Y,… (READandWRITE)
The operations are typically denoted as:
RT(X) read item X in transaction T WT(X) write item X in transaction T AT abort transaction T
CT commit transaction T
COMP9315 21T1 ♢ Transaction Processing ♢ [9/12]
<< ∧ >>
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
10/14

2021/4/28 Transaction Processing
<< ∧ >>
❖ Schedules
A schedule gives the sequence of operations from ≥ 1 tx
Serial schedule for a set of tx’s T1 .. Tn
all operations of Ti complete before Ti+1 begins
E.g. RT1(A) WT1(A) RT2(B) RT2(A) WT3(C) WT3(B) Concurrent schedule for a set of tx’s T1 .. Tn
operations from individual Ti’s are interleaved E.g. RT1(A) RT2(B) WT1(A) WT3(C) WT3(B) RT2(A)
COMP9315 21T1 ♢ Transaction Processing ♢ [10/12]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
11/14

2021/4/28 Transaction Processing
<< ∧ >>
❖ Schedules (cont)
Serial schedules guarantee database consistency
eachTi commitsbeforeTi+1 starts
priortoTi databaseisconsistent
afterTi databaseisconsistent (assumingTiiscorrect) beforeTi+1 databaseisconsistent…
Concurrent schedules interleave tx operations arbitrarily and may produce a database that is not consistent
after all of the transactions have committed successfully
COMP9315 21T1 ♢ Transaction Processing ♢ [11/12]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html
12/14

2021/4/28 Transaction Processing
❖ Transaction Anomalies
What problems can occur with (uncontrolled) concurrent tx’s? The set of phenomena can be characterised broadly under:
dirty read:
reading data item written by a concurrent uncommitted tx
nonrepeateable read:
re-reading data item, since changed by another concurrent tx
phantom read:
re-scanning result set, nding it changed by another tx
COMP9315 21T1 ♢ Transaction Processing ♢ [12/12]
<< ∧ https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html 13/14 2021/4/28 Transaction Processing Produced: 11 Apr 2021 https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/tx-intro/slides.html 14/14

Related Posts