On 3/7/17, 12:41 PM, nicholas walton wrote:
Hi,
I have a a data model built around triggers. It accepts data in via
only one table RAW_DATA upon which I have one BEFORE trigger
CREATE TRIGGER lock_transaction
NO CASCADE BEFORE INSERT ON raw_data
REFERENCING NEW AS NEW
LOCK raw_data in EXCLUSIVE MODE;
My intent being to lock out the entire transaction until all other
triggers have fired and completed so I can’t have lockouts. However,
under multiple transactions I am getting lockouts
ERROR 40001: A lock could not be obtained due to a deadlock, cycle of
locks and waiters is:
Lock : ROW, MVAVG_DELTA_DOC, (1,10302)
Waiting XID : {2929274, S} , NWALTON, INSERT INTO
"NWALTON"."SENSOR_AVG_DOCUMENT_TAGGING" (docid, rowid, tag)
………
I can understand why it might lock accessing
SENSOR_AVG_DOCUMENT_TAGGING but since I have an exclusive lock on the
only point at which data enters RAW_DATA two transactions should not
be accessing the table.
Any suggestions as to what I’m doing wrong.
TIA
Nick
Hi Nick,
Have you tried the lock-debugging techniques described at
https://wiki.apache.org/db-derby/LockDebugging and
http://db.apache.org/derby/docs/10.13/adminguide/cadminlockvti42553.html
and http://db.apache.org/derby/docs/10.13/ref/rrefproper98166.html
Hope this helps,
-Rick