I have been doing a few exercises for an exam, they dont come with answers so just looking clarification, it asks to find the errors in the table and to explain each error which 2PL principles it violates.


I have came up with
T4 at Step 14 cannot work as T1 has not released the lock it holds.

T4 at step 15 and 16 as T1 as it doesnt have the lock.

T4 at step 5 and 6 as T2 hasn't released the lock.

Is this correct


I think I understand what you are trying to get at. The T1...T4 elements are threads (label axis clearly please), and 1...28 are the sequence of steps that occur. With that clarified, we can move forward.

One observation - T2 unlocks D3 at step 21 after writing it at step 7, and then commits finally at step 22. This would not occur in real life unless you were rolling back the transaction, so get rid of step 21.

S1 - t1.d1.lock()
S2 - t1.d1.read()
s3 - t2.d3.lock()
s4 - t2.d3.read()
s5 - t4.d3.lock()
s6 - t4.d3.read() At this point t4 has read consistency established on d3. Any changes by t2 or others will not be visible until it relinquishes the lock.
s7 - t2.d3.write() T2 now has a write (exclusive) lock on d3
s8 - t3.d2.lock()
s9 - t3.d2.read()
s10 - t3.d2.unlock() No other locks exist on d2 at this point, so d2 is available
s11 - t1.d2.lock()
s12 - t1.d2.read()
s13 - t2.d2.unlock() See s10.
S14 - t4.d1.read()
S15 - t4.d4.read()
S16 - t4.d4.write() This should establish a lock on d4, implicitly.
S17 - t1.d1.write()
S18 - t2.D4.lock() This will queue t2 on D4 until t4 commits or rolls back. As a result T2's steps 19-22 will also wait until T4 commits. Likewise T3's steps 24-27 will queue. T2 and T3 are now gated on T4's actions and will wait until T4 is done, or the system times them out and rolls them back automatically.

Anyway, this analysis assumes that locks are advisory but do establish read consistency, and writes without an explicit lock establish an implicit lock. As you can see, there are a lot of assumptions here. However, if you take the purist approach that if you have an explicit lock on a data item, then other threads will automatically queue on them. If so, then S14 will wait until T1 commits.

So, my final point here is, first clarify your terms, and the semantics of each operational type - a state machine representation would be good practice to identify what transitions are valid. A good text on the subject is:

Database Transaction Models for Advanced Applications, edited by Ahmed Elmagarmid, published by Morgan Kaufmann. My copy was published around 1990.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.