recently I have gone into problem, that one long-running query (sometimes even 5 mins) which does only select (with multiple subqueries) is blocking update statements. Those updates are trying to update one row in two of the tables used in problematic SELECT statement.
How can it be? I use Postgresql 9.0 with default read_commited transaction isolation and hibernate from jboss 5.1.
You will have to refer to documentation of the database to check if it is possible. I am aware that it can be done for SQL server by using nolock in the SQL statement.
Even if database allows this can result in data corruption as another transaction will be able to update the data which is being read, whereby invalidating the read operation. However this will not be the case for all scenarios. So you will have to look at your scenario & then take a call.
Joined: Mar 14, 2006
Doesn't found anything like this. But how about creating another datasource and using transacation-isolation to none or read uncommited? I would just use it for read only purposes, when up-to-date data is not needed. That should at least prevent SELECTs from waiting until updates ends?
In case you are using Spring or EJB, you could set transaction attribute on read methods & specify lower isolation level.
Joined: Mar 14, 2006
I am using Seam 2 with EJB 3.0. To be sure can you tell me how to achieve this in EJB? I learned all myself, but never I understood container managed transactions completely.
Assuming I got method with annotation @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW) and in method I create entity manager and fetch row from table A, update it and persist, does it mean table's A row is blocked only in whole method? I have complex session beans, that they call each other, with many methods with default transaction attribute.
If anyone knows any good "practical" book or site, I would appreciate it - I have 4-5 , but those are only theory or simple examples - none is taking more complex, like what happens when method with transaction is invoking method which creates new (ok, parent is suspended until child finishes, but how does look when we look at database transactions?).
does not support read-uncommited at all (so it can't be a solution for your problems),
does not use exclusive locks to read data (readers don't block writers, writers don't block readers).
What you're experiencing should not be happening at all. Are you sure that something else is not going on in your database? Especially, don't you lock something explicitly (eg. SELECT FOR UPDATE)? The documentation does not seem to indicate that locks would be needed even for complicated read-only queries. It would be against the concept of multiversioning, after all.