CODEWITHSUNDEEP
mysqldatabasehibernatelocking
Gendaful
Gendaful

Reputation: 5802

How to implement row level locking using Hibernate Query Language?

Background: I am working on a MYSql database where multiple instances reads from the same Account Table . I want to implement row level exclusive lock using Hibernate Query Language to avoid dirty read.

My code is as below.

List<AccountTableBean> currentRequestslist = sessionFactory.getCurrentSession()
                .createQuery("from Account where status = :finalStatus")
                .setLockMode("MappCurrentRequests", LockMode.PESSIMISTIC_READ)
                .setParameter("finalStatus","Active").list();

Currently, I have used Pessimistic_read on the entire table. Is there a way i can apply to lock on individual rows? Any example will be very helpful.

Thank you, Gendaful

Upvotes: 1

Views: 3702

Answers (1)

mindas
mindas

Reputation: 26713

The locking type is implied by the database itself.

If you are using MyISAM storage engine, there is nothing you can do to force row level locking, no matter how hard you try - either in raw SQL or in JDBC. MyISAM simply does not support row level locks.

Simply switch to using InnoDB and you won't even have to call setLockMode in order to get the necessary lock type.

From the manual:

MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications. MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, allowing only one session to update those tables at a time, making them more suitable for read-only, read-mostly, or single-user applications.

Upvotes: 2

Related Questions