1 Reply Latest reply on Dec 17, 2005 1:10 AM by gmccreath

Read Only Entity Beans? Nasty SELECT FOR UPDATE.

gmccreath Dec 8, 2005 6:08 PM

All,

At the DB, the EJB3 layer causes a SELECT FOR UPDATE on bunches of related lookup tables that do not need to be locked during a transaction on a (say) Transaction table. Throughput goes out the window when these lock are on these tables.

Is there a way to get EJB3 to NOT use SELECT FOR UPDATE? I would have thought setting the Entity Bean to read only may have helped, but I cannot find an EJB anotation that does that.

We'll look at restructuring things for using optimitistic locking and version in the medium term, but right now, I just need to stop those SELECT FOR UPDATE htting the DB.

Using JBoss 4.0.1 with JBoss Cache. In the process of going to 4.0.3 now. All caches have an isolation level of READ_COMMITTED. The default of REPEATABLE_READ causes deadlocks.

Any thoughts?

Many thanks,
Greg.

1. Re: Read Only Entity Beans? Nasty SELECT FOR UPDATE.

gmccreath Dec 17, 2005 1:10 AM (in response to gmccreath)

Found it, sort of. The SELECT FOR UPDATE was being caused by he DB (Postgres). It uses a SELECT for UPDATE on Referential Integrity (RI) links. The has the side effect of an insert on (say) a transaction table to lock all related tables. The side effect of that is a SEVERE drop in throughput (only one at a time).

Solution? Upgrade to Postgres 8.1. It handles locking for RI differeht by introducing a UPDATE FOR SHARE which uses a shared lock rather than an exclusive one.

As for deadlocking - still it is REPEATABLE_READ that is the culprit. I'd recommend that the default is change away from this to READ_COMMITTED. Much safer.
Actions