June 20, 2014 at 12:40 pm
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
June 20, 2014 at 12:57 pm
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".
June 20, 2014 at 1:13 pm
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
June 20, 2014 at 1:26 pm
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
June 20, 2014 at 1:31 pm
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".
June 23, 2014 at 12:05 pm
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
June 23, 2014 at 12:20 pm
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