Advance Database -Lecture 3 - ataghinezhad. github. io In the tree protocol, the only lock instruction allowed is exclusive locks (lock-X) Each transaction 𝑻 𝒊 can lock a data item at most once, and must observe the following rules: 1 The first lock by 𝑇 𝑖 may be on any data item (the root of data items used) Subsequently, a data Q can be locked by 𝑻 𝒊 only
Databases: GATE CSE 2024 | Set 2 | Question: 17 With $2 \mathrm{PL}$, a transaction always locks the data item being read or written just before every operation and always releases the lock just after the operation; With $2 \mathrm{PL}$, once a lock is released on any data item inside a transaction, no more locks on any data item can be obtained inside that transaction
Database Concurrency Control: Exam Q A - studylib. net The database is organized as a forest of rooted trees Each transaction Ti must follow the following rules: • The first lock in each tree may be on any data item • The second, and all subsequent, locks in a treemay be requested only if the parent of the requested node is currently locked • Data items may be unlocked at any time
Transaction Management: Concurrency Control, part 2 A Simple Tree Locking Algorithm: (“lock” here is really a latch on tree structure) Search Start at root and descend: “crabbing down the tree” repeatedly, get S “lock” for child then “unlock” parent, end up with S “lock” on leaf page Get 2PL S lock on row, provide row pointer to caller
Locking Techniques - AAGASC It uses the intention lock modes to ensure serializability It requires that if a transaction attempts to lock a node, then that node must follow these protocols: o Transaction T1 should follow the lock-compatibility matrix o Transaction T1 firstly locks the root of the tree It can lock it in any mode
CS 44800: Introduction To Relational Database Systems Transaction T i can lock a node Q, using the following rules: 1 The lock compatibility matrix must be observed 2 The root of the tree must be locked first, and may be locked in any mode 3 A node Q can be locked by T i in S or IS mode only if the parent of Q is currently locked by T i in either IX or IS mode 4 A node Q can be locked by T
Multiple-Granularity Locks Transaction Management Index Locking One solution: Ignore the tree structure, just lock pages while traversing the tree, following 2PL This has terrible performance! – Root node (and many higher level nodes) become bottlenecks because every tree access begins at the root Database Management Systems, S Chakravarthy 10 Two Useful Observations Higher levels of the tree only