Lock table

N-te Jr. · **Posted:** Thu Apr 26, 2007 7:33 am

Hi,

I have a database where a lot of people insert data at the same time. For this data, the primary keys are generated with triggers in the database. So I have to lock the whole table when I want to insert an object. How can I do this? Especially I don't only have to lock only one table but at least 3 or 4 at the same time, because there are FK relations between the data.

Hibernate version: 3.2 beta 8

Name and version of the database you are using: Oracle 10g

batmat · **Posted:** Fri Apr 27, 2007 1:36 am

I don't even know if Oracle is able to lock an entire table. Isn't able to lock at the row level?

Maybe see the Session.lock() method and what the values are for the LockMode parameter.

bumbalo · **Joined:** Fri Apr 20, 2007 3:46 pm **Posts:** 15

If you must lock the whole table in oracle you can do with this command

LOCK TABLE TABLE_NAME IN EXCLUSIVE MODE

You can issue this command in hibernate just through the regular sql.

bumbalo · **Joined:** Fri Apr 20, 2007 3:46 pm **Posts:** 15

But I forgot to say that locking the whole table and locking other tables is very bad regarding the performances, especially if you have quite a few threads.

I don't know your business use case but you should try to avoid the strategy of locking tables.

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

You don't need to lock tables if your using Hibernate... regardless of DB or FKs, I'd of thought. If your using a native/sequence PK then Hibernate and your DB will take care of inserting new records in paralle for you.
Right?
Also wasn't there already a thread on this?

N-te Jr. · **Posted:** Sat Apr 28, 2007 2:38 pm

Yes, you're right, that hibernate takes care that my primary keys are generated unique but I think that there will be some situations where I'll have to lock a whole table. Especially when I have to make sure that nobody writes data during some select statements.
I know that locking a table isn't good for the performance but in the moment the most important thing is that it works. The speed up comes later. ^^
Thank you all for your help!!!

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

I'd say that if you are having to lock an entire table then there is a more fundimental architecture issue that you need to look at. If you leave it until you are having performace issues it could be too late and you could find yourself faced with a real headache.