Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: deadlock a table for select

    Hi Experts,

    I am doing a test for deadlocking tables on MSSQL, oracle, sybase, db2, teradata and informix databases and for this I need to deadlock a table for select and therefore on issueing a simple select statement I want the database to return the deadlock error

    I was able to find out that this can be done on db2 with command "db2 +c update" and then doing a select on the table from another prompt.

    Could you please let me know how I can replicate the same sceario on MSSQL, oracle, sybase, teradata and informix databases

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What you are describing is not a deadlock it's a simple lock. Those are two completely different things.

    You will not be able to block a SELECT statement in Oracle.
    I don't know about the others.

  3. #3
    Join Date
    Jun 2009
    Posts
    4
    It does not matter how I create the deadlock but finally when I issue a select statement I want the database to return deadlock error

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    For a deadlock two transactions need to wait on each other. In your example only the second transaction is waiting for the first, so you'll never get a deadlock. Only a "simple" lock.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    place lock by:

    begin tran
    update... delete or insert

    and do not commit, lock will not be released until rolled back or committed.

  6. #6
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    here his a way to generate a deadlock on SQL server:


    -- 1) Create Objects for Deadlock Example
    USE TEMPDB

    CREATE TABLE dbo.foo (col1 INT)
    INSERT dbo.foo SELECT 1

    CREATE TABLE dbo.bar (col1 INT)
    INSERT dbo.bar SELECT 1

    -- 2) Run in first connection
    BEGIN TRAN
    UPDATE tempdb.dbo.foo SET col1 = 1

    -- 3) Run in second connection
    BEGIN TRAN
    UPDATE tempdb.dbo.bar SET col1 = 1
    UPDATE tempdb.dbo.foo SET col1 = 1

    -- 4) Run in first connection
    UPDATE tempdb.dbo.bar SET col1 = 1

    you should get this message :

    Server: Msg 1205, Level 13, State 50, Line 1
    Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

  7. #7
    Join Date
    Jun 2009
    Posts
    4
    I want to do the same for select statement and not update.
    It is easy to deadlock a table for update but I want a deadlock error message on issuing select statement

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mathew - please could you explain your goal here? You want the engine to return a deadlock error on issuing specifically a select statement - why? What do you want to use that information for?

    Just so I understand - you do not expect your select statement to participate in the deadlock, correct? You want your select statement to be
    issued after the deadlock occurs, and to be blocked by it, returning an error?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Mathew07, I don't want to get do picky about this but it appears you are using the wrong terminology (as shammat pointed out).

    A Deadlock happens with 2 transactions and 2 tables and involves a change (Insert, Update or Delete). As an example:

    Thread 1 has a Share lock on Table 1 and wants an Exclusive lock on Table 2.
    Thread 2 has a Share lock on Table 2 and want a Share lock on Table 1.

    These transactions are deadlocked and can not continue because Thread 1 is waiting for Thread 2 to release its lock while Thread 2 is waiting for Thread 1 to release its lock. Eventually one of them will time out and get out of the others way.

    What you want is a simple Lock timeout. As an example:

    Thread 1 has an Exclusive lock on Table 1 (this involves an Insert, Update or Delete).
    Thread 2 wants a Share lock on Table 1. It can't get it because Thread 1 already has an Exclusive lock.

    The Reason this terminology is important is the answer you want depends on the situation. Setting up a Deadlock (something like what hillcat did) is completely different from setting up a simple Lock Timeout (something like what PMASchmed did).

  10. #10
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    Sample code for deadlock on select

    Session1
    Code:
    use tempdb
    select * into pd1 from master..spt_values where type='P'
    create index ix1 on pd1 (number) 
    begin tran
    update pd1 set type='P' where number=0
    Session2:
    Code:
    use tempdb
    begin transaction
    select * from pd1 (holdlock) where number=2047
    Session1:
    Code:
    update pd1 set type='P' where number=2047
    Session 1 wait for lock to be released

    Session2:
    Code:
    select * from pd1 (holdlock) where number=0
    Session 2 fail with
    Msg 1205, Level 13, State 18, Line 1
    Transaction (Process ID 367) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Session1: cleanup
    Code:
    rollback
    drop table pd1
    Last edited by pdreyer; 06-04-09 at 09:39.

  11. #11
    Join Date
    Jun 2009
    Posts
    4
    Thanks a lot . That worked.

    Any idea how to repro this in oracle

  12. #12
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use whatever oracle has for REPEATABLE READ Isolation level

Posting Permissions

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