Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Deadlocks in two phase locking protocol ?

    Hello Everyone !!!!!
    This is my first post in this forum.
    I am following Advanced Database Management Systems as a subject in a Professional Graduate Diploma.
    I know that deadlocks occur in two phase locking protocol.
    But I want to demonstrate it using an example.
    I am grateful to anyone who could show me how deadlocks occur in two transactions (T1 & T2) which adhere to 2PL.
    Thanks in advance !!!!!!

  2. #2
    Join Date
    Mar 2013
    Posts
    6
    please help !!!!

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Open up two query windows.
    In the first, begin a transaction the updates a record, but do not commit the transaction.
    In the second, issue a statement to update the same record.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by blindman View Post
    In the first, begin a transaction the updates a record, but do not commit the transaction.
    In the second, issue a statement to update the same record.
    I don't think that would be a deadlock -- just a simple lock wait.

    This would produce a deadlock:

    - Open the same two sessions, disable autocommit in both.
    - In session 1 update a record in table A.
    - In session 2 update a record in table B.
    - In session 1 attempt to update the same record in table B. This will cause a lock wait.
    - In session 2 attempt to update the same record in table A. This will be a deadlock; each session is now waiting on another to complete, neither can proceed.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Mar 2013
    Posts
    6
    Quote Originally Posted by neyo537 View Post
    Hello Everyone !!!!!
    This is my first post in this forum.
    I am following Advanced Database Management Systems as a subject in a Professional Graduate Diploma.
    I know that deadlocks occur in two phase locking protocol.
    But I want to demonstrate it using an example.
    I am grateful to anyone who could show me how deadlocks occur in two transactions (T1 & T2) which adhere to 2PL.
    Thanks in advance !!!!!!
    Thank you

  6. #6
    Join Date
    Mar 2013
    Posts
    6
    Quote Originally Posted by n_i View Post
    I don't think that would be a deadlock -- just a simple lock wait.

    This would produce a deadlock:

    - Open the same two sessions, disable autocommit in both.
    - In session 1 update a record in table A.
    - In session 2 update a record in table B.
    - In session 1 attempt to update the same record in table B. This will cause a lock wait.
    - In session 2 attempt to update the same record in table A. This will be a deadlock; each session is now waiting on another to complete, neither can proceed.
    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •