Introduction, Transactions, Concurrency Control and Recovery Flashcards
What are database transactions
query
update
transaction
What is a query?
a query is a request to the DBMS to retrieve data from the database
What is a update?
an update requests the DBMS to insert, delete or modify existing data
What is a transaction?
a transaction is a unit of work performed against a database, and treated in a coherent and reliable way independent of other transactions.
transactions are logical groupings of query and update requests to perform a task
Transactions management only applies to the DML. why?
this encompasses the four basic functions of persistent storage, create, read, update and delete (CRUD)
FACTS about transactions
each SQL command run is treated as a transaction
How do you define a multi step transaction
START TRANSACTION or BEGIN starts a new transaction
COMMIT to commit (Save) the current transaction making its changes permanent
usually used to ensure;
complex data integrity
transaction states are?
two outcomes;
SUCCESS
FAILURE
Describe Failure
transaction aborts, and database must be resorted to consistent state before it started. such a transaction is rolled back or undone.
Describe Success
transaction commits and database reaches a new consistent state
a committed transaction cannot be ABORTED!
Transaction managers are components of a DBMS.
describe the two main purposes.
- to provide reliable units of work
2. to provide isolation between programs accessing a database concurrently.
to ensure these goals, DB transactions must be:
atomic
consistent
isolated
durable
Explain atomicity
either all the operations associated with a transaction happen or none of them happen
Explain consistency
a transaction must transform the database from one consistent state to another, ensuring all predefined rules are adhered to
e.g. foreign keys
Explain Isolation
the result of the execution of concurrent
transactions is the same as if transactions were executed serially
Explain Durability
the effects of completed transactions become permanent surviving any subsequent failures
Why is transaction manager necessary/
Concurrency
Failure Tolerance
what is concurrency?
we want to allow multiple OPERATIONS to be performed at the SAME TIME so as to increase db THROUGHPUT
support by multi-threading, multi-processors
What is failure tolerance
we want the DB to be resilient from system crashes due to hardware or software failures
What would happen if DBMS has poor transaction management…?
lost updates
uncommitted dependencies
inconsistent analysis
Lost updates are?
an apparently successful completed
update by one user can be overridden by another user
what are uncommitted dependencies?
one transaction is allowed to see intermediate results of another transaction
before it has committed
what are inconsistent analysis?
a transaction reads several values from the DB but a second transaction updates
some of them during the execution of the first
A solution to these issues transactions are SERIAL schedules. explain what this is
allows only one transaction to execute at at time
what is a non-serial schedule?
is where the operations from a set of concurrent transactions are interleaved
what is serialisability?
if a set of transactions execute concurrently, we say that the non-serial schedule is correct if it produces the same results as some serial execution (this schedule is called serialisable)
how to recover schedules?
for each pair of transactions Ta, and TB, if Tb reads a data item previously written by Ta, then the commit operation of Ta, must precede the commit operation of Tb
What are concurrency control schemes?
a trade-off between the amount of concurrency they allow and the amount of overhead that they incur
what is a concurrency control
is the process of managing simultaneous operations on the DB without having them interfere with one another
