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
Locking from sql/hibernate
Rob Gansevles
2010-12-22 00:25:26.0
Hello,

I have a question about locking between applications using the DBF driver.

I know about the special locking mechanism implemented in the DBF driver (the virtial column "_LockFlag_"), but this requires special resultset handling.

My question is, is there support in the sql for the same locking?
Like a 'select for update [nowait]' statement we see in many other databases?

This would be very useful to us and others, we want to use it from our Hibernate Dialect.

If you do not support it, are you planning on implementing this feature in the near future?

Thanks is advance,

Rob Gansevles
Re:Locking from sql/hibernate
HXTT Support
2010-12-23 06:30:59.0
SELECT ... FOR UPDATE will lock all the selected rows so that other users cannot lock or update the rows until you close that ResultSet since v4.2.198. It will be available after about 1 hour.
If one row's locked by other applcation, your query sql will throw TimeOut exception.
Re:Re:Locking from sql/hibernate
Rob Gansevles
2010-12-27 01:06:16.0
Thanks for the quick reply and change.

The lock will stay until the resultset is closed, but in a hibernate environment, locks live until the end of a transaction, and the resultset is closed immediately after reading the data.

Can you keep locks alive until the end of the transaction?

Also, when I use the locking on a connection with transactionIsolation=TRANSACTION_READ_COMMITTED I receive following exception with a select-for-update:

Exception in thread "main" java.sql.SQLException: Failed to read Record of 2 in rob since it has been locked by another process
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.dbf.i.a(Unknown Source)
at com.hxtt.sql.a1.a(Unknown Source)

Rob
Re:Re:Re:Locking from sql/hibernate
HXTT Support
2010-12-27 19:44:49.0
>The lock will stay until the resultset is closed, but in a hibernate environment, >locks live until the end of a transaction, and the resultset is closed >immediately after reading the data.
>Can you keep locks alive until the end of the transaction?
If that resulteSet is not closed, but Connecition.commit has beed called, whether those locks is still alive, or removed?

>Also, when I use the locking on a connection with >transactionIsolation=TRANSACTION_READ_COMMITTED I receive following exception
>with a select-for-update:
>Exception in thread "main" java.sql.SQLException: Failed to read Record of 2
>in rob since it has been locked by another process
When record is locked, dirty read is allowed, but TRANSACTION_READ_COMMITTED has to wait lock release.


Re:Re:Re:Re:Locking from sql/hibernate
Rob Gansevles
2010-12-28 00:06:23.0
With Hibernate, resultsets will not stay open, all data will be read immediately and the resultset will be closed.
So that scenario should not happen, locks should be kept as long as the transaction is active (either committed or rolled back).

Regarding the exception, this happens with just 1 app connecting to the dbf file, there is no dirty read:

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
PreparedStatement ps = connection.prepareStatement("select * from rob where id = 1 for update");
ResultSet rs = ps.executeQuery();
while (rs.next())
........

Rob
Re:Re:Re:Re:Re:Locking from sql/hibernate
HXTT Support
2010-12-28 00:31:27.0
>Regarding the exception, this happens with just 1 app connecting to the dbf file,
>there is no dirty read:
The latest package has allowed that action.

>With Hibernate, resultsets will not stay open, all data will be read immediately
>and the resultset will be closed.
>So that scenario should not happen, locks should be kept as long as the
>transaction is active (either committed or rolled back).
We need to discuss what's the correct action for FOR UPDATE sql. For instance,
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
PreparedStatement ps = connection.prepareStatement("select * from rob where id = 1 for update");
ResultSet rs = ps.executeQuery();
connection.commit();//Whether that action will close those locks?
while (rs.next())
........



Re:Re:Re:Re:Re:Re:Locking from sql/hibernate
Rob Gansevles
2010-12-28 00:51:58.0
In our application that scenario does not happen.

You may want to check what other databases do in this situation, my guess would be that a commit automatically closes all open resultsets.

All we (and the hibernate users) need is that the lock stays alive until commit/rollback.

Rob
Re:Re:Re:Re:Re:Re:Re:Locking from sql/hibernate
HXTT Support
2010-12-28 01:09:23.0
>my guess would be that a commit automatically closes all open resultsets.
Maybe we can choose
Let ResultSet manages those locks, when Resultset.HOLD_CURSORS_OVER_COMMIT,
Let Transaction manages those lock, when ResultSet.CLOSE_CURSORS_AT_COMMIT
?
Re:Re:Re:Re:Re:Re:Re:Re:Locking from sql/hibernate
Rob Gansevles
2010-12-28 02:12:13.0
Actually, I think all users would expect locks to be managed by the transaction.

The proposed would work for us, as long as when ResultSet.CLOSE_CURSORS_AT_COMMIT is used, the locks are kept when the resultset is closed and the transaction is not committed yet.

Will ResultSet.CLOSE_CURSORS_AT_COMMIT be the default?

Rob
Re:Re:Re:Re:Re:Re:Re:Re:Locking from sql/hibernate
HXTT Support
2010-12-28 02:33:10.0
Google and find Apache Derby
When an implicit or explicit commit occurs, ResultSets that hold cursors open behave as follows:
* Open ResultSets remain open. The cursor is positioned before the next logical row of the result set.
* When the session is terminated, the ResultSet is closed and destroyed.
* All locks are released, except locks protecting the current cursor position of open cursors specified to stay open after commits.
* Immediately following a commit, the only valid operations that can be performed on the ResultSet are:
o positioning the ResultSet to the next valid row in the result with ResultSet.next().
o closing the ResultSet with ResultSet.close().

So that we choose Let Transaction manages those locks anytime.
>>If that resulteSet is not closed, but Connecition.commit has beed called, whether those locks is still alive, or removed?
Those locks wil be removed.
Re:Re:Re:Re:Re:Re:Re:Re:Locking from sql/hibernate
HXTT Support
2010-12-28 05:06:45.0
Supported. The latest package will be available after about 1 hour.
Re:Re:Re:Re:Re:Re:Re:Re:Locking from sql/hibernate
Rob Gansevles
2011-01-03 02:55:39.0
It works, thanks!

Rob

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