Transactions and Concurrency Flashcards
What are ‘Transactions’ in terms of SQL?
Logical unit of work – a “batch” of SQL commands. Consist of several database operations.
What does ‘ROLLBACK’ do?
Following the ROLLBACK all operations since the last COMMIT are undone
What does ‘COMMIT’ do?
Following a COMMIT the operations are done and have been saved for sure
What does Transaction Isolation Level control?
Whether locks are taken when data is read, and what type of locks are requested.
What are the 4 Isolation Levels?
- Read uncommitted (lowest level)
- Read committed
- Repeatable read
- Serialisable (highest level)
What do low levels of isolation improve?
Performance
What do low levels of isolation improve?
reliability/reduce inconsistency
What are the problems with Read Uncommitted?
- Another transaction might see data that gets rolled back.
2. Oracle will not allow read uncommitted although MySQL will
What is concurrency?
Many simultaneous users
What are the problems with Concurrency?
- Inconsistent retrieval
- Lost updates
- Uncommitted data
What does the phrase ‘FOR UPDATE’ do?
ensures that the row is locked and so another process will see the right data - ‘Exclusive Lock’
What is Locking?
A procedure used to control concurrent access to data
When one transaction is accessing the same database, what will a lock do?
- Ensure that the interleaving of low level steps can’t happen
- Ensure one transaction must be complete before another one can make use of the same data
What is a Deadlock?
A deadlock might occur when two (or more) transactions are each waiting for locks to be released that are held by the other
What can Transactions cause?
- A process to suffer a Read or Write fail
- A process to hang
- One or more processes to deadlock
