Re: How to avoid waiting for locks during delete

"Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote

> Bill probably has a large storage array

EMC.
> and is probably using partitioned tables.

Yep. Trust the likes of you to smell the config a mile away Jim. ;-)

But then that's not to say that Andre cannot use partitioning and partition exchanging to address his problem on a smaller system.

> Have you done an explain plan and a tkprof or run statspack?
> Are things analyzed? These are basics.

I'm not sure that is the problem. What bugs me is Andre mentioning long running transactions (he mentioned days).

Andre, what happens if at the last momemt, at 99% completion, the transaction fails? Due to things like rollback space problems, power failures, operating system crashing... etc.

If any such event occurs, you have lost hours and hours of processing time. Worse, all this will take hours and hours to undo. In my little pond, I cannot afford something like that.

The concept of one user wanting to delete a row while another user has that same row locked in an update... that does not ring the right tones (except when dealing with quantum mechanics and Feynmann diagrams ;-). Why bother with the update if the row is to be deleted? This points IMO to the problem I mentioned at the start - a basic design issue. Be that at database side, in the application, or in the business's workflow.

And that problem cannot be fixed with a DELETE NOWAIT.

--
Billy