select for update

  • Hello,

    I have a problem with selecting rows which I want to update.

    Select sets a read lock on the rows and then if I try to update selected rows I get a deadlock.

    In ORACLE there is a SELECT FOR UPDATE clause but it does not exist in the MSSQL. How it should be solved?

    In easy words how to select rows which then I want to update in the same transaction properly?

    Thank you in advance for help.

  • Please provide us with the code you use for your update.

    Based on what you are writing just now, I assume you use a correlated subquery?

    B

  • Hi,

    I think you will not be able to find the reason without knowing the tables structure.

    We've changed select to update already and added rowlock but it does not work. Deadlocks are on the first update

    However, I am attaching the problematic transaction:

    begin tran

    set @inseason = 0

    --check if we fit into existing range

    select

    @indateto = dateto

    ,@inseason = seasonid

    from

    tblSeasonDates

    where

    datefrom<@datefrom

    and

    dateto>@dateto

    and

    seasonid in (select seasonid from tblSeason where groupid=@groupid)

    if @inseason = 0

    begin

    --both ends are in different ranges

    print 'update tblSeasonDates 1'

    --shorten first

    update

    tblSeasonDates WITH (ROWLOCK)

    set

    DateTo=@DateFrom,

    user_modified = @user_modified

    where

    DateTo > @DateFrom

    and

    DateFrom < @DateFrom

    and

    SeasonID in (select seasonid from tblSeason where groupid=@groupid)

    print 'update tblSeasonDates 2'

    --shorten second

    update

    tblSeasonDates WITH (ROWLOCK)

    set

    Datefrom=@DateTo,

    user_modified = @user_modified

    where

    DateTo > @DateTo

    and

    DateFrom <= @DateTo

    and

    SeasonID in (select seasonid from tblSeason where groupid=@groupid)

    print 'delete tblSeasonDates 1'

    --delete overlapped

    delete

    from

    tblSeasonDates

    where

    DateFrom>=@DateFrom

    and

    DateTo<=@DateTo

    and

    SeasonID in (select seasonid from tblSeason where groupid=@groupid)

    print 'insert tblSeasonDates 1'

    --insert new

    Insert into tblSeasonDates

    (SeasonID, DateFrom , dateTo, user_modified)

    values

    (@SeasonID, @DateFrom , @DateTo, @user_modified)

    end

    else

    begin

    --we are inside range

    print 'update tblSeasonDates 3'

    --shorten first part

    update

    tblSeasonDates WITH (ROWLOCK)

    set

    DateTo=@DateFrom,

    user_modified = @user_modified

    where

    DateTo = @indateto

    and

    SeasonID in (select seasonid from tblSeason where groupid=@groupid)

    print 'insert tblSeasonDates 2'

    --insert second part

    Insert into tblSeasonDates

    (SeasonID, DateFrom , dateTo, user_modified)

    values

    (@inseason, @DateTo , @indateto, @user_modified)

    print 'insert tblSeasonDates 3'

    --insert new

    Insert into tblSeasonDates

    (SeasonID, DateFrom , dateTo, user_modified)

    values

    (@SeasonID, @DateFrom , @DateTo, @user_modified)

    end

    print 'delete tblSeasonDates 2'

    commit tran

  • Why don't you take the initial select statement out of the transaction all together? ... just keep the update statements in the transaction if you need it.

    HTH,

    B

  • so? ... did the suggestion work?

    B

  • Unfortunately no. We are still looking for a solution...

  • You do realize that this query will not produce consistent results?

    select

    @indateto = dateto

    ,@inseason = seasonid

    from

    tblSeasonDates

    where

    datefrom<@datefrom

    and

    dateto>@dateto

    and

    seasonid in (select seasonid from tblSeason where groupid=@groupid)

    If there is more than 1 row returned from this query, your variables will contain the values from the last row in the result set. Of course you have no order by so you don't know what the last row will be.

    I don't think you need a correlated subquery for this. I am pretty sure this query will give you the same results.

    select @indateto = dateto,

    @inseason = seasonid

    from tblSeasonDates sd

    join tblSeason s on s.seasonid = sd.seasonid

    where datefrom < @datefrom

    and dateto > @dateto

    and s.groupid = @groupid

    I would drop your ROWLOCK query hint. Query hints in SQL server can be very detrimental and should only be used when you are absolutely certain that the hint is the best choice. Lock escalation in sql server is very complex and unless you understand it really well it is best to avoid this.

    Also I used a free online sql formatter (http://poorsql.com) to format your code so it is easier to read.

    BEGIN TRANSACTION

    SET @inseason = 0

    --check if we fit into existing range

    SELECT @indateto = dateto

    ,@inseason = seasonid

    FROM tblSeasonDates

    WHERE datefrom < @datefrom

    AND dateto > @dateto

    AND seasonid IN (

    SELECT seasonid

    FROM tblSeason

    WHERE groupid = @groupid

    )

    IF @inseason = 0

    BEGIN

    --both ends are in different ranges

    PRINT 'update tblSeasonDates 1'

    --shorten first

    UPDATE tblSeasonDates

    WITH (ROWLOCK)

    SET DateTo = @DateFrom

    ,user_modified = @user_modified

    WHERE DateTo > @DateFrom

    AND DateFrom < @DateFrom

    AND SeasonID IN (

    SELECT seasonid

    FROM tblSeason

    WHERE groupid = @groupid

    )

    PRINT 'update tblSeasonDates 2'

    --shorten second

    UPDATE tblSeasonDates

    WITH (ROWLOCK)

    SET Datefrom = @DateTo

    ,user_modified = @user_modified

    WHERE DateTo > @DateTo

    AND DateFrom <= @DateTo

    AND SeasonID IN (

    SELECT seasonid

    FROM tblSeason

    WHERE groupid = @groupid

    )

    PRINT 'delete tblSeasonDates 1'

    --delete overlapped

    DELETE

    FROM tblSeasonDates

    WHERE DateFrom >= @DateFrom

    AND DateTo <= @DateTo

    AND SeasonID IN (

    SELECT seasonid

    FROM tblSeason

    WHERE groupid = @groupid

    )

    PRINT 'insert tblSeasonDates 1'

    --insert new

    INSERT INTO tblSeasonDates (

    SeasonID

    ,DateFrom

    ,dateTo

    ,user_modified

    )

    VALUES (

    @SeasonID

    ,@DateFrom

    ,@DateTo

    ,@user_modified

    )

    END

    ELSE

    BEGIN

    --we are inside range

    PRINT 'update tblSeasonDates 3'

    --shorten first part

    UPDATE tblSeasonDates

    WITH (ROWLOCK)

    SET DateTo = @DateFrom

    ,user_modified = @user_modified

    WHERE DateTo = @indateto

    AND SeasonID IN (

    SELECT seasonid

    FROM tblSeason

    WHERE groupid = @groupid

    )

    PRINT 'insert tblSeasonDates 2'

    --insert second part

    INSERT INTO tblSeasonDates (

    SeasonID

    ,DateFrom

    ,dateTo

    ,user_modified

    )

    VALUES (

    @inseason

    ,@DateTo

    ,@indateto

    ,@user_modified

    )

    PRINT 'insert tblSeasonDates 3'

    --insert new

    INSERT INTO tblSeasonDates (

    SeasonID

    ,DateFrom

    ,dateTo

    ,user_modified

    )

    VALUES (

    @SeasonID

    ,@DateFrom

    ,@DateTo

    ,@user_modified

    )

    END

    PRINT 'delete tblSeasonDates 2'

    COMMIT TRANSACTION

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply