32 terms

DB L10: Database Integrity

What is a transaction?
-Set of actions that changes the contents of the database from one consistent state to anotehr
What is a consistent database state?
-State in which all data integrity constraints of the database are satisfied
-Ex) transaction of updating an inventory table
What is a Transaction?: Database Transaction 3 things?
1) Logical unit of work
2) Must be either entirely completed or aborted
3) No intermediate states are acceptable
What is a Transaction?: A mult-part transaction _____ ____ be partiall completed.
-MUST NOT be partially completed
-Ex) Transaction of selling a product to a customer consists of two parts 1) Update the inventory table 2) Update the AR table
-Updating only one table is not acceptable!
Transaction Properties (ACID): Atomicity
-The 'all or nothing' property
-All operations of a transaction must be completed; otherwise, the transaction is aborted
-A transaction is treated as a single, indivisible, logical unit of work
Transactions Properties (ACID): Consistency
-Must transform the database from consistent state to another consistent state
-It is the responsibility of both the DBMS and the application developers to ensure consistency
Transactions Properties (ACID): Isolation
-Transaction execute independently of one another
-The partial effects of incomplete transactions should not be visible to other transactions.
-Ex) Partial result of one transaction (T11) cannot be used by another transaction (T2) until T1 is completed
Transactions Properties (ACID): Durability
-The effects of a successfully completed (committed) transaction are permanently recorded in the database and must not be lost because of a subsequent failure
Properties of Transactions (ACID): Every transactions .....
-must exhibit these 4 properties
Transaction Support: Two possible outcomes?
1) Success - transaction commits and DB reaches a new consistent state
2) Failure - transaction aborts, and DB must be restored to the prior consistent state. Such a transaction is rolled backed or undone
-Committed transactions cannot be aborted
-Aborted transactions that are rolled back can be restarted later
Transaction Management with SQL: Provided by what 2 SQL statement?
1) Commit - All the changes are permanently recorded within the DB; Syntax: COMMIT;
2) Rollback - All the uncommitted changes are aborted and the database is rolled back to its previous consistent state. Committed transactions can not be rolled back. Syntax: ROLLBACK;
What is Concurrent Access?
-"When users process a database concurrently, special controls are needed to ensure that the actions of one user do not inappropriately influence the results for another."
-No concurrency control technique is perfect for all situations!!
What is a concurrent transaction?
-Refers to 2 or more transactions that appear to users as if they are being processed against a DB at the same time
-CPU can execute only 1 instruction at a time = Interleaved meaning that the operating system quickly switches CPU resources among tasks so that some portion of each of them is carried out in a given interval
What is concurrency control?
-Process of managing simultaneous operations on the DB without having them interfere with one another
-Prevents interference when two or more accessing DB simultaneously and at least one is updating data
-Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect reusult
What are the 3 Needs for Concurrency Control?
1) Lost Update Problem
2) Uncommitted dependency problem
3) Inconsistent analysis problem
What is the Lost Update Problem?
-Successfully completed update is overridden by another user
-T1 withdrawing 10 from and an account with balX intially 100
-T2 depositing 100 into same account
-Serially, final balance is 190
What is the Uncommitted Dependency Problem?
-Occurs when one transaction can see intermediate results of another transaction before they are committed; ie DIRTY READ
-T4 updates balX to 200 but aborts so it should fall back to 100
-T3 has read new value of balX and uses the value as basis of 10 reduction, giving the new balance 190, instead of 90
What is the Inconsistent Analysis Problem?
-Occurs when a transaction reads several values, but an alternative transaction updates some of them during the execution period; ie NON-REAPTABLE READ
-T6 is totalling balances of account X, Y, and Z
-T5 has transferred 10 from balX to balZ so T6 now has the wrong result (10 too much)
What is Resource Locking? And what is its goal?
-Used in an effort to prevent these issues which are caused by concurrent access
-Goal = serializable transactions
Lock Terminology: IL, EL & LG?
-Implicit locks - locks placed by the DMBS
-Explicit locks - locks issued by external applications
-Lock granularity - refers to the size of the locked resources (Rows, page, table, and DB level)
Lock Terminology: Two types of locks (EL and SL)
-Exclusive lock - prohibits other users from reading the locked resource
-Shared lock - allows other users to read the locked resource, but they cannot update it
What is a Serializable Transaction?
-Refers to 2 transactions that run concurrently and generate results that are consistent with the results that would have occurred if they had been executed separately
What is Two-phased locking?
-Transactions are allowed to obtain as many locks as necessary (growing phase)
-Once the first lock is released (shrinking phase) no additional locks can be obtained
Describe the special case of 2-phased locking?
-Locks are obtained throughout the transaction
-NO locks are released until the COMMIT or ROLLBACk command has been issued
-This is more restrictive, but easier to implement then the general 2-phased locking technique
Optimistic vs Pessimistic Locking: What is Optimistic? What is it preferred for?
-Assumes that no transaction conflict(s) will occur:
>DBMS processes transaction and checks whether a conflict occurred > if not the transaction is finished, if so, the transaction repeats until there is no conflict
-Preferred for the Internet and for many intranet applications
What is a Deadlock?
-An impasse that may result when 2 (or more) transactions are waiting for locks held by other to be released
Dealing with Deadlocks: They must be?
-Transparent to the user
Dealing with Deadlocks: Possible ways of Breaking a deadlock?
-Almost every DBMS has algorithms for detecting a deadlock
-When a deadlock occurs, DBMS uses a timeout to abort one of the transactions and rolls back any partially completed work
Dealing with Deadlock: Possible ways of Preventing a deadlock?
-Allow users to issue all lock requests at one time
-Use a lock manager > Time stamps
What is a Timestamp?
-Unique identifier created by DBMS that indicate relative starting time of a transaction
Time stamping: How is it ordered?
-Transactions ordered globally so that older transactions, those with smaller timestamps, get priority in the event of conflict
Time stamping: How is conflict resolved?
-Conflict is resolved by rolling back and restarting the associated transaction
-No locks so no deadlocks