Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    43

    Unanswered: simulating deadlock in sybase 11

    Hi

    Can anyone let me know how to simulate deadlock in sybase 11. Please give me only practical example, do not give theoritical .

    ~Pankaj

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    It's quite simple:

    First create two tables, for example:
    Code:
    [77] DBA_SQL.testdb.1> create table lock_a (id int, c varchar(20));
    [78] DBA_SQL.testdb.1> create table lock_b(id int, c varchar(20));
    [79] DBA_SQL.testdb.1> insert lock_a values(1, 'aaa');
    (1 row affected)
    [80] DBA_SQL.testdb.1> insert lock_b values(2, 'bbb');
    (1 row affected)
    Now - open two connections to the server. In the first connection,
    start a transaction, and update the first table:
    Code:
    [81] DBA_SQL.testdb.1> begin tran;
    [82] DBA_SQL.testdb.1> update lock_a set c='ccc' where id=1;
    (1 row affected)
    In the second connection, start a transaction and update the second table:
    Code:
    [32] DBA_SQL.testdb.1> begin tran;
    [33] DBA_SQL.testdb.1> update lock_b set c='ccc' where id=2;
    (1 row affected)
    Now in the first connection you update the row in the second table that is being updated in the second connection, and in the second connection you update the row in the first table. You've now created a deadlock and one of the transactions will get aborted:
    Code:
    [83] DBA_SQL.testdb.1> update lock_b set c='ggg' where id=2;
    (1 row affected)
    [84] DBA_SQL.testdb.1>
    and
    Code:
    [34] DBA_SQL.testdb.1> update lock_a set c='ddd' where id=1;
    Msg 1205, Level 13, State 1
    Server 'DBA_SQL', Line 1
    Your server command (family id #0, process id #15) encountered a deadlock situation. Please re-run your command.
    [35] DBA_SQL.testdb.1>
    Michael
    [

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    43
    Sorry I forgot to metioned that I have already tables in place. Any way I hv simulated the deadlock.

    But thanks for ur help.

Posting Permissions

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