Flashcards
Databases Topic 7
Flashcards do conjunto (39)
- 1What is a transaction?A transaction is a logical unit of work that must be either entirely completed or aborted; no immediate states are acceptable.
- Most real-world database transactions are formed by two or more database requests. - 2Eg. Transactionsread(balx) (t=1)
balx = balx + 10 (t=2)
write(balx) (t=3)
commit (t=4)
At t=1: Reads the balance of x
At t=3: Writes the new balance of x
'Commit' statement tells the DBMS the transaction is complete. - 3Transaction Properties (1/2)Atomicity requires that all operations of a transaction be completed; if not, the transaction is aborted.
Consistency - Must transform database from one consistent state to another. - 4Transaction Properties (2/2)Isolation means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
Durability indicates the permanence of the database's consistent state. - 5Transaction SupportTransaction can have 1 or 2 outcomes:
- Success: transaction commits and database reaches a new consistent state.
- Failure: transaction aborts, and database must be restored to consistent state before it started. - 6Concurrency Control (1/3)Concurrency control refers to the coordination of concurrent transactions in a multiprocessing database system.
The transactions are processed concurrently, although to the users it appears simultaneous. - 7Concurrency Control (2/3)Concurrency control is the process of managing simultaneous operations on the database without having them to interfere with one another.
- 8Concurrency Control (3/3)Prevents interference when two or more users are accessing database simultaneously and at least one is updating data.
- 9Problems with concurrency controlIf two trans both access the same database item, it is possible for their operations to become interleaved in a way that leaves the database inconsistent.
- 10Three potential problems:Lost Updates
Uncommitted data
Inconsistent retrievals - 11Lost UpdatesCorrect way. However, if
- 12Con't Lost UpdatesT1's update has been lost - T2 has overwritten the result of T1.
- 13Uncommitted DataOccurs when one transaction can see intermediate results of another transaction before it has committed.
- 14Inconsistent RetrievalsWhen transaction reads several values but second transaction updates some of them during execution of first.
Sometimes referred to as dirty read or unrepeatable read. - 15Summary of concurrency problems (1/3)Lost update problem: Occurs when an apparently successfully completed update operation by one user is overridden by another user.
- 16Summary of concurrency problems (2/3)Uncommitted data: Occurs when one transaction can see intermediate results of another transaction before it has committed.
- 17Summary of concurrency problems (3/3)Inconsistent retrievals: Occurs when a transaction reads several values but a second transaction updates some of them during execution of first.
- 18SchedulingTo avoid these problems
- A schedule is a sequence of the operations by a set of concurrent transactions that preserves the order of the operations in each of the individual transactions. - 19Types of Schedules: SerialAll statements in each transaction are executed consecutively without any interleaved operations from other transactions.
- 20Types of Schedules: Non SerialOperations of the different transactions are interleaved.
Ensure that if a set of transactions executes concurrently it produces the same results as some serial execution.
- Such schedule is called serialisable. - 21SerialisabilityThree techniques to guarantee serialisability:
- Locking
- Time stamping
-Optimistic techniques - 22Locking methodsConcurrency can be controlled using locks.
Lock guarantees exclusive use of a data item to a current transaction.
Locks can be either placed by command or by the DBMS. - 23Lock GranularityRefers to the level of the database which the lock is applied.
- Can be database, table, page, row and field. - 24Lock Granularity - LevelsDatabase level: Entire database is locked.
Table level: Entire table is locked.
Page level: The page containing the record required is locked.
Row level: Allows concurrent access to different rows in the same table.
Field level: High maintenance cost. - 25Lock typesTwo main types of lock - Binary and Shared/Exclusive
- 26Binary LockHas only two states: Locked (1) or unlocked (0)
Each transaction requires a lock and unlock operation for each data item that is accessed.
Very restrictive - No need lock if only reading not even updating a database - 27Shared/Exclusive LockIf transaction has a shared lock on an item, it may read but not modify the item.
If transaction has an exclusive lock it may read and modify the item. - 28Potential problems with locksThe resulting transaction schedule may not be serialisable.
The schedule may create deadlocks - 29Solution with locking methodsTwo-phase locking for serializability problem.
Deadlock detection and prevention techniques for the deadlock problem. - 30Two-Phase Locking (TPL)It guarantees seriallisability, but it does not prevent deadlocks.
Growing phase: Acquires all locks
Shrinking Phase: Releases all locks - 31DeadlocksAn impasse that may result when 2 transactions are each waiting for locks to be released that are held by the other Eg.
T1 has data item X and need Y
T2 has data item Y and need X
Neither can unlock as still in growing phase. - 32Solution to deadlocksOnly one way to break deadlock: abort one or more of the transactions.
Three techniques for handling deadlock:
- Timeouts.
- Deadlock prevention.
- Deadlock detection and recovery. - 33TimeoutsTransaction that requests lock will only wait for a system-defined period of time.
If lock has not been granted within this period, lock request times out.
DBMS assumes transaction may be deadlocked, it aborts and auto restarts transaction. - 34Deadlock PreventionDBMS looks ahead to see if transaction would cause deadlock and never allows deadlock to occur.
- 35Deadlock Detection and RecoveryDBMS allows deadlock to occur but recognizes it and breaks it.
- 36Recovery From Deadlock DetectionChoice of deadlock victim
How far to roll a transaction back
Avoiding starvation - 37Concurrency control with Time Stamping MethodsA concurrency protocol that orders transactions such that older transactions get priority in the event of conflict.
Assigns global unique time stamp to each transaction to schedule concurrency trans. - 38Time stampMust have two properties:
Uniqueness assures that no equal time stamp values can exist.
Monotonicity assures that time stamp values always increase. - 39Concurrency Control with Optimistic Methods.Transaction is executed without restrictions until it is committed,
Transaction moves through 2or3 phases:
Read phase
Validation phase
Write phase: changes are permanently applied to database.