dmarmur2002 wrote:

> Am I just confused or what!!

If you're confused, I'm totally baffled. For continuity, here's the
problem - code follows. The table has a primary key with a generator /
trigger combination that assigns a value if none is supplied. It also
has a field that allows nulls but has a unique constraint. Two separate
transactions, both read-committed no wait, attempt to insert a record
into the table, the first succeeds .. and the second... well, it seems
to be version dependent.

I ran your tests on 1.5.2 and saw your results. Even though both
transactions were declared as no wait read committed, the transaction
that did the second insert waited for the first transaction to commit
then reported "lock conflict on no wait transaction. violation of
PRIMARY or UNIQUE KEY constraint ..."

So I ran the test on 2.0 alpha 3. Both transactions succeed, as they
should.

So I ran the test on 1.5.3 RC1. The second transaction got the lock
conflict instantaneously.

So, three versions, three behaviors.

I also tried a concurrency, wait transaction in 1.5.2. The second
transaction waited for the first, which committed, then the second
succeeded.

Regards,


Ann

/*******************************************************************/
/**** Generated by IBExpert 2005.08.08 2005-09-28 21:32:33 ****/
/*******************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1252;

CREATE DATABASE 'xyzzy.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET WIN1252;

CREATE GENERATOR GEN_NEW_TABLE_ID;

CREATE TABLE TEST_TABLE (
ID INTEGER NOT NULL,
UNIQUE_FIELD CHAR(10)
);

ALTER TABLE TEST_TABLE ADD CONSTRAINT UNQ1_TEST_TABLE UNIQUE
(UNIQUE_FIELD);
ALTER TABLE TEST_TABLE ADD CONSTRAINT PK_TEST_TABLE PRIMARY KEY (ID);

SET TERM ^ ;
/* Trigger: NEW_TABLE_BI */
CREATE TRIGGER NEW_TABLE_BI FOR TEST_TABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_NEW_TABLE_ID,1);
END
^

SET TERM ; ^

/*******************************************************************/
/**** End ****/
/*******************************************************************/

Here is the query:

INSERT INTO TEST_TABLE (UNIQUE_FIELD)
VALUES (NULL);


run the query from two different isql sessions...