Am I just confused or what!!

I have a unique index on a field marked to take NULL values. I can
insert multiple records in this table with or without specifying NULL
in the VALUES "clause".

But with two connections and each using Read Committed i get a Lock
Conflict if the first transaction is posted but not committed.

Here is the database:

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

SET SQL DIALECT 3;

SET NAMES WIN1252;

CREATE DATABASE '/opt/yourchoice'
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);

I use IB Expert and did not test this with any other tool. If I
execute in one instance of the SQL tool (but do not commit) I cannot
execute on the other until I have committed on the first.

The following server messages are given

Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements.
lock conflict on no wait transaction.
violation of PRIMARY or UNIQUE KEY constraint "UNQ1_TEST_TABLE" on
table "TEST_TABLE".

Server is Firebird 1.5.2 running on a Windoze machine.

I can work around this, but I post since I suspect someone might be
interested - as I am of why the index should generate a lock when the
engine knows I'm trying to insert NULL.

This kind of makes unique constraints a stopper when you have lots of
users inserting into the same table.

Regards!

/Dany