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.
|
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.
|
>delayedClose=0
Please use delayedClose=1000 or other value, then your locks can hold util meet the next update sql.
|
|
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?
|
>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.
|
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.
|
>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.
|
>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.
|
Main
webmaster@hxtt.com