Transaction Management/transactSQL Flashcards
What are transactions and what causes complications to arise?
A series of queries. The complication arises from that many might happen at the same time (concurrency) and that computers can fail (partial execution)
What can concurrency lead to?
- Inconsistent data in the database
- (for example two people being able to book the same seat in a cinema or the following person overriding the first person’s booking)
What are transactions?
- They state that a group of SQL statements must be executed together so that no conflicts arise
- Transactions mean that specific events have to happen before another event
- meaning that we can’t for example have two people book the same seat as we’ll have a transaction that means once the seat has been picked, another person can’t pick it.
how do we ensure serialisable behaviour?
- By telling a DBMS that a sequence of SQL statements forms a transaction.
- It then knows that the transaction is to be executed as if in isolation from all other transactions
What is partial execution?
- Partial execution is when only part of a transaction gets carried out
- This could happen due to a failure or a power outage
How do DBMSs deal with partial execution?
- Transactions make sure that the whole set of instructions in a transaction is carried out otherwise the actions done are dropped/not permanently implemented. Either none or all of the commands are done.
How do we state in SQL that a transaction should be executed as a whole or not at all?
Using the keywords ‘START TRANSACTION’ and ‘COMMIT’
What do we do if we have to abort a transaction due to partial execution due to a failure?
Use the keyword ROLLBACK - if something fails and you don’t want any of the transaction to happen.
Why can we ignore operations such a calculations when looking at transactions?
- Because the OS does those operations
What does read(X) do?
- reads a database item X into a program variable (also named x for simplicity)
- finds address of disk block that contains item x
- copies that disk block into a buffer in main memory
- copy item x from buffer to program variable
What does write(x)
- finds address of disk block that contains item x
- copies that disk block into a buffer in main memory
- the program copies the new value into the correct location of item x in the buffer
- either immediately or some time after it writes the item in the bugger to the correct disk block
What is a schedule?
- a bunch of transactions in a specific order
What are the two main types of schedules?
- Serial schedules: executes transactions one after another
- Concurrent schedules: interleaves operations from different transactions, while still preserving that the operation in each transaction happen in the right order
What do each of the symbols represent?
Sn = id of the schedule
ri(x) = read(x) in transaction i
wi(x) = write(x) in transaction i
ci = commit in transaction i
ai = abort (“rollback”) in transaction i
Does order matter in serial schedules?
Yes because the data stored in shared variables between transactions can be different depending on transaction order
what is true about serial and concurrent schedules?
- All serial schedules are concurrent schedules
- But not all concurrent schedules are serial schedules
When is something not a concurrent schedule?
- When the order of the SQL statements inside the same transaction are executed out of order
Why can it be a problem that two transactions are not isolated from each other?
How can we solve this?
- issue of concurrent access
- if two transaction are accessing the same item then the outcome can be inconsistent with the real world.
- can solve this by undoing the second transaction
What does it mean for a transaction should be atomic?
the transaction is indivisible
Why can it be a problem if there’s a failure in the middle of a transaction?
How can we solve this?
- issue of partial execution
- leads to inconsistencies in the real world
- can solve by just undoing the transaction when the computer comes back online
What is durability?
That any later changes did not disappear after having finished based on something someone else did in the database
- so one transaction did not incorrectly overwrite an item that another one wrote to previously
What do we use to make ensure that we’re never in the situation of having errors made due to a failure (partial execution) and concurrent access?
ACID
What are the properties of ACID?
- A: Atomicity C: Consistency I: Isolation D: Durability
What is atomicity?
A transaction is an atomic unit of processing
- an indivisible unit of execution
- executed in it’s entirety or not at all
Deals with failure by aborting a transaction
- we undo the word done up to the error point
- system recreates state of database before start of aborted transaction
Commit - no errors, entire transaction is executed, system is updated appropriately