Locking from sql/hibernate |
Rob Gansevles |
2010-12-22 00:25:26 |
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 |
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 |
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 |
>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 |
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 |
>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 |
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 |
>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 |
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 |
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 |
|
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 |
It works, thanks!
Rob
|
Main
webmaster@hxtt.com