>> "insert into TABLE_B (ID, TABLE_A_ID) values (1, 5);"
>> Why does the second transaction wait?
>
>I might be thinking too simple, but for me this looks obvious.
>
>If the first transaction translates to "Update Table_A set ID = 5 where ID = 5",
>it does not make too much sense, but should not affect the "child" table. In this case,
>I suppose that Firebird server is not prepared for nonsense commands but expects
>all updates to be real updates (changing indeed the ID to another value).
>
>But any attempted change of the ID to another value *must* stop the second
>transaction, because otherwise it would be next to impossible to keep database
>consistency. The foreign key relation would be corrupted!
>
>So, I guess what you are asking is: why is the parser not intelligent enough to ignore
>my attempt to update the ID with the same value?
>
>If I had a vote on this, I would like to keep it as it is.
>Otherwise the parser would have to check this on every update which surely is some
>overhead; to be paid by each and every update transaction.

Moreover, what should be done if your foreign key was 'cascade', you had one row with id = 4 and one with id = 5 and you issued

"Update Table_A set ID = ID+1 where ID in (4, 5)"

Should the newly inserted record get TABLE_A_ID 5 or 6 or should it vary depending upon whether transaction A or B committed first?

Set