2PL and deadlocks

Hi
Regarding the Two-Phase Locking protocol used in transactions, it is said in some websites that this protocol guarantees serializability. I tried to find examples and more on how it guarantees serialisability but could not find. Alll I found is the rules which are:
1.Two transactions cannot have conflicting locks
2.No UNLOCK operation can precede a LOCK operation in the same transaction.
3.No data are affected until all locks are obtained—that is, until transaction is in its locked point

I need to understand how this protocol guarantees serializability and how deadlock can happen in 2 PL.
if you have examples please post them
Thanks
Jean
jean11Asked:
Who is Participating?
 
BigSchmuhConnect With a Mentor Commented:
Deadlock can always happen except if your define a LOCKING policy that is well implemented in all programs targeting the db.
Ex: Always starts locking by table A, then B, then C... would avoid every deadlock for sure

2PL just add some complexity to deadlock scenario but if prog 1 locks A then B and prog 2 locks B then A, you'll have a simple deadlock scenario :
-prog 1 will lock some A rows required by prog 2
-prog 2 will lock some B rows required by prog 1
-one will be elected as a deadlock survival while the other will be stopped ( 2PL should handle this  extension of deadlock supervision and reaction )

Serializability is just a consequence of the locking capability in your platform whether it's 2PL or direct. If the locking mechanism works, Serialization works...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.