Is delete from table where rownum <= x necessarily in a guaranteed order? I would think not, as select * from table where rownum <= x is not, unless there is an explicit order by.Ī roadblock I'm facing is that I don't see anything else anywhere in the code that has even a chance of causing a deadlock. I still haven't come up with a definitive argument/reason for myself as to why, hence my attempt to construct a test case. But I was wondering if the inserts that are happening in separate transactions at the same time, and filling in gaps in the blocks, would get in there and cause the order to be different depending on the exact sequence. Both jobs are going to read the table in the same sequence. ![]() I'm in a situation where it would best to identify and prove exactly what the issue is. I have not yet included the triggers in my simulation attempt.Īt the moment I'm not looking at how to prevent the problem (or in a position to do so just yet). So presumably the deadlock could be caused not by the two deletes conflicting with each other, but by one of the deletes conflicting with the poupulation of the table. It should also be noted that this table in questions, which has rows constantly added to it, is populated via a trigger. I'm trying to prove this by simulating a test case, but am failing to reproduce it.Ĭan someone show me how to simulate a deadlock by deleting from the same table with two sessions?Īttached is my (apparently poor) attempt. I'm thinking these two deletes must be interfering with each other, and indeed one of them shows up in the trace file for the deadlock. Also, it is a live table with records added to it one at a time by countless other sessions at the same time that deletes can fire. ![]() This table in question has no indexes, no constraints, no updates. Both of these jobs are scheduled to run at the top of every hour, and both delete with the same exact statement (including same rownum limit). There are two jobs that both delete records from the same table, for example: delete from x where rownum <= 10000. I'm troubleshooting a deadlock situation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |