delayed commit causes doubled result ?

  • Hi,

    has anyone seen where a delete has not committed and while waiting on the commit the user

    creates new data, and then a SQL returns the new and the old data

    since the commit has not deleted the data ?

    The simple data and process model is like this:

    State One

    ------------

    Table Parent

    PKey ParentA

    Table Child

    PKey Child1 FKey ParentA 5.00

    PKey Child2 FKey ParentA 5.00

    State Two

    ------------

    Table Parent

    PKey ParentA

    Table Child

    PKEY FKEY TimeStamp Data State

    Child1 ParentA 5.00 delete transaction in process waiting on a lock

    Child2 ParentA 5.00 delete transaction in process waiting on a lock

    Table Child

    PKEY FKEY TimeStamp Data State

    Child3 ParentA 5.00 insert commited

    Child4 ParentA 5.00 i nsert commited

    Output result rows to to a text file then to a mail merge

    Result Data 20.00

    Expected Result Data 10.00

    SQL in a Java application

    The only way to reproduce this is to reinsert the deleted data from history

    The TimeStamp on the new Data and the deleted Data is exactly the same

    This is an intermittent problem

    Note: Can not use a max ID to fix this in the select since multiple rows can be validly inserted

    for the new data

    Sorry if this is not up t par for a post its a complex problem

    trying to simplify it involves online Java system that is not even regular Java

    it is Forte converted to Java

  • Does the read query use "WITH (NOLOCK)"?

    Is snapshot isolation in use?

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher (6/20/2014)

    Is snapshot isolation in use?

    The fact that his delete had been waiting to commit tells you that it's not a snapshot isolation level in use.

    Igor Micev,My blog: www.igormicev.com

  • It does depend how your delete query is doing.

    If it makes a table scan for the rows to delete then it can cause a table-level lock.

    If it uses an index than it uses an index seek and initiates page-level locks.

    Since an update operation initiates an Exclusive locks which can be page or table level, no other operations are allowed to commit until it commits.

    In your case, it's possible that your delete query makes a slow scan over the table and in meanwhile an insert starts.

    Try to make your delete statement use an index so it performs an index seek.

    Igor Micev,My blog: www.igormicev.com

  • Igor Micev (6/20/2014)


    ScottPletcher (6/20/2014)

    Is snapshot isolation in use?

    The fact that his delete had been waiting to commit tells you that it's not a snapshot isolation level in use.

    No, it does not.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you Igor

    I do see where the delete stored procedure does an update and then a delete

    so if the user deletes before they create a new entry

    the update-delete should commit before the insert (correct?)

    The scenario may be the user creates a new entry and then deletes the old entry

    The time stamps in history on the 2 child records are exactly the same ...

    so it is difficult to tell which is actually first ...

    A index on the delete is a good idea... I will test that !

    Thanks! john

  • Igor,

    We had TRIGGER to history issues way back and we so no need to update in the Delete stored procedure as the history trigger exists and we are not having problems with them the way we did circa 2003 ish .. removing the update from the delete and testing

    Thank You Again, John

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

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