You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by nicholas walton <nw...@gmail.com> on 2017/03/07 20:41:04 UTC

Triggers, locks and transactions

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

Re: Triggers, locks and transactions

Posted by Rick Hillegas <ri...@gmail.com>.
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\u2019t 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)
> \u2026\u2026\u2026
>
> 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\u2019m 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