We are in the middle of testing JBoss user transactions under sql server 2005. We have two applications one is inserting the db records under jboss user transactions. Other application is polling continously for the new records being inserted. We observed that if the first application is trying to insert the records, second application has to wait until the first application commits the user transaction.
Has anyone else noticed this issue and if there is any fix or work around?
That sounds like standard ACID transaction behaviour: the insertion of the record requires the acquisition of a write lock which is exclusive, so no readers can get in there until the lock is released, which happens when the transaction commits.