you are at >> basics >>transactions
Transactions
General
It is a collection of operations on the database, which form one whole. They should all be done or none of them. There can't be done partially. Terms of the transactions are more accurately described in terms of ACID.
In this course will be presented PostgreSQL transactions
. In this language transactions are described by 3 phrases:
- BEGIN WORK - start of transaction
- COMMIT WORK - All items included in the transaction are completed and should be comitted. And from this time there will be available for all simultaneous and subsequent transactions.
- ROLLBACK WORK - transaction has to be cancelled and all operations on database have to be cancelled
ACID
ACID defines terms which transaction has to meet.- Atomic - Transaction must be made as a single unit. It should be made at once and can't be devided on subsets.
- Consistency - System has to be integal after comittment of transaction
- Isolatation - Each transaction must be performed independently from other transactions that may be performed at the same time
- Durability - performed transaction has to be comitted permanently.
Anomalies
- dirty read - one transaction could read a database object A, modified by another transaction which hasn't committed.
- readings which can't be repeated - the reading of the data set, which when re-reading the same data gives a completely different result
- phantom read- occurs when a transaction reads or updates the tables, and the second transaction at this same time added a new line, which should be added later, the problem is similar to the previous
Isolation levels
Below are following levels of isolation of transaction- READ UNCOMMITTED - there are possible dirty read, readings which can't be repeated and phantom read
- READ COMMITTED - there is impossible dirty read, there are possible readings which can't be repeated and phantom read
- REPEATABLE READ - there are impossible dirty read, readings which can't be repeated there are possible phantom read
- SERIALIZABLE - there isn't possibility of occuring any of anomalies
READ COMMITTED is a default mode
Isolation level can be set by syntax given below:
set transaction isolaction level { isolation_level_name }
[ wróć na górę strony ]