Transactions

STUDY
PLAY

Terms in this set (...)

ACID
Atomicity: states that database modifications must follow an all or nothing rule.

Consistency: states that only valid data will be written to the database.

Isolation: requires that multiple transactions occurring at the same time not impact each others execution.

Durability: ensures that any transaction committed to the database will not be lost.
Transaction
A transaction either COMMIT after completing all its actions or ABORT after executing some actions.

A user can think of a transaction as always executing all its actions in one step, or not executing any actions at all.

DBMS logs all actions so that it can undo the actions of aborted transactions.
Conflicts of interleaved execution
The different schedules of transactions might ignore the operations finalized by other transactions.

Reason: conflicts with Read/Write operations

Conflicts are caused by the isolation property of transactions.

The isolation property might cause an anomaly in the database.

Four possible combination of Read/Write operations:
Read/Read, (Reading does not alter the state of the database. Just reading is always safe!)
Write/Read,
Read/Write,
Write/Write
Strict Two Phase-Locking (2PL)
We define two locks on objects, Shared and Exclusive.

We denote with X(A) an exclusive lock on an object A. We denote with S(A) a shared lock.

The Strict 2PL scheduler ensures that no anomalies arise from the interleaved executions of the transactions.
2PL Rules
A transaction that wants to read an object requests a shared lock.

A transaction that wants to write an object requests an exclusive lock.

A transaction is allowed to release all the locks only after it commits or aborts (so rollback is executed before another transaction can use the data).
Locking on tables
Query for T1:
SELECT type FROM ships WHERE firepower = 1500

Query for T2
UPDATE ships SET type = "Star Destroyer" WHERE firepower >= 1500

Set a shared lock for T1 on the entire table, or set a shared lock only on the rows with firepower = 1500 (more concurrency).
Transaction in PostgreSQL
BEGIN TRANSACTION begins a transaction.

ISOLATION LEVEL sets the isolation level as seen above.

COMMIT commits the transaction.

Example:
BEGIN TRANSACTION ISOLATION LEVEL
REPEATABLE READ;
SELECT name
FROM ships
WHERE firepower = 1500;
COMMIT;
Deadlock prevention
Build a graph where each node is mapped to a transaction

An arrow starts from T1 and points to T2 if T1 is waiting for a lock to be released by T2.

If we have a cycle in such graph then there is a deadlock.

The DBMS scheduler aborts transactions involved in a deadlock to free their locks.
Lecture 3
Neem de presentatie door want dit is veel te weinig en goeie filmpjes op youtube is niet mogelijk.