Transcript Tansactions

Transactions generalities
Transactions - generalities
1
Transactions generalities
Transactions - example
 Parts
 P_id, P_name, Colour, Weight, Total_qty
 Contracted
 S_id, P_id, Qty
 Update
 add a new contract between ‘S4’ for 200 pieces of ‘P1’
2
Transactions generalities
Example extension
P_id
P_name
Colour Weight
Total_qty
P1
P2
…
gear
pin
…
white
black
…
1150
10000
…
S_id
S1
S1
S2
S3
S3
P_id
P1
P3
P1
P1
P2
1.233
0.1
…
Qty
500
200
150
500
1000
3
Transactions generalities
Transaction
 logical unit of work
 sequence of database operations
 transforms a consistent state of a db into
another consistent state
 between operations the db can be
inconsistent
4
Transactions generalities
Pseudo-code
BEGIN TRANSACTION;
INSERT (‘S4’, ‘P1’, 200) INTO Contracted;
IF any error occurred THEN GO TO undo;
UPDATE Parts WHERE P_id =‘P1’
SET Total_qty = Total_qty + 200;
IF any error occurred THEN GO TO undo;
COMMIT TRANSACTION;
GO TO finish;
undo: ROLLBACK TRANSACTION;
finish: RETURN;
5
Transactions generalities
SQL support
 COMMIT and ROLLBACK
 No BEGIN TRANSACTION (in SQL2 and
Oracle)
 all data definition and data manipulation
statements are transaction initiating
 PostgreSQL provides
BEGIN [TRANSACTION]
6
Transactions generalities
Transaction processing
 do not allow for
 one operation to be performed and the other ones not
 the system should guarantee
 that all constituent operations will be executed
 not possible! hence the following principle:
 principle of transaction processing support
 if some operations are executed and then a failure occurs
(before the planned termination) then those operations will be
undone
7
Transactions generalities
Transaction manager
 COMMIT TRANSACTION
 a logical unit of work was successfully completed
 all the updates can be made permanent
 ROLLBACK TRANSACTION
 unsuccessful end of transaction
 all the attempted updates must be rolled back
8
Transactions generalities
COMMIT and ROLLBACK points
COMMIT
idle
BEGIN
TRANSACTION
COMMIT
time
transaction time
same point as far as the
DB state is concerned
COMMIT
idle
BEGIN
TRANSACTION
ROLLBACK
time
transaction time
same state of DB
9
Transactions generalities
At the COMMIT point
 all updates, since the previous commit, are
made permanent (will not be undone)
 all database positioning and all tuple locks
are lost
10
Transactions generalities
ACID properties of transactions
 Atomicity
 all or nothing
 Consistency
 preserve database consistency
 Isolation
 transactions are isolated from one another
 Durability
 committed transaction  updates are performed
11
Transactions generalities
Pointers ahead
 recovery
 concurrency
12