Hi all,
We're on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
We have a nightly job that performs a bulk update in a table. This process does a spatial calculation which is very (very) slow in 10g if it is done serially. At the time that this bulk update was developed, there were no (easy) solutions to use native parallel processing in Oracle. The current implementation is that several parallel sql*plus sessions are started with the following sql.
update <table_name>
set geometry = <sdo_calculation>
where mod(id,16) = <session_number>
and geometry is null
The field ID is the primary key. In this example, 16 sessions would be started and each session would have a unique session number between 0 and 15.
This sometimes results in a deadlock... but I do not understand why. The mod(id,<total_sessions>) = <session_number> would ensure that no session updates a record of another session.
What am I missing here?
Many thanks,
Rop
Disclaimer: I understand that this not the optimal way of doing things . A redesign is not possible at the moment. We're migration to 12c soon, where the problem of the slow spatial calculation should be solved.