May 9, 2023 at 3:08 pm
Hey everyone!
I have a SQL agent job that deletes data then inserts data into the same table hourly. Both the delete and insert command are wrapped in the same Begin/Commit/Rollback Transaction statement and the process takes about 30 seconds to complete. I have another job that on occasion reads data during this update and returns no data. I'm able to reproduce this issue by running the job manually and then trying to select data from that table which produces an empty record set.
If read committed is the default isolation level why is no data being returned? Is it possible the isolation level isn't actually read committed? Any thoughts are appreciated!
May 9, 2023 at 5:09 pm
Are you saying that the DELETE in the hourly process deletes ALL of the data in the table or just some of the data?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2023 at 5:35 pm
Hi Jeff,
Thanks for the reply.
It only deletes some of the data. Also, the query that isn't returning data only pulls from that subset that gets deleted.
May 9, 2023 at 5:55 pm
Hi Jeff,
Thanks for the reply.
It only deletes some of the data. Also, the query that isn't returning data only pulls from that subset that gets deleted.
Those rows are locked for deletion and haven't been committed, yet. I'm pretty sure that returning none of the rows affected in the delete/insert will be available to anything that uses "Read Committed".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2023 at 9:54 pm
Does the query contain WITH (NOLOCK)?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 10, 2023 at 5:07 pm
Do you have RCSI enabled on that database? Or is the query using snapshot isolation?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2023 at 6:18 pm
Steve,
No there are no query hints being used.
May 11, 2023 at 6:21 pm
Jeffrey,
RCIS is set to 0. There are no additional query hints or anything being used within the two queries.
May 11, 2023 at 8:45 pm
If your query is attempting to query the data that is being deleted - that query will be blocked until the delete has completed and of course it will have no rows returned...because...they were deleted.
It only deletes some of the data. Also, the query that isn't returning data only pulls from that subset that gets deleted.
That is the default behavior for read committed. If you are expecting something different to happen, then maybe you were thinking of read uncommitted which could then return the rows that haven't yet been deleted.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2023 at 9:08 pm
Jeffrey,
RCIS is set to 0. There are no additional query hints or anything being used within the two queries.
Transactions are designed to maintain consistency across multiple transactions(and allow the ability to rollback), within a single transaction as soon as the delete successfully completes you should no longer see those records regardless of isolation level within the scope of that transaction.
May 12, 2023 at 1:18 am
Hey Jeffrey thanks for chiming in.
Let me clarify what’s happening. There is a delete query and an insert query wrapped in a Begin Commit/Rollback Tran. Some of the data is deleted and some left in the table The data being queried from the other job is specifically the data that’s being deleted and then reinserted, so upon completion of the commit it should return data. What’s happening is there are inconsistent results on occasion (either no data or not the same amount of rows) being returned. I mentioned read committed because it’s the default setting and since there are no query hints and they’re just basic delete/insert/select statements that isolation level should be applied. It’s an interesting scenario and I’ll try and provide more detail when I can.
May 12, 2023 at 4:19 am
Hey Jeffrey thanks for chiming in.
Let me clarify what’s happening. There is a delete query and an insert query wrapped in a Begin Commit/Rollback Tran. Some of the data is deleted and some left in the table The data being queried from the other job is specifically the data that’s being deleted and then reinserted, so upon completion of the commit it should return data. What’s happening is there are inconsistent results on occasion (either no data or not the same amount of rows) being returned. I mentioned read committed because it’s the default setting and since there are no query hints and they’re just basic delete/insert/select statements that isolation level should be applied. It’s an interesting scenario and I’ll try and provide more detail when I can.
Hmmm.... in your original post, you clearly stated "I have another job that on occasion reads data during this update and returns no data. "
It sounds like you've changed the problem a bit. Now you're saying "upon completion of the commit it should return data". At that point, if it's not, then perhaps one of two things is happening... the commit hasn't happened like you think it has or the delete/insert code is actually bad and not adding the right stuff.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply