+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Serializable isolation level make deadlock

  1. Serializable isolation level make deadlock

    Dear all,

    I am facing a problem using serialzable isolation level in a stored
    procedure, when 2 sessions call the sp by other stored procedure loop 10000
    times. Deadlock occur and 1 session will be killed as deadlock detect.
    Siimplify version as below

    Stored Procedure 1 (sp1):
    set isolation level serialzable
    begin trans
    update table t1 where ... and (select sum(c1) from t1 > 100000);
    insert into t2 ...;
    end

    Stored Procedure 2 (sp2)
    loop 10000 times to exec sp1

    I want a session if undergo the update statement in sp1, no session can
    start select sum(c1) from t1 until the update statement complete.

    Is it I just ignore the "set isolation level" statement can achieve what I
    want?

    Thanks!

    Regards,
    Edwin

    Is it


  2. Re: Serializable isolation level make deadlock

    >> I am facing a problem using serializable isolation level in a stored procedure, when 2 sessions call the sp by other <<

    The nature of the serializable isolation level is that it locks out
    everyone else. This is why we don't like to use it very often in
    production code, as you leaned.

    >> .. stored procedure loop 10000 times. Deadlock occur and 1 session will be killed as deadlock detect. <<


    SQL code should be declarative and not have loops in it. My guess is
    that you need to re-write the Stored Procedure. The simplified
    version is not much help. There is no such syntax as UPDATE TABLE in
    SQL and only dialects allow an alias in the UPDATE clause; (SELECT SUM
    ( c1) FROM T1 > 100000) is also invalid. An UPDATE followed by an
    insertion is also a symptom of a procedural mindset. We need to see a
    MUCH better simplified version or the real code with DDL.

    >> Is it I just ignore the "set isolation level" statement can achieve what I want? <<


    You never told us what you want to achieve. How can we know?

  3. Re: Serializable isolation level make deadlock

    On Nov 22, 9:16*am, Edwin Chan
    wrote:
    > Dear all,
    >
    > I am facing a problem using serialzable isolation level in a stored
    > procedure, when 2 sessions call the sp by other stored procedure loop 10000
    > times. Deadlock occur and 1 session will be killed as deadlock detect.
    > Siimplify version as below
    >
    > Stored Procedure 1 (sp1):
    > set isolation level serialzable
    > begin trans
    > update table t1 where ... and (select sum(c1) from t1 > 100000);
    > insert into t2 ...;
    > end
    >
    > Stored Procedure 2 (sp2)
    > loop 10000 times *to exec sp1
    >
    > I want a session if undergo the update statement in sp1, no session can
    > start select sum(c1) from t1 until the update statement complete.
    >
    > Is it I just ignore the "set isolation level" statement can achieve what I
    > want?
    >
    > Thanks!
    >
    > Regards,
    > Edwin
    >
    > Is it


    I would use sp_getapplock to serialize requests. Alternatively, you
    could use snapshot isolation so that readers and writers do not block
    each other.

  4. Re: Serializable isolation level make deadlock

    On Nov 22, 11:39*am, --CELKO-- wrote:
    > >> I am facing a problem using serializable isolation level in a stored procedure, when 2 sessions call the sp by other <<

    >
    > The nature of the serializable isolation level is that it locks out
    > everyone else. *


    Not everyone - many selects can still proceed.

  5. Re: Serializable isolation level make deadlock

    >> I would use sp_getapplock to serialize requests. Alternatively, you could use snapshot isolation so that readers and writers do not block each other. <<

    I don't think that locks are the real problem here. I would guess
    that the looping is going to kill performance, even if he can avoid
    locking everything. This looks like he is mimicking a tape file
    system in SQL; the loop and the UPDATE and INSERT are symptoms that
    this procedure can be replaced with a single MERGE statement. The
    single statement would be atomic so fancy locking by the programmer
    would not be required. It would also run orders of magnitude
    faster.

    But who knows until we get more specs, real code and some DDL?


  6. Re: Serializable isolation level make deadlock

    Thanks all. I put the code in following.

    TABLE:

    CREATE TABLE Comb1 (
    Count int identity (1, 1) NOT NULL,
    MS int NOT NULL,
    Invest double precision NOT NULL,
    Odds double precision NOT NULL,
    WinPay double precision NOT NULL,
    PositionLimit double precision NOT NULL,
    constraint PK_COMB1 primary key nonclustered (Count)
    );

    CREATE TABLE Trans (
    ID int identity (1, 1) NOT NULL,
    MS int NOT NULL,
    Invest double precision NOT NULL,
    Odds double precision NOT NULL,
    constraint PK_TRANS primary key nonclustered (ID)
    );

    Stored Procedure:

    CREATE PROCEDURE dbo.bet_func
    (@ms INT, @invest FLOAT, @odds FLOAT, @Result BIT OUTPUT)
    AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    UPDATE dbo.Comb1 SET
    Invest = Invest + @invest,
    Odds = @odds,
    WinPay = WinPay + (@invest * @odds)
    WHERE MS = @ms
    AND ((((select SUM(Invest) FROM dbo.Comb1) + @invest) - ((@invest *
    @odds) + (SELECT WinPay FROM dbo.Comb1 WHERE MS=@ms))) > -100000000000 );
    IF ( @@rowcount = 1)
    BEGIN
    INSERT INTO Trans (MS, Invest, Odds) VALUES
    (@ms, @invest, @odds);
    SET @Result = 1;
    END
    ELSE
    SET @Result = 0;
    COMMIT TRANSACTION;

    CREATE PROCEDURE dbo.call_bet_func
    (@ms INT, @invest FLOAT, @odds FLOAT, @betCount INT, @Result BIT OUTPUT,
    @Timediff INT OUTPUT)
    AS
    DECLARE @betCounter INT;
    DECLARE @startTime DATETIME;
    DECLARE @endTime DATETIME;
    SET @betCounter = 1;
    SET @startTime = (select CURRENT_TIMESTAMP);
    WHILE (@betCounter <= @betCount)
    BEGIN
    EXEC dbo.bet_func @ms, @invest, @odds, @Result OUT;
    SET @betCounter = @betCounter + 1;
    END
    SET @endTime = (select CURRENT_TIMESTAMP);
    SET @Timediff = datediff (millisecond, @startTime, @endTime);

    Initial data load:

    INSERT INTO dbo.Comb1 (MS,Invest,Odds,WinPay,PositionLimit) VALUES
    (10,0,0,0,0);
    INSERT INTO dbo.Comb1 (MS,Invest,Odds,WinPay,PositionLimit) VALUES
    (12,0,0,0,0);
    INSERT INTO dbo.Comb1 (MS,Invest,Odds,WinPay,PositionLimit) VALUES
    (11,0,0,0,0);

    Execute:

    DECLARE @Result BIT, @timeDiff INT
    EXEC dbo.call_bet_func 12,1,0,10000, @Result OUT, @timeDiff OUT
    PRINT 'Result = ' + RTRIM(CAST(@Result AS varchar(1)))
    PRINT 'Time Diff = ' + RTRIM(CAST(@timeDiff AS varchar(10)))

    I want to execute the stored procedure dbo.call_bet_func which loop for
    execute dbo.bet_func from 2 or more sessions. But there is deadlock detect if
    2 session running at the same time. I turn on trace and find deadlock on
    table Comb1.

    I have no idea how to solve problem and my aim is maximize no. of records
    can be handled in a second by multi sessions.

    Thanks!

    Regards,
    Edwin


    "--CELKO--" wrote:

    > >> I would use sp_getapplock to serialize requests. Alternatively, you could use snapshot isolation so that readers and writers do not block each other. <<

    >
    > I don't think that locks are the real problem here. I would guess
    > that the looping is going to kill performance, even if he can avoid
    > locking everything. This looks like he is mimicking a tape file
    > system in SQL; the loop and the UPDATE and INSERT are symptoms that
    > this procedure can be replaced with a single MERGE statement. The
    > single statement would be atomic so fancy locking by the programmer
    > would not be required. It would also run orders of magnitude
    > faster.
    >
    > But who knows until we get more specs, real code and some DDL?
    >
    >


  7. Re: Serializable isolation level make deadlock

    COUNT is a reserved word and it is too vague to be a data element
    name. TRANS(ACTION) is a reserved word cannot be a table name, etc.
    IDENTITY is a non-relational kludge and can never be either a key or
    an attribute. It is the physical insertion attempt count and has
    nothing to do with the data model. But it lets you pretend that this
    is a sequential file system.

    Why did you use DOUBLE PRECISION? It is expensive (unless you have a
    floating point processor chip in your hardware), gives rounding errors
    and it is over-kill for anything but scientific work. Then you add
    the overhead of casting DOUBLE PRECISION and FLOAT on top of all of
    that. Make all of this into DECIMAL(s,p) types.

    Why did you use BIT flags, as if this was assembly language and not
    SQL? In good software engineering, we name a function with the
    template4 _ but you put _func in the name to tellus
    a function is function! Please read any book on Software Engineering
    to learn the basics. Why not Place_Bet() to tell us what this
    function does?

    Win_pay is a computed column so why are you storing it? We had to do
    that with punch cards 50 years ago, but not in SQL today.

    Do not use PRINT in production code if you can avoid it. There is
    only one place to print the messages, so it has to be locked to one
    and only one session.

    You have written a bad file layout in SQL and not RDBMS at all. Is
    that vague ms the real key or not?

    This is such a mess that you cannot get the help you need on a
    newsgroup. We will need to get the actual specs and the data model (if
    any), then throw out this file system so we can build an RDBMS.



  8. Re: Serializable isolation level make deadlock

    Thanks for your reply but I don't see why there is a deadlock from your
    reply. Also, the odds value will be changed from time to time. If the winpay
    value not being stored, the accurate winpay value will be lost.

    Moreover, create the tables, insert the data, create the stored procedures
    and execute the stored procedure from 2 or more sessions at the same time.
    The deadlock can be detected.

    "--CELKO--" wrote:

    > COUNT is a reserved word and it is too vague to be a data element
    > name. TRANS(ACTION) is a reserved word cannot be a table name, etc.
    > IDENTITY is a non-relational kludge and can never be either a key or
    > an attribute. It is the physical insertion attempt count and has
    > nothing to do with the data model. But it lets you pretend that this
    > is a sequential file system.
    >
    > Why did you use DOUBLE PRECISION? It is expensive (unless you have a
    > floating point processor chip in your hardware), gives rounding errors
    > and it is over-kill for anything but scientific work. Then you add
    > the overhead of casting DOUBLE PRECISION and FLOAT on top of all of
    > that. Make all of this into DECIMAL(s,p) types.
    >
    > Why did you use BIT flags, as if this was assembly language and not
    > SQL? In good software engineering, we name a function with the
    > template4 “_” but you put “_func” in the name to tell us
    > a function is function! Please read any book on Software Engineering
    > to learn the basics. Why not “Place_Bet()” to tell us what this
    > function does?
    >
    > Win_pay is a computed column so why are you storing it? We had to do
    > that with punch cards 50 years ago, but not in SQL today.
    >
    > Do not use PRINT in production code if you can avoid it. There is
    > only one place to print the messages, so it has to be locked to one
    > and only one session.
    >
    > You have written a bad file layout in SQL and not RDBMS at all. Is
    > that vague “ms” the real key or not?
    >
    > This is such a mess that you cannot get the help you need on a
    > newsgroup. We will need to get the actual specs and the data model (if
    > any), then throw out this file system so we can build an RDBMS.
    >
    >
    >

  9. Re: Serializable isolation level make deadlock

    On Nov 24, 8:30*pm, Edwin Chan
    wrote:
    > Thanks all. I put the code in following.
    >
    > TABLE:
    >
    > CREATE TABLE Comb1 (
    > * Count int identity (1, 1) NOT NULL,
    > * MS int NOT NULL,
    > * Invest double precision NOT NULL,
    > * Odds double precision NOT NULL,
    > * WinPay double precision NOT NULL,
    > * PositionLimit double precision NOT NULL,
    > * constraint PK_COMB1 primary key nonclustered (Count)
    > );
    >
    > CREATE TABLE Trans (
    > * ID int identity (1, 1) NOT NULL,
    > * MS int NOT NULL,
    > * Invest double precision NOT NULL,
    > * Odds double precision NOT NULL,
    > * constraint PK_TRANS primary key nonclustered (ID)
    > );
    >
    > Stored Procedure:
    >
    > CREATE PROCEDURE dbo.bet_func
    > * (@ms INT, @invest FLOAT, @odds FLOAT, @Result BIT OUTPUT)
    > AS
    > * SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    > * BEGIN TRANSACTION;
    > * * UPDATE dbo.Comb1 SET
    > * * Invest = Invest + @invest,
    > * * Odds = @odds,
    > * * WinPay = WinPay + (@invest * @odds)
    > * * WHERE MS = @ms
    > * * AND ((((select SUM(Invest) FROM dbo.Comb1) + @invest) - ((@invest*
    > @odds) + (SELECT WinPay FROM dbo.Comb1 WHERE MS=@ms))) > -100000000000 );
    > * * IF ( @@rowcount = 1)
    > * * * BEGIN
    > * * * * INSERT INTO Trans (MS, Invest, Odds) VALUES
    > * * * * (@ms, @invest, @odds);
    > * * * * SET @Result = 1;
    > * * * END
    > * * ELSE
    > * * * SET @Result = 0;
    > * COMMIT TRANSACTION;
    >
    > CREATE PROCEDURE dbo.call_bet_func
    > * (@ms INT, @invest FLOAT, @odds FLOAT, @betCount INT, @Result BIT OUTPUT,
    > @Timediff INT OUTPUT)
    > AS
    > * DECLARE @betCounter INT;
    > * DECLARE @startTime DATETIME;
    > * DECLARE @endTime DATETIME;
    > * SET @betCounter = 1;
    > * SET @startTime = (select CURRENT_TIMESTAMP);
    > * WHILE (@betCounter <= @betCount)
    > * * BEGIN
    > * * * EXEC *dbo.bet_func @ms, @invest, @odds, @Result OUT;
    > * * * SET @betCounter = @betCounter + *1;
    > * * END
    > * SET @endTime = (select CURRENT_TIMESTAMP);
    > * SET @Timediff = datediff (millisecond, @startTime, @endTime);
    >
    > Initial data load:
    >
    > INSERT INTO dbo.Comb1 (MS,Invest,Odds,WinPay,PositionLimit) VALUES
    > (10,0,0,0,0);
    > INSERT INTO dbo.Comb1 (MS,Invest,Odds,WinPay,PositionLimit) VALUES
    > (12,0,0,0,0);
    > INSERT INTO dbo.Comb1 (MS,Invest,Odds,WinPay,PositionLimit) VALUES
    > (11,0,0,0,0);
    >
    > Execute:
    >
    > DECLARE @Result BIT, @timeDiff INT
    > EXEC *dbo.call_bet_func 12,1,0,10000, @Result OUT, @timeDiff OUT
    > PRINT 'Result = ' + RTRIM(CAST(@Result AS varchar(1)))
    > PRINT 'Time Diff = ' + RTRIM(CAST(@timeDiff AS varchar(10)))
    >
    > I want to execute the stored procedure dbo.call_bet_func which loop for
    > execute dbo.bet_func from 2 or more sessions. But there is deadlock detect if
    > 2 session running at the same time. I turn on trace and find deadlock on
    > table Comb1.
    >
    > I have no idea how to solve problem and my aim is maximize no. of records
    > can be handled in a second by multi sessions.
    >
    > Thanks!
    >
    > Regards,
    > Edwin
    >
    > "--CELKO--" wrote:
    > > >> I would use sp_getapplock to serialize requests. Alternatively, you could use snapshot isolation so that readers and writers do not block each other. <<

    >
    > > I don't think that locks are the real problem here. *I would guess
    > > that the looping is going to kill performance, even if he can avoid
    > > locking everything. *This looks like *he is mimicking a tape file
    > > system in SQL; the loop and the UPDATE and INSERT are symptoms that
    > > this procedure can be replaced with a single MERGE statement. *The
    > > single statement would be atomic so fancy locking by the programmer
    > > would not be required. *It would also run orders of magnitude
    > > faster.

    >
    > > But who knows until we get more specs, real code and some DDL?


    The following code:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    UPDATE dbo.Comb1 SET

    is very prone to deadlocks. You can switch to an approach where you
    only insert, do not update, and select some kind of totals.

  10. Re: Serializable isolation level make deadlock

    >> Also, the odds value will be changed from time to time. If the winpay value not being stored, the accurate winpay value will be lost. <<

    If there is a temporal element to this date, then it has to be stored
    as values in a column of a row in a table. This is called the
    Information Principle and it is part of Dr. Codd's 12 rules.

    >> Moreover, create the tables, insert the data, create the stored procedures and execute the stored procedure from 2 or more sessions at the same time. The deadlock can be detected. <<


    Why would you do all that for every session? The tables that
    implement your data model should appear only once in the schema. That
    is basic RDBMS. Likewise, a procedure will be created only once and
    invoked many times by many users.

    This sounds like you are trying to fake an entire transaction system
    in SQL.