No other error in java log on client, or in servoy_log on server !!
the transactions exits just before the committransactions, cause I test with hasTransaction and put the output in application logfile.
Regards,
databaseManager.hasTransaction() doesnt test this,
that is just a test on the client, that the client thinks it has a transaction object.
Problem is here that something went wrong on the server and the server dropped the transaction connection for that client already.
just a guess, but is there someone changing/updating server definitions through the admin pages at the same time?
No, I am sure that that does not happen.
I am the only one that has access to the admin page.
In the save routine I do a check if a transactions exists, then I do an extra save ( as You asked me ) and then the committransaction is the
next statement. So this is done in a few lines in 1 method and no other method is called in between.
The commitTransaction returns false in this case (this only happens now and then, in 99% of the call’s to this save method commitTransaction returns true)
Regards,
Johan,
Is there anything I can do to get to the rootcause if this issue ??
Regards,
no not yet, the only thing we can currently think of is that the server is restarted or the somehow the server configuration is touched through the admin pages.
that exception says, id=0 and that 0 means that this is the very first transaction that is ever done for that server config.
I am sure that that is not the case !
This is about the 30’st transaction of that day. ( and 29 went oke)
Since startTransaction does not return anything I am not sure if the transaction has been created when this problem occurs.
The save is done in the framework and the transaction exists only for a very short time ( start transaction, pre-save, save, post-save, save, commit transaction)
so I can’t check it on the server admin page.
Regards,
I see an issue that could be related to the situation that Hans describes.
When doing:
databaseManager.startTransaction()
I don’t see a transaction on the admin pages after this statement.
But when I step by the next statement (which I added, because I didn’t see a transaction appear)
databaseManager.refreshRecordFromDatabase(forms[_form].foundset, forms[_form].foundset.getSelectedIndex())
then I see a transaction on the adminpages.
Similar issue when locking records:
databaseManager.acquireLock(forms[_form].foundset, 0)
databaseManager.startTransaction()
then I don’t have a transaction, but I do have a lock. Where changing the order of those statements:
databaseManager.startTransaction()
databaseManager.acquireLock(forms[_form].foundset, 0)
then I do have a transaction and I have a lock.
Can someone explain that?
Hans Nieuwenhuis:
databaseManager.startTransaction()
if (!databaseManager.saveData()) {
_thePressedButton = plugins.dialogs.showErrorDialog(‘Error in first Save’, ‘See log’, ‘OK’);
_failedArray = databaseManager.getFailedRecords()
for (var j = 0; j < _failedArray.length; j++) {
_record = _failedArray[j];
plugins.Log.error('Error : ’ + _record.exception);
}
databaseManager.rollbackEditedRecords()
databaseManager.rollbackTransaction()
return -1
}
Hans,
If the issue that I’ve seen has something to do with your case and I’m also familiar with that pre-save/post-save setup, then probably you must do this:
User presses edit button
Start Transaction
Refresh record from database
User changes fields
User presses save button
Run pre-save code
Save data
Run post-save code
Save data
Commit Transaction
Martin
Hi Martin,
As Johan described to me, starting a transaction only sets a flag.
The first change ( saveData ) to the database will start the actual transaction.
So it is expected behaviour that you don not see the transaction after the startTransaction command.
Maybe Johan can confirm my explanation or correct it ?
B.t.w. :
User presses edit button
Start Transaction
Refresh record from database
User changes fields
User presses save button
Run pre-save code
Save data
Run post-save code
Save data
Commit Transaction
In my opinion doing it this way leaves the transaction is open much to long.
I suggest :
enter edit mode
user changes data
user hits save button
validation checks
presave method
saveData
postsave method
saveData
commitTransaction
Regards,
hans explained it exactly right.
A transaction only really does something at the first moment a connection is used for that client
Before that it is only state inside the client.
(startTransaction doesnt result in a call to the server at all)
jcompagner:
(startTransaction doesnt result in a call to the server at all)
Eh, what ? So you don’t use database transactions at all ???
EDIT: oh wait…I should read the thread better.
So the actual call it send to the backend db when it does a save.
So lets say I want to do a bunch of SELECT’s in a db transaction to make sure I get a consistant snapshot of some data (race conditions, ya know) then this won’t work since I don’t save any data. Am I correct in thinking that ?
that will also work, at the moment you make a server call the transaction id (the state in the client) is send to the server and a connection is reserved for that transaction.
But how do you do your selects? Select for update? else you still are not sure that it is consistent…
jcompagner:
But how do you do your selects? Select for update? else you still are not sure that it is consistent…
No, just selects. My writes are done in db transactions. When I do, lets say, 3 selects to build up a report or export and after select 1 another client saves data then my select 2 and 3 might fetch (related) data that select 1 didn’t have. Hence the use of a db transaction for a select.
Although now you make me wonder that this might not work the way I expect it to work…
Edit: I see I need to set the isolation level of the transaction to ‘SERIALIZABLE’ to be able to do that (on Pg).
Hi,
I am not sure about other databases, but I know how Oracle handles this.
(and I am learing about postgresql)
Transaction-Level Read Consistency
Oracle also offers the option of enforcing transaction-level read consistency. When a transaction runs in serializable mode, all data accesses reflect the state of the database as of the time the transaction began. This means that the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries made by a serializable transaction do see changes made by the transaction itself. Transaction-level read consistency produces repeatable reads and does not expose a query to phantoms.Read committed This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.
Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.Serializable Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.
Read-only Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.Set the Isolation Level
Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
To save the networking and processing cost of beginning each transaction with a SET TRANSACTION statement, you can use the ALTER SESSION statement to set the transaction isolation level for all subsequent transactions:
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
Hi Hans,
yes, I just found it in the Pg manual.
http://www.postgresql.org/docs/9.0/stat … ction.html
‘READ COMMITTED’ is the default on PostgreSQL.
ROCLASI:
jcompagner:
But how do you do your selects? Select for update? else you still are not sure that it is consistent…No, just selects. My writes are done in db transactions. When I do, lets say, 3 selects to build up a report or export and after select 1 another client saves data then my select 2 and 3 might fetch (related) data that select 1 didn’t have. Hence the use of a db transaction for a select.
Although now you make me wonder that this might not work the way I expect it to work…
Edit: I see I need to set the isolation level of the transaction to ‘SERIALIZABLE’ to be able to do that (on Pg).
or i guess use something like select for update. Then it should lock the things you update and you can calculate on that data the thing you want to update and do the commit
others that also want to do that also try to select for update but those will be blocked.
Servoy has support for locking rows and using select for update in the database for that…
Hi Johan,
I prefer to stay away from any locking if I can.
Anyway, just setting the isolation level to ‘SERIALIZABLE’ does the trick.
But I do not know if it is possible in Servoy to set the
isolation level for a transaction ?
Maybe a nice new feature ??
Hi Hans,
If I am not mistaken when you start a transaction then your rawSQL plugin will also use this transaction. So you can send the SQL after you started the transaction using the rawSQL and then everything you do in that transaction (using Servoy objects or otherwise) will use use this transaction (and isolation level).