Lockmode PESSIMISTIC_WRITE in criteria vs select for update

Question

Is there any significant difference in performance in using PESSIMISTIC_WRITE as LockMode in hibernate criteria as an alternative for Select for update query.

Use case involves following points:

Restrictions on values in multiple columns like status, time
Sorting on multiple columns
Select only the first row for processing.

This processing is done simultaneously by multiple machines/threads. I am currently using hibernate criteria, and that is causing timeouts in getting lock multiple times, which results in choking of all other queries.

Are you sure that Hibernate actually implements `PESSIMISTIC_WRITE` for MySQL/MariaDB? — Rick James, Apr 30 '17 at 04:40
Yes, there is for MySQL. ALthough I am using InnoDB engine instead of MariaDB — Amit Pareek, Apr 30 '17 at 07:32
What SQL is generated for `PESSIMISTIC_WRITE`? What little I could find says `SELECT ... FOR UPDATE` (in InnoDB). When traffic is low and queries are fast, you are unlikely to hit the question. — Rick James, Apr 30 '17 at 15:02

score 0 · Answer 1 · answered Apr 30 '17 at 19:16

In this case, the FOR UPDATE is "required" for proper concurrency:

BEGIN;
SELECT x ... FROM t ... FOR UPDATE;
use `x` for whatever
UPDATE t ...;
COMMIT;

Without FOR UPDATE, the value of x could change, thereby potentially invalidating the intent of the UPDATE.

Lockmode PESSIMISTIC_WRITE in criteria vs select for update

1 Answers1