[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2131) SYBASE +select for update is showing deadlock because lock is not working properly
Michel HAMON (JIRA)
noreply at atlassian.com
Thu Feb 22 15:44:31 EST 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2131?page=comments#action_26224 ]
Michel HAMON commented on HHH-2131:
-----------------------------------
Hi,
I have the same problem.
I think "select ... for update" and "select ... holdlock" are not equals.
select ... for update case:
Thread 1: Thread 2:
begin tran begin tran
select ... for update
select .... for update (blocked until end of Thread1 end)
update ....
commit
select .... for update (end waiting)
update
commit
select ... holdlock case:
Thread 1: Thread 2:
begin tran begin tran
select ... for update
select .... for update (not blocked!!!)
update ....
(we will work with bad data because select was done)
update .... (blocked)
commit
update .... (unblocked)
commit
In page
http://archives.devshed.com/forums/databases-139/sybase-faq-12-19-ase-sql-1-of-3t-401268.html
or
http://www.isug.com/Sybase_FAQ/ASE/section6.1.html#6.1.4 ,
you can read "Note that the holdlock attribute does not stop another transaction from acquiring a shared level lock on the object (i.e. another reader). It only stops an exclusive level lock (i.e. a writer) from being acquired."
In Page:
http://www.faqs.org/faqs/databases/sybase-faq/part8/ ,
"Remember also that HOLDLOCK is for read-consistency. It doesn't block other readers!"
In CAST, i have open 2 connections, i have verify that i can execute:
begin tran
select .... holdlock
in both connection.
sp_lock confirm that i have 2 locks on the same rows (In reality 6, 3 by connection. I don't know why).
Test with Sybase 12.5 - Hibernate 3.2.2 - Java 1.4.2
The good news is that Hibernate 3.2.2 generate correctly the holdlock (it's not true in 3.2.1)
In page: http://www.hibernate.org/hib_docs/reference/en/html/transactions.htm, I have read
"If the database does not support the requested lock mode, Hibernate will use an appropriate alternate mode", Can Hibernate do it for Sybase?
Thanks for your help.
> SYBASE +select for update is showing deadlock because lock is not working properly
> ----------------------------------------------------------------------------------
>
> Key: HHH-2131
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2131
> Project: Hibernate3
> Type: Bug
> Components: core
> Versions: 3.1.3
> Environment: sybaseASE 12.5.03 in solaris +hibernate3.1.3
> Reporter: George Thomas
>
>
> I am connecting to Sybase database from hibernate.I tried setting locks using while selecting rows
> 1.session.load(class,id,LOCKMODE.UPGRADE)
> 2.session.lock(obj,LOCKMODE.UPGRADE)
> I am attaching a sample code while updating a field in table fund after selecting it.
> {
> Fund fund = null;
> ClientCredential credential = new ClientCredential("testDatabase", fund);
> org.hibernate.Session session = SessionFactoryManager.getSessionFactory(credential).getCurrentSession();
> Transaction transaction = session.beginTransaction();
> log.info("Run by " + Thread.currentThread().getName());
> fund = (Fund) session.get(Fund.class, new Short((short)12), LockMode.UPGRADE);
> fund.setLegalEntityOrgId(fund.getLegalEntityOrgId()+1);
> transaction.commit();
> }
> my requirement was that i wanted to do a select for update.I should acquire lock on certain rows and do a matching and if concurrent request comes ,it should wait till transaction is over that is lock is released.
> When I went through the hibernate code,the hibernate is making static queries in the beginning while loading session factory.Since we are appending the holdlock during execution,the sybase dialect is not appending.
> As a work around I tested with native sql with holdlock but when I test concurrent request using threads its bombing.Its throwing LockAcquisitionException.( Your server command (family id #0, process id #3777) encountered a deadlock situation.).can anyone give a solution to this problem???
> org.hibernate.exception.LockAcquisitionException: could not update: [com.citco.aexeo.common.dataaccess.domain.Fund#12]
> at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:84)
> at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2223)
> at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2118)
> at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2375)
> at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:91)
> at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
> at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:233)
> at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
> at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
> at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
> at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
> at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:333)
> at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:107)
> at com.satyam.Testing.doTransaction(Testing.java:103)
> at com.satyam.MyRunnable.run(MyRunnable.java:13)
> at java.lang.Thread.run(Thread.java:568)
> Caused by: com.sybase.jdbc2.jdbc.SybSQLException: Your server command (family id #0, process id #3777) encountered a deadlock situation. Please re-run your command.
> at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
> at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
> at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
> at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1642)
> at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1625)
> at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:91)
> at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
> at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2205)
> ... 14 more
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list