Subject | "lock conflict on no wait transaction" exception |
---|---|
Author | Ed Dressel |
Post date | 2010-04-14T19:50:51Z |
FB 1.5, Dialect 3
I am getting a "lock conflict on no wait transaction" on a table that I only insert data into. The table is a log file for changes to a different table (ClientInfo)--the inserts happen from ClientInfo's BeforeUpdate trigger.
Here is the SQL from the BeforeUpdate that of the ClientInfo table
****
...
if (New.clienttype_id <> Old.CLientType_ID) then
insert into ClientTypeChangeLog(Client_ID, Old_ClientType_ID,
New_ClientType_ID)
values (New.Client_ID, Old.ClientType_ID, New.ClientType_ID);
if (New.RefNo <> Old.refno) then
insert into ClientInfoChangeLog(Client_ID, ChangeType,
ChangeDetails)
values (New.Client_ID, 1, Old.RefNo);
if (New.first_name <> Old.First_Name) then
insert into ClientInfoChangeLog(Client_ID, ChangeType,
ChangeDetails)
values (New.Client_ID, 2, Old.First_Name);
if (New.Last_name <> Old.Last_Name) then
insert into ClientInfoChangeLog(Client_ID, ChangeType,
ChangeDetails)
values (New.Client_ID, 3, Old.Last_Name);
if ((not New.ExpireDate is null) and (not Old.ExpireDate is null))
then
begin
Diff = New.ExpireDate - Old.ExpireDate;
if ((Diff < -1) or (Diff > 1)) then
begin
Insert into ClientExpireDateChangeLog(Client_ID,
Prior_ExpireDate, New_ExpireDate)
values (New.Client_ID, Old.ExpireDate, New.ExpireDate);
end
end
end
*****
The ClientTypeChangeLog table is defined as
*****
CREATE TABLE CLIENTTYPECHANGELOG (
CLIENTTYPECHANGE_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
EVENT_DATETIME DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
CLIENT_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
OLD_CLIENTTYPE_ID DM_INTEGER /* DM_INTEGER = INTEGER */,
NEW_CLIENTTYPE_ID DM_INTEGER /* DM_INTEGER = INTEGER */
);
CREATE OR ALTER TRIGGER CLIENTTYPECHANGELOG_BI0 FOR CLIENTTYPECHANGELOG
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
New.clienttypechange_id = Gen_ID(gen_misc, 1);
New.Event_DateTime = Current_Timestamp;
end
*****
Any ideas on what to look at?
Thanks
Ed Dressel
I am getting a "lock conflict on no wait transaction" on a table that I only insert data into. The table is a log file for changes to a different table (ClientInfo)--the inserts happen from ClientInfo's BeforeUpdate trigger.
Here is the SQL from the BeforeUpdate that of the ClientInfo table
****
...
if (New.clienttype_id <> Old.CLientType_ID) then
insert into ClientTypeChangeLog(Client_ID, Old_ClientType_ID,
New_ClientType_ID)
values (New.Client_ID, Old.ClientType_ID, New.ClientType_ID);
if (New.RefNo <> Old.refno) then
insert into ClientInfoChangeLog(Client_ID, ChangeType,
ChangeDetails)
values (New.Client_ID, 1, Old.RefNo);
if (New.first_name <> Old.First_Name) then
insert into ClientInfoChangeLog(Client_ID, ChangeType,
ChangeDetails)
values (New.Client_ID, 2, Old.First_Name);
if (New.Last_name <> Old.Last_Name) then
insert into ClientInfoChangeLog(Client_ID, ChangeType,
ChangeDetails)
values (New.Client_ID, 3, Old.Last_Name);
if ((not New.ExpireDate is null) and (not Old.ExpireDate is null))
then
begin
Diff = New.ExpireDate - Old.ExpireDate;
if ((Diff < -1) or (Diff > 1)) then
begin
Insert into ClientExpireDateChangeLog(Client_ID,
Prior_ExpireDate, New_ExpireDate)
values (New.Client_ID, Old.ExpireDate, New.ExpireDate);
end
end
end
*****
The ClientTypeChangeLog table is defined as
*****
CREATE TABLE CLIENTTYPECHANGELOG (
CLIENTTYPECHANGE_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
EVENT_DATETIME DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
CLIENT_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
OLD_CLIENTTYPE_ID DM_INTEGER /* DM_INTEGER = INTEGER */,
NEW_CLIENTTYPE_ID DM_INTEGER /* DM_INTEGER = INTEGER */
);
CREATE OR ALTER TRIGGER CLIENTTYPECHANGELOG_BI0 FOR CLIENTTYPECHANGELOG
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
New.clienttypechange_id = Gen_ID(gen_misc, 1);
New.Event_DateTime = Current_Timestamp;
end
*****
Any ideas on what to look at?
Thanks
Ed Dressel