Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
JPA EntityManager with Hibernate Locking not working
ams
2013-11-08 17:48:43.0
I wrote a small test program to find out if HXXT driver can work with hibernate and do pessimistic locking. The code for this program is shown below, as you can see it just finds an entity then calls entity manager lock to get a PESSIMITIC_WRITE LOCK. However after the record is locked from the java code foxpro 2.6a can still obtain a lock on the supposedly locked row.

public static void main(String[] args) throws IOException {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("com.example");
EntityManager em = emf.createEntityManager();

em.getTransaction().begin();
System.out.println("Reading with pessimistic write");
TestEntity e = em.find(TestEntity.class,1);
em.lock(e, LockModeType.PESSIMISTIC_WRITE);
e.setValue(e.getValue() + 1);
System.out.println(e);
System.out.println("Press any key to unlock");
System.in.read();
em.getTransaction().commit();
}

Here is the console output from the above program

17:46:08,897 INFO [Version] HCANN000001: Hibernate Commons Annotations {4.0.1.Final}
17:46:08,912 INFO [Version] HHH000412: Hibernate Core {4.1.12.Final}
17:46:08,912 INFO [Environment] HHH000206: hibernate.properties not found
17:46:08,912 INFO [Environment] HHH000021: Bytecode provider name : javassist
17:46:09,225 INFO [DriverManagerConnectionProviderImpl] HHH000402: Using Hibernate built-in connection pool (not for production use!)
17:46:09,225 INFO [DriverManagerConnectionProviderImpl] HHH000115: Hibernate connection pool size: 20
17:46:09,225 INFO [DriverManagerConnectionProviderImpl] HHH000006: Autocommit mode: true
17:46:09,225 INFO [DriverManagerConnectionProviderImpl] HHH000149: JDBC isolation level: READ_COMMITTED
17:46:09,225 INFO [DriverManagerConnectionProviderImpl] HHH000401: using driver [com.hxtt.sql.dbf.DBFDriver] at URL [jdbc:dbf:/w:/Proddev/?lockType=FOXPRO;lockTimeout=1000;delayedClose=0]
17:46:09,225 INFO [DriverManagerConnectionProviderImpl] HHH000046: Connection properties: {user=, password=****, autocommit=true, release_mode=auto}
17:46:09,350 INFO [Dialect] HHH000400: Using dialect: com.hxtt.support.hibernate.HxttDbfDialect
17:46:09,381 INFO [TransactionFactoryInitiator] HHH000268: Transaction strategy: org.hibernate.engine.transaction.internal.jdbc.JdbcTransactionFactory
17:46:09,397 INFO [ASTQueryTranslatorFactory] HHH000397: Using ASTQueryTranslatorFactory
17:46:09,444 INFO [Version] HV000001: Hibernate Validator 4.3.0.Final
Hibernate:
create table HT_locktest (id integer not null, hib_sess_id CHAR(36) null)
Reading with pessimistic write
Hibernate:
select
testentity0_.id as id1_0_0_,
testentity0_.value as value2_0_0_
from
locktest testentity0_
where
testentity0_.id=?
Hibernate:
select
id
from
locktest
where
id =? for update

TestEntity{id=1, value=21}
Press any key to unlock

The persistence unit is configured like so.



value="com.hxtt.support.hibernate.HxttDbfDialect"/>








value="jdbc:dbf:/w:/Proddev/?lockType=FOXPRO;lockTimeout=1000;delayedClose=0"/>




I also tried using RAW JDBC as show in the example below.

public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException
{
Class.forName("com.hxtt.sql.dbf.DBFDriver");
Connection conn = DriverManager.getConnection("jdbc:dbf:/w:/Proddev/?lockType=FOXPRO;lockTimeout=1000;delayedClose=0", "", "");
Statement stmt = conn.createStatement();
System.out.println("trying to lock recrod 1");
boolean execute = stmt.execute("SELECT * FROM locktest WHERE id=0 FOR UPDATE");
// stmt.close();
System.out.println("Record locked");
System.in.read();
}

The JDBC code for locking works correctly, once I lock a row from Java FoxPro can not lock the same row. However, If I add a stmt.close() the one commented in the code above the locking stops work and foxpro can lock recrods that java locked.
Re:JPA EntityManager with Hibernate Locking not working
HXTT Support
2013-11-13 06:37:10.0
Tested.

SELECT * FROM UPDATE will lock the want data rows, but If that ResultSet object close, or the owner statement of that ResultSet object close(), or Connection.commit called, those record locks will be released.

That's why
// stmt.close();
will release those locks.

>as you can see it just finds an entity then calls entity manager lock to get a PESSIMITIC_WRITE LOCK. However after the record is locked from the java code foxpro 2.6a can still obtain a lock on the supposedly locked row.
It means that Hibernate should release the produced ResultSet object by "select * ... for upate" at once:( We will dig out how to tell Hibernate can't release it.
Re:Re:JPA EntityManager with Hibernate Locking not working
HXTT Support
2013-11-13 18:37:26.0
>delayedClose=0
Please use delayedClose=1000 or other value, then your locks can hold util meet the next update sql.
Re:Re:Re:JPA EntityManager with Hibernate Locking not working
ams
2013-11-13 20:11:31.0
Thanks I will try this out, what exactly does delayedClose=1000 mean / do ? does that mean wait 1000 milliseconds after statement close to free lock? how do I know the right value for delayedClose?
Re:Re:Re:Re:JPA EntityManager with Hibernate Locking not working
HXTT Support
2013-11-13 20:16:52.0
>what exactly does delayedClose=1000 mean / do ?
>does that mean wait 1000 milliseconds after statement close to free lock?
>how do I know the right value for delayedClose?
After yoou using "select * .. for update", the connection hold record locks, but if no following operation to using that table in delayedClose period, that table will be closed and release all lock.
Re:Re:Re:Re:Re:JPA EntityManager with Hibernate Locking not working
ams
2013-11-13 20:33:53.0
I have tried out your suggestion and it works if i set delayedClosed to 1000 my example works.

However can you please clarify the following points.

1. What are the units for delayedClose seconds or milliseconds or something else?

2. how do you detect that the connection is not being used for the close period is there a background thread running that is doing checking if the delayed close period is over.
Re:Re:Re:Re:Re:Re:JPA EntityManager with Hibernate Locking not working
HXTT Support
2013-11-13 20:57:12.0
>1. What are the units for delayedClose seconds or milliseconds or something else?
milliseconds. delayedClosed=0 means to close all used table at once.

>2. how do you detect that the connection is not being used for the close period
> is there a background thread running that is doing checking if the delayed close
> period is over.
A delayed thread to close all idle tables. No any idle table, that thread will stop too after an idle period.
Re:Re:Re:Re:Re:Re:JPA EntityManager with Hibernate Locking not working
HXTT Support
2013-11-13 20:59:06.0
>However after the record is locked from the java code foxpro 2.6a can still obtain a lock on the supposedly locked row.
It will still lock/unlock automatically the want row for update sql, although the locks of "select * for upate" sql has released.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap