Re: Catalogue of SQL and PL/SQL bad practices: Call for participation

> As Oracle by default issues row level locks and never escalates a lock
> your issue is a non-issue, and the reasons you provide are myths.
>

Hi Sybrand,

I'm not talking about lock escalation. The deadlock problem that I was referring to is an issue, and I've experienced it first-hand. On online gaming systems, with frequent transactions against accounts, locking on individual rows may pose a problem. Bonus processing, for example, may take 10-15 minutes to pay a few hundred thousand players their bonuses. During that time, they cannot transact until the bonus processing commits because of the locks on that particular account record. Even worse, the batch process may be slowed down because of other transactions going on and waiting for locks to be released. Sometimes these jobs have to run during trading hours because they depend on 3rd party system interfaces (risk control or external notifications) that are only available during work hours. In this case, committing after every record is processed actually helps.

Using bulk fetch and forall would definitely shorten the time for that processing (because the data fetch would be done before any locking) but I'm still not sure that even the update process alone would be short enough to run during trading hours without interrupting the business.

is there a better way to solve the problem?

--
gojko adzic
http://gojko.net