All my classes have an
我所有的课都有
@Version
@Version
annotation, so I assumed they were using optimistic locking.
注释，我假设它们使用的是乐观锁。
But I the following exception in my logs that seem to indicate Im using pessimistic locking. So which is it ? (I want to use optimistic locking)
但是我的日志中有一个例外，似乎表明我在使用悲观锁定。那么到底是哪一个呢?(我想使用乐观锁定)
update Song set acoustidFingerprint=?, acoustidId=?, album=?, albumArtist=?, albumArtistSort=?, albumSort=?, amazonId=?, arranger=?, artist=?, artistSort=?, artists=?, barcode=?, bpm=?, catalogNo=?, comment=?, composer=?, composerSort=?, conductor=?, country=?, custom1=?, custom2=?, custom3=?, custom4=?, custom5=?, discNo=?, discSubtitle=?, discTotal=?, djmixer=?, duration=?, encoder=?, engineer=?, fbpm=?, filename=?, genre=?, grouping=?, isCompilation=?, isrc=?, keyOfSong=?, language=?, lastModified=?, lyricist=?, lyrics=?, media=?, mixer=?, mood=?, musicbrainzArtistId=?, musicbrainzDiscId=?, musicbrainzOriginalReleaseId=?, musicbrainzRecordingId=?, musicbrainzReleaseArtistId=?, musicbrainzReleaseCountry=?, musicbrainzReleaseGroupId=?, musicbrainzReleaseId=?, musicbrainzReleaseStatus=?, musicbrainzReleaseType=?, musicbrainzWorkId=?, musicipId=?, occasion=?, originalAlbum=?, originalArtist=?, originalLyricist=?, originalYear=?, producer=?, quality=?, rating=?, recordLabel=?, releaseYear=?, remixer=?, script=?, subtitle=?, tags=?, tempo=?, title=?, titleSort=?, track=?, trackTotal=?, urlDiscogsArtistSite=?, urlDiscogsReleaseSite=?, urlLyricsSite=?, urlOfficialArtistSite=?, urlOfficialReleaseSite=?, urlWikipediaArtistSite=?, urlWikipediaReleaseSite=?, version=? where recNo=? and version=? [50200-172]
**org.hibernate.PessimisticLockException: Timeout trying to lock table ; SQL statement:**
update Song set acoustidFingerprint=?, acoustidId=?, album=?, albumArtist=?, albumArtistSort=?, albumSort=?, amazonId=?, arranger=?, artist=?, artistSort=?, artists=?, barcode=?, bpm=?, catalogNo=?, comment=?, composer=?, composerSort=?, conductor=?, country=?, custom1=?, custom2=?, custom3=?, custom4=?, custom5=?, discNo=?, discSubtitle=?, discTotal=?, djmixer=?, duration=?, encoder=?, engineer=?, fbpm=?, filename=?, genre=?, grouping=?, isCompilation=?, isrc=?, keyOfSong=?, language=?, lastModified=?, lyricist=?, lyrics=?, media=?, mixer=?, mood=?, musicbrainzArtistId=?, musicbrainzDiscId=?, musicbrainzOriginalReleaseId=?, musicbrainzRecordingId=?, musicbrainzReleaseArtistId=?, musicbrainzReleaseCountry=?, musicbrainzReleaseGroupId=?, musicbrainzReleaseId=?, musicbrainzReleaseStatus=?, musicbrainzReleaseType=?, musicbrainzWorkId=?, musicipId=?, occasion=?, originalAlbum=?, originalArtist=?, originalLyricist=?, originalYear=?, producer=?, quality=?, rating=?, recordLabel=?, releaseYear=?, remixer=?, script=?, subtitle=?, tags=?, tempo=?, title=?, titleSort=?, track=?, trackTotal=?, urlDiscogsArtistSite=?, urlDiscogsReleaseSite=?, urlLyricsSite=?, urlOfficialArtistSite=?, urlOfficialReleaseSite=?, urlWikipediaArtistSite=?, urlWikipediaReleaseSite=?, version=? where recNo=? and version=? [50200-172]
at org.hibernate.dialect.H2Dialect$2.convert(H2Dialect.java:317)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy22.executeUpdate(Unknown Source)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3123)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3021)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3350)
at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:140)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:354)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:276)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326)
at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:62)
at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1182)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1611)
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
at com.jthink.songkong.db.SongCache.loadSongsFromDatabase(SongCache.java:58)
at com.jthink.songkong.analyse.analyser.SongGroup.getSongs(SongGroup.java:48)
at com.jthink.songkong.analyse.analyser.MergeMusicBrainzMatches.matchToMissingTracks(MergeMusicBrainzMatches.java:318)
at com.jthink.songkong.analyse.analyser.MergeMusicBrainzMatches.call(MergeMusicBrainzMatches.java:105)
at com.jthink.songkong.analyse.analyser.MergeMusicBrainzMatches.call(MergeMusicBrainzMatches.java:40)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
update Song set acoustidFingerprint=?, acoustidId=?, album=?, albumArtist=?, albumArtistSort=?, albumSort=?, amazonId=?, arranger=?, artist=?, artistSort=?, artists=?, barcode=?, bpm=?, catalogNo=?, comment=?, composer=?, composerSort=?, conductor=?, country=?, custom1=?, custom2=?, custom3=?, custom4=?, custom5=?, discNo=?, discSubtitle=?, discTotal=?, djmixer=?, duration=?, encoder=?, engineer=?, fbpm=?, filename=?, genre=?, grouping=?, isCompilation=?, isrc=?, keyOfSong=?, language=?, lastModified=?, lyricist=?, lyrics=?, media=?, mixer=?, mood=?, musicbrainzArtistId=?, musicbrainzDiscId=?, musicbrainzOriginalReleaseId=?, musicbrainzRecordingId=?, musicbrainzReleaseArtistId=?, musicbrainzReleaseCountry=?, musicbrainzReleaseGroupId=?, musicbrainzReleaseId=?, musicbrainzReleaseStatus=?, musicbrainzReleaseType=?, musicbrainzWorkId=?, musicipId=?, occasion=?, originalAlbum=?, originalArtist=?, originalLyricist=?, originalYear=?, producer=?, quality=?, rating=?, recordLabel=?, releaseYear=?, remixer=?, script=?, subtitle=?, tags=?, tempo=?, title=?, titleSort=?, track=?, trackTotal=?, urlDiscogsArtistSite=?, urlDiscogsReleaseSite=?, urlLyricsSite=?, urlOfficialArtistSite=?, urlOfficialReleaseSite=?, urlWikipediaArtistSite=?, urlWikipediaReleaseSite=?, version=? where recNo=? and version=? [50200-172]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:158)
at org.h2.command.Command.filterConcurrentUpdate(Command.java:281)
at org.h2.command.Command.executeUpdate(Command.java:237)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 24 more
3 个解决方案
#1
4
You are using optimistic locking, as your UPDATE statement already indicates:
您正在使用乐观锁定，正如您的更新语句已经指出的那样:
where recNo=? and version=?
The presence of the version column is what optimistic locking is all about.
版本列的存在就是乐观锁定的意义所在。
You were misled by the PessimisticLockException which might be caused by an explicit or an implicit row-level lock.
可能由显式或隐式行级锁引起的悲观锁异常误导了您。
You must understand that locks are acquired on rows implicitly, to avoid dirty-writes. This book explains this topic with lots of diagrams and code examples.
您必须了解锁是在行中隐式地获取的，以避免dirty-write。这本书用大量的图表和代码示例来解释这个主题。
#2
7
This exception is due to Lock TimeOut.
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ;
这个异常是由于锁超时造成的。引起的:org.h2.jdbc。JdbcSQLException:试图锁定表的超时;
Check a solution here
检查一个解决方案
Also hibernate provides mechanisms for implementing both types of locking in your applications.
hibernate还提供了在应用程序中实现这两种锁定的机制。
Your locking strategy can be either optimistic or pessimistic.
锁定策略可以是乐观的，也可以是悲观的。
Optimistic
乐观
Optimistic locking ssumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back[1].
乐观锁定假定多个事务可以在不影响彼此的情况下完成，因此事务可以在不锁定它们影响的数据资源的情况下进行。在提交之前，每个事务都要验证没有其他事务修改它的数据。如果检查显示了冲突的修改，则提交事务将回滚[1]。
Pessimistic
悲观
Pessimistic locking assumes that concurrent transactions will conflict with each other, and requires resources to be locked after they are read and only unlocked after the application has finished using the data.
悲观锁定假定并发事务将相互冲突，并且要求在读取资源之后进行锁定，并且只有在应用程序使用完数据之后才解锁。
Details can be found here
详情可以在这里找到
#3
0
It is most likely, that you will use a different database for Testing, than you do in LIVE environment.
很有可能，您将使用不同的数据库进行测试，而不是在活动环境中。
H2 can produce errors, which might not appear on LIVE Database like Oracle, MySQL a.s.o.. Means even your code is correct, but the H2 can possibly be messing around.
H2可能会产生错误，这些错误可能不会出现在像Oracle这样的实时数据库中。这意味着即使你的代码是正确的，但是H2可能是在浪费。
The MVCC=true works perfectly. At the same time you should ensure that you have full integration tests on LIVE like systems for your use case in order to verify the LIVE Database behaviour.
MVCC = true完美工作。同时，您应该确保为您的用例在LIVE like系统上有完整的集成测试，以便验证LIVE数据库行为。
#1
4
You are using optimistic locking, as your UPDATE statement already indicates:
您正在使用乐观锁定，正如您的更新语句已经指出的那样:
where recNo=? and version=?
The presence of the version column is what optimistic locking is all about.
版本列的存在就是乐观锁定的意义所在。
You were misled by the PessimisticLockException which might be caused by an explicit or an implicit row-level lock.
可能由显式或隐式行级锁引起的悲观锁异常误导了您。
You must understand that locks are acquired on rows implicitly, to avoid dirty-writes. This book explains this topic with lots of diagrams and code examples.
您必须了解锁是在行中隐式地获取的，以避免dirty-write。这本书用大量的图表和代码示例来解释这个主题。
#2
7
This exception is due to Lock TimeOut.
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ;
这个异常是由于锁超时造成的。引起的:org.h2.jdbc。JdbcSQLException:试图锁定表的超时;
Check a solution here
检查一个解决方案
Also hibernate provides mechanisms for implementing both types of locking in your applications.
hibernate还提供了在应用程序中实现这两种锁定的机制。
Your locking strategy can be either optimistic or pessimistic.
锁定策略可以是乐观的，也可以是悲观的。
Optimistic
乐观
Optimistic locking ssumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back[1].
乐观锁定假定多个事务可以在不影响彼此的情况下完成，因此事务可以在不锁定它们影响的数据资源的情况下进行。在提交之前，每个事务都要验证没有其他事务修改它的数据。如果检查显示了冲突的修改，则提交事务将回滚[1]。
Pessimistic
悲观
Pessimistic locking assumes that concurrent transactions will conflict with each other, and requires resources to be locked after they are read and only unlocked after the application has finished using the data.
悲观锁定假定并发事务将相互冲突，并且要求在读取资源之后进行锁定，并且只有在应用程序使用完数据之后才解锁。
Details can be found here
详情可以在这里找到
#3
0
It is most likely, that you will use a different database for Testing, than you do in LIVE environment.
很有可能，您将使用不同的数据库进行测试，而不是在活动环境中。
H2 can produce errors, which might not appear on LIVE Database like Oracle, MySQL a.s.o.. Means even your code is correct, but the H2 can possibly be messing around.
H2可能会产生错误，这些错误可能不会出现在像Oracle这样的实时数据库中。这意味着即使你的代码是正确的，但是H2可能是在浪费。
The MVCC=true works perfectly. At the same time you should ensure that you have full integration tests on LIVE like systems for your use case in order to verify the LIVE Database behaviour.
MVCC = true完美工作。同时，您应该确保为您的用例在LIVE like系统上有完整的集成测试，以便验证LIVE数据库行为。