concurrency control

Question 1

concurrency control

Answer 1

the process of managing simultaneous operations on the database without having them interfere with one another

Question 2

concurrency control prevents

Answer 2

interference when two or more users are accessing database simultaneously and at least one is updating data

Question 3

although two transactions may be correct in themselves,

Answer 3

interleaving of operations may produce an incorrect result

Question 4

three examples of potential problems caused by concurrency

Answer 4

lost updates, uncommitted data, inconsistent retrievals

Question 5

lost update problem

Answer 5

occurs when a successfully completed update is overwritten by another transaction

Question 6

uncommitted data problem

Answer 6

occurs when one transaction accesses the intermediate results of another transaction before they are committed, and the second transaction is then rolled back

Question 7

lost update example

Answer 7

T1 withdraws $10 from 100
T2 deposits 100 into same account
incorrectly overlapping transactions may lead to final balance of $90, with T2’s update being lost

Question 8

inconsistent retrievals problem

Answer 8

occurs when a transaction reads several values, but a different transaction updates some of them in the midst of this process

Question 9

inconsistent retrievals example

Answer 9

T6 is totaling 3 account balances. T5 is also transferring $10 from acc x to acc z after x is totaled but before z is, so T6 has a result that is $10 too high

Question 10

serializable schedule

Answer 10

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 executed in serial order

Question 11

because waiting for one transaction to finish before starting any others may be inefficient, the built-in scheduler ensures

Answer 11

efficient use of the DBMS and CPU by interleaving operations when possible. if transactions access unrelated data, there is no conflict among their operations

Question 12

methods for scheduling conflicting operations in concurrent transactions

Answer 12

locking methods, timestamping

Question 13

lock granularity

Answer 13

refers to the size of the locked resource

Question 14

scale for size of locked resource

Answer 14

database-level -> table-level -> page-level -> row-level

Question 15

database-level means ______, while row-level means ______

Answer 15

slow data access, significant overhead

Question 16

an exclusive lock

Answer 16

prohibits other users from reading the locked resource

Question 17

a shared lock

Answer 17

allows other users to read the locked resource, but they cannot update it

Question 18

optimistic locking assumes that

Answer 18

no transaction conflicts will occur

Question 19

with optimistic locking, DBMS processes

Answer 19

a transaction to a temporary file and checks whether conflict occurred. if not, transaction finished. if so, the transaction is repeated until there is not conflict.

Question 20

optimistic locking is acceptable for applications with

Answer 20

few update operations

Question 21

pessimistic locking assumes that

Answer 21

conflicts will occur

Question 22

with pessimistic locking, locks are issued

Answer 22

before a transaction is processed, and then the locks are released

Question 23

two-phase locking (2PL)

Answer 23

guarantees serializability

Question 24

T/F: 2PL is one of the most common techniques used to achieve serializability

Answer 24

true

Question 25

growing phase 2PL

Answer 25

transactions are allowed to obtain as many locks as necessary

Question 26

shrinking phase 2PL

Answer 26

once the first lock is released, no additional locks can be obtained

Question 27

two-phase locking doesn't prevent

Answer 27

deadlocks

Question 28

deadlock

Answer 28

an impasse that may result when two (or more) transactions are waiting for locks held by the other to be released

Question 29

3 basic techniques for controlling deadlocks

Answer 29

deadlock prevention, deadlock detection, deadlock avoidance

Question 30

deadlock prevention

Answer 30

abort a transaction if possibility of deadlock, reschedule transaction for later execution

Question 31

deadlock detection

Answer 31

DBMS periodically tests database for deadlocks. if found, one transaction ('victim') is rolled back

Question 32

deadlock avoidance

Answer 32

transactions obtain all needed locks before execution

Question 33

timestamp

Answer 33

a unique identifier created by DBMS that indicates the relative starting time of a transaction

Question 34

transactions are ordered globally so that

Answer 34

older transactions with smaller timestamps get priority in the event of conflict

Question 35

timestamp conflict is resolved by

Answer 35

rolling back and restarting the associated transaction - no locks, so no deadlocks

Question 36

timestamping demands

Answer 36

a lot of system resources (both memory and processing overhead)