4

In my project due to my business requirement a lot of tables are continuously updated.

Isolation level I have set Read Uncommitted in my MYSQL database

but sometimes I get this exception at different different place in the application.

org.springframework.dao.PessimisticLockingFailureException: could not execute batch; SQL 
[update test_item set test_name=null where permission_id=? and draft_id=?]; nested exception 
is org.hibernate.PessimisticLockException: could not execute batch
at com.test.service.impl.SheetService.reverseSyncTimebaseSheet (SheetService.java:8890)
at com.test.service.impl.SheetService.syncSheet (SheetService.java:8546)
at com.test.controller.SheetController.syncSheetDataBySheetId (SheetController.java:987)
at com.test.security.JwtAuthenticationFilter.doFilterInternal 
(JwtAuthenticationFilter.java:55)
Caused by: org.hibernate.PessimisticLockException: could not execute batch
at org.hibernate.dialect.MySQLDialect$3.convert (MySQLDialect.java:531)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert 
(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert (SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution 
(BatchingBatch.java:128)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch 
(BatchingBatch.java:104)
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute 
(AbstractBatchImpl.java:147)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch 
(JdbcCoordinatorImpl.java:212)
at org.hibernate.engine.spi.ActionQueue.executeActions (ActionQueue.java:633)
at org.hibernate.engine.spi.ActionQueue.executeActions (ActionQueue.java:478)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions 
(AbstractFlushingEventListener.java:356)

Although I haven't done any configuration for locking in my application

but still its failing for pessimistic lock which I don't want.

I don't need locking in my application at all , data integrity is fine for me at the moment.

Please let me know how can I get rid of locking ?

Harish Bagora
  • 686
  • 1
  • 9
  • 26
  • Do you use InnoDb or MyISAM engine ? – Fabien MIFSUD Nov 30 '20 at 12:20
  • @FabienMIFSUD InnoDB – Harish Bagora Nov 30 '20 at 12:23
  • 1
    did you have a look here : https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html which sql queries are you running ? in different transactions ? – Fabien MIFSUD Nov 30 '20 at 12:26
  • 1
    "I (think I) don't need locking at all" and "the database doesn't need locking at all" are two different things. The database will prevent data corruption, and even by allowing dirty reads, the database won't perform "corrupting writes". – Kayaman Nov 30 '20 at 12:30
  • 1
    I’m voting to close this question because it's based on a misunderstanding on how databases work, and assumes you can just "turn off" locking. – Kayaman Nov 30 '20 at 12:38
  • @Kayaman Basically you are saying that its not possible , we can't handle it with application , its database mechanism to prevent dirty writes. right ? – Harish Bagora Nov 30 '20 at 12:50
  • The database needs mechanisms to be able to provide ACID. By setting isolation to `READ COMMITTED` you're allowing dirty reads, which is weird enough (as most dbs [can't (or won't?) offer them](https://en.wikipedia.org/wiki/Multiversion_concurrency_control)). However the db won't go into a complete random mode, so it still needs to keep track of *some* order. This is where implicit locks and other mechanisms come into play. You can't put the DB into a full lockless mode. It would destroy your data, or at the very least you'd see "interesting" behaviour in your application. – Kayaman Nov 30 '20 at 13:02
  • What's the root cause of the exception (at the bottom of the stacktrace)? You're going to have to deal with locks. Whether you want it or not. See [here](https://stackoverflow.com/a/45309667/2541560) for the causes of an `org.hibernate.PessimisticLockException` and try to design properly to account for it. – Kayaman Nov 30 '20 at 13:13

0 Answers0