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

>They all use READ COMMITTED isolation.
>It is an application that runs in JBoss App server so I have single datasource defined that uses READ COMMITTED.
>Blocked statements are insert and update. Selects are not blocked.

Why are inserts blocked? That should not happen unless you

a)      don’t use generators to fill values into your primary keys or

b)      have a unique constraint or similar (like triggers limiting the number of child records per master record)


I can understand why UPDATEs (including the UPDATE OR INSERT statement) result in lock conflicts and that can be unavoidable and something you have to try to minimize rather than eliminate. INSERTs, on the other hand, should only cause lock conflicts if your business logic determines it should be so.


Maybe you could show us how these lock conflicts occur (what does a typical insert statement that causes a lock conflict look like and are there any constraints/unique requirements to any of the fields?)