db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From nicholas walton <nwalton250...@gmail.com>
Subject Triggers, locks and transactions
Date Tue, 07 Mar 2017 20:41:04 GMT
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



Mime
View raw message