OK talking theoretically to start with... If you're getting deadlocks across tables (one thread locks A and waits for B, the other locks B and waits for A) then you need to try and change your code to modify different types of entity class in a consistent order (always A then B, or always B then A). If you're getting row-level deadlocks inside one table (fairly unusual) then a similar approach is needed - when modifying more than one entity instance, do it in order of primary key so you always rattle through the data in a predictable way and in the same direction.
I used to work with Ingres in the 1990s and due to it's painful locking strategy this was the kind of thing we had to do all the time to minimise deadlocks. Not something I've had to consider since, though. As far as generic locking behaviour goes I think you need to treat this as a two-part investigation. Locking in MySQL is one part - to understand when and how MySQL takes locks, what type they are, how and when they escalate. Then look at your Hibernate configuration and see what influences that strategy. Not that I confess to any detailed MySQL knowledge in this area. With Ingres the key was to try and stop lock escalation happening (where you start with row level locks and then try and lock the entire table when the number of row locks hits a threshold). Then you can look at your Hibernate configuration and see how it may influence the whole locking situation. Hibernate will try to batch modifications up to improve performance - but you can force-flush the updates programatically (which isn't a good idea unless you have a very specific reason to do so). Force flushing will also lengthen the window over which locks are held at all, thereby increasing the likelihood of deadlock occurring in the first place... BTW just seeing a locked table isn't an indication that you're going to get a deadlock, just an indication that a lock is being held. If you see the same table locked frequently, it's going to be a hot spot in your application. Remember that to get a deadlock you've got to have two ACTIVE processes competing for the same resource locks but in different orders. Food for thought, I hope. On Feb 28, 8:06 am, Jan Goyvaerts <[email protected]> wrote: > > Does anybody in here knows about documentation about Mysql locking behavior > for the Hibernate/Spring stack ? I'd like to iron out the table locks in > favor of row-level locks. Otherwise our users keep on stepping onto each > other's toes. :-) > -- You received this message because you are subscribed to the Google Groups "The Java Posse" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/javaposse?hl=en.
