-
What is a database transaction
any action that reads from and/or writes to a database
-
Describe a successful transaction
- one is which all of the SQL statements are completed successfully
- A successful transaction changes the database from one consistent state to another
-
A _______ database state is one in which all data integrity constraints are satisfied
consistent
-
4 properties of a transaction
- atomicity
- Consistency
- isolation
- durability
-
Describe the atomicity property
- "all or nothing" property
- All transaction operations must be completed
-
Describe the Consistency property
when a transaction is completed, the database must be in a consistent state
-
Describe the isolation property
Data used during the execution of a transaction cannot be used by a second transaction until the first one is complete
-
Describe the durability property
Once transaction changes are committed they cannot be undone or lost to a subsequent failure
-
Describe COMMIT (ANSI SQL standard)
- Permanently records all changes in the database
- Automatically ends the transaction
-
Describe ROLLBACK (ANSI SQL standard)
- aborts all uncommitted changes
- Database is rolled back to its previous state
- a rolled back transaction can typically be restarted later
-
What do transaction logs store
- A record for the beginning of the transaction
- A record for each transaction component (i.e. types of operation, tables/attributes affected, before and after values for attributes effected)
- A record for the end of the transaction (COMMIT)
-
Transaction logs are ______ __________ when the DBMS executes transactions that modify the database
automatically updates
-
What are some of the tradeoffs with transaction logs
- increases DBMS processing overhead
- but...provides ability to restore a corrupted database
-
When can you implement multi-step transactions
- When connecting from ...
- other office applications via VBA
- a .NET application
- a web-based application
-
MS Access only supports ________ transaction swhen using _____ to access the DBMS
Multistep, code
-
What is concurrency control
the process of managing simultaneous operations on the database without having them interfere with one another
-
Three potential problems caused by concurrency
- lost updates
- uncommitted data
- inconsistent retrievals
-
How does a lost update problem happen
when a successfully completed update is overwritten by another transaction
-
How does an uncommitted data problem happen
When one transaction accesses the intermediate results of another transaction before they are committed - and the second transaction is then rolled back
-
How does an inconsistent retrieval problem happen
When a transaction reads several values, but a different transaction updates some of the them in the midst o this process
-
What is a serializable schedule
A schedule of a transaction's operations in which the interleaved execution of all active transactions yields the same results as if those transactions were executedi n serial order
-
What are some methods for scheduling conflicting operations in concurrent transactions
- locking methods
- timestamping
-
refers to the size of the locked resource
- lock granularity
-
prohibits other users from reading the locked resource
exclusive lock
-
allows other users to read the locked resource but they can't update it
shared loc
-
Describe optimistic locking
- assumes that no transaction conflicts will occur
- DBMS processes a transaction to a temporary file, checks whether conflict occurred, if not it's finished if so the transaction is repeated until there is no conflict
- Acceptable for applications with few update operations
-
Describe pessimistic locking
- assumes that conflicts will occur
- locks are issued before a transaction is processed, then the locks are released
-
Two-phase locking(2PL)
- guarantees serializability
- transactionsa re allowed to obtain as many locks as necessary (growing phase)
- Once the first lock is released (shrinking phase) no additional locks can be obtained
- doesn't prevent deadlocks
-
What is a deadlock
an impasse that may result when two (or more) transactions are waiting for locks held by the other to be released
-
3 basic techniques for controlling deadlocks
- Deadlock prevention
- Deadlock detection
- Deadlock avoidance
-
Deadlock prevention
- abort a transaction if possible of deadlock
- Reschedule transaction for later execution
-
Deadlock detection
- DBMS periodically tests databasse for deadlocks
- If found, one transaction ("victim") is rolled back
-
Deadlock avoidance
transactions obtain all needed locks before execution
-
What is timestamping
- A unique identifier created by DBMS that indicates the relative starting time of a transaction
- Transactions ordered globally so that older transaction (with smaller timestamps) get priority in the event of conflict
- conflict resolved by rolling back and restarting the associated transaction - no locks, no deadlocks
- Demands a lot of system resources (memory and processing overhead)
|
|
