|Subject||RE: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to|
|Author||Svein Erling Tysvær|
>This is the definition of the table that locks on insert:
>CREATE TABLE CASH_CASHBOX_DAY_AMMOUNT
> CASHBOXID INTEGER NOT NULL,
> DATE_TIME NUMERIC( 18, 0) NOT NULL,
> AMMOUNT NUMERIC( 18, 0),
> CONSTRAINT PK_CASH_CASHBOX_DAY_AMMOUNT PRIMARY KEY (CASHBOXID, DATE_TIME)
>ALTER TABLE CASH_CASHBOX_DAY_AMMOUNT ADD CONSTRAINT FK_CASH_CASHBOX_DAY_AMMOUNT
> FOREIGN KEY (CASHBOXID) REFERENCES CASH_CASHBOX (ID);
>It is simple insert that just inserts single record. There is no triggers or generators. Primary key value is know before insert is executed.
I see two likely ways an insert can fail here:
1) The CASHBOXID doesn’t exist or has been inserted in another transaction that is not yet committed
(possible solution: insert into CASH_CASHBOX and CASH_CASHBOX_DAY_AMMOUNT in the same transaction)
2) A record with the same CASHBOXID and DATE_TIME already exists
Can be avoided by using a new field populated through a generator as the primary key – but you would then probably have to also implement a ‘duplicate resolution’-button or similar in your program to fix up those cases where there are several records with the same CASHBOXID and DATE_TIME
Either of these ought to give you an error message (which would be good to post on this list).
Your problem could of course be related to something completely different (like Mark suggests – or something stupid like the user having no insert rights to the table), but if it is the inserts themselves that are the problem, I see no other possible reason for a lock conflict.