Hi All,

According to The great Firebird Book, try/catch blocks
within a stored procedure act as "nested" transactions.

So I have a procedure:

CREATE OR ALTER PROCEDURE get_enum1_id( enum1 VARCHAR(128) )
RETURNS( enum1_row BIGINT )
AS
BEGIN
SELECT enum1_id FROM enum1 WHERE enum1_name = :enum1 INTO :enum1_row;
IF(:enum1_row IS NULL ) THEN
BEGIN
enum1_row = GEN_ID (enum1_generator, 1);
INSERT INTO enum1 VALUES(:enum1_row, :enum1);
WHEN SQLCODE -803 DO
BEGIN
SELECT enum1_id FROM enum1 WHERE enum1_name = :enum1 INTO :enum1_row;
END
WHEN GDSCODE deadlock DO
BEGIN
SELECT enum1_id FROM enum1 WHERE enum1_name = :enum1 INTO :enum1_row;
END
WHEN GDSCODE lock_conflict DO
BEGIN
SELECT enum1_id FROM enum1 WHERE enum1_name = :enum1 INTO :enum1_row;
END
END
SUSPEND;
END

These kinds of procedures are called by a lot of clients at once with,
possibly, the same data. These procedure calls conflict often.

This method of dealing with lock conflicts
seems to work better than throwing the exceptions into client-side
code and rolling-back and retrying transactions there.

Is this a good way to handle lock-conflicts? or am I missing something?

thanks,

stan