File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes How SELECT can produce deadlock? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How SELECT can produce deadlock?" Watch "How SELECT can produce deadlock?" New topic
Author

How SELECT can produce deadlock?

Michal Glowacki
Ranch Hand

Joined: Mar 14, 2006
Posts: 114
Hi,

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.

Regards,
Michal
Rishi Shehrawat
Ranch Hand

Joined: Aug 11, 2010
Posts: 218

It is possible if Postgresql takes a shared lock while reading which will block the update statement trying to take a exclusive lock.
Michal Glowacki
Ranch Hand

Joined: Mar 14, 2006
Posts: 114
Is there any way to ommit this? And any possible threats of doing it?
Rishi Shehrawat
Ranch Hand

Joined: Aug 11, 2010
Posts: 218

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.
Michal Glowacki
Ranch Hand

Joined: Mar 14, 2006
Posts: 114
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?
Rishi Shehrawat
Ranch Hand

Joined: Aug 11, 2010
Posts: 218

In case you are using Spring or EJB, you could set transaction attribute on read methods & specify lower isolation level.
Michal Glowacki
Ranch Hand

Joined: Mar 14, 2006
Posts: 114
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?).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

I don't know PostgreSQL, but have you seen this?

Apparently PostgreSQL uses multiversioning, therefore it:
  • 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.
     
    jQuery in Action, 2nd edition
     
    subject: How SELECT can produce deadlock?