-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: MySQL & Locking
PostPosted: Thu Nov 06, 2003 12:30 pm 
Newbie

Joined: Thu Nov 06, 2003 7:14 am
Posts: 2
Hi,

I'm trying to lock a row for update in a mysql db (v4.1 , type InnoDB):

Ond ond = (Ond)sess.load(Ond.class, ondVAF.getOndnr(),LockMode.UPGRADE);
...
sess.close();


this results in a sql query that should (?) lock this row:

17:15:23,150 INFO [STDOUT] Hibernate: select ondnr, version, bdrcd, afdcd, ondom, ondst from ond where ondnr =? for update

But the row is not locked, for i can select it for update with another webpage in my app, before the update in the first page is finished.

Does this type of locking work at all? If so, what am doing wrong?

TIA.


Top
 Profile  
 
 Post subject: check tx_isolation or transaction-isolation for InnoDB
PostPosted: Thu Nov 06, 2003 1:04 pm 
Regular
Regular

Joined: Sun Sep 21, 2003 11:43 pm
Posts: 85
Location: Massachusetts
I've been experimenting with this and had a similar problem. You need to set the global or local MySQL variable tx_isolation for those types of locks to actually work when you use the appropriate type of LockMode. See:

http://www.mysql.com/documentation/mysq ... tion_model

Specifically, read section 7.5.9.1. I've tried it manually but not done it with Hibernate yet. I think the hibernate variable in
hibernate.properties is called "hibernate.connection.isolation=whatever"
or "connection.isolation" as the name in the hibernate.cfg.xml file.

Regards,
David


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 07, 2003 6:01 am 
Newbie

Joined: Thu Nov 06, 2003 7:14 am
Posts: 2
Thank you, I actually got it working, but i'm not sure it is the best way.

I've set the global transaction level in mysql to read committed.

The code when a user selects something to update:

Code:
      net.sf.hibernate.Session sess = InitServlet.currentSession();
      sess.connection().setAutoCommit(false);
      Ond ond = (Ond)sess.load(Ond.class, ondVAF.getOndnr(),LockMode.UPGRADE);

(1 <-- )



I'm using Threadlocal for the session:

Code:
 

    public static final ThreadLocal session = new ThreadLocal();

    ...

    public static Session currentSession()
      throws HibernateException {

    Session s = (Session) session.get();
    if (s == null) {
       System.out.println("new session");
       s = sessionFactory.openSession();
       session.set(s);
    }
    return s;
  }


When the user submits the updated data:


Code:
       
        sess = InitServlet.currentSession();
        ...
        sess.saveOrUpdate(ondUpdate);
        sess.flush();
        ...
        InitServlet.closeSession()


The result is that the record is locked and that when a second user selects a record to update that a first user already is updating, the second user has to wait until either the first user commits his update or the lock timeout is reached, then a sqlexception is thrown.

Now, to achieve this , i have to keep the session open for as long as the user is updating the record. I have tried to use session.disconnect() at (1 <-- ) in the first code snippet and session.reconnect() later on, but that doesnt work; the record is no longer locked.
Is the the way i'm doing it now actually considered bad practice, is there a better way?


Top
 Profile  
 
 Post subject: Record locking issues
PostPosted: Fri Nov 07, 2003 1:40 pm 
Regular
Regular

Joined: Sun Sep 21, 2003 11:43 pm
Posts: 85
Location: Massachusetts
Your post is unclear about something: are you trying to ensure read access to the records while someone is holding it for writing? There are other MySQL transaction isolation levels (I believe InnoDB has 4 levels) which should allow for this, IF you are using session transactions.

To write-lock individual records between disconnected sessions, you're out of luck. If you keep your session open and in the client's HTTPSession, you eat up database connections and risk timeouts. Your best bet is probably to setup a "locking table" to keep track of which records are checked out by each person. That way, the record will be readable for everyone. When you want to update it, you can check your "locking table" to see if you 'checked' it out before writing. If you do this, be sure to add some sort of release mechanism: a manager who can see and remove locks, a function to see if the lock is XXX minutes/hours old and release it when someone else tries to save it, etc.

Regards,
David


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.