We have set autosave to false for our application - this gives our users an opportunity to make multiple edits and then save (or rollback) when they are ready.
This seems to be working great so far.
However, we have found that controller.deleteRecord() not only deletes the record but it commits it as well ! There is no way to rollback the delete
We’re all a little puzzled by this behavior - we assumed that delete would work the same way adds and changes do in that they can all be saved or rolled back.
If you use Servoy in memory transactions (autoSave = false) records are deleted straight ahead when you call deleteRecord(), it’s not possible to cancel a record deletion, only records creations and records updates can be rolled back. This is documented in Servoy manuals and online help.
If you want to be able to rollback records deletion you should use DB transaction instead.
There is but I can’t remember now, you should ask Servoy R&D team.
An easy way to workaround this is to use the onDelete table event to start a real DB transaction and to commit or rollback that transaction in your save and cancel methods (you must check if there is a Db transaction open to avoid exceptions); this way you use mainly in memory transactions and you use db transaction only when a user deletes a record (not so often in my experience).
Doublecheck your DB documentation to see how transactions are handled before implementing this technique.
This is a form where the user can add, edit en delete records, so a transaction has to cover
all three of these changes.
I supose i can do all transactions in the database, but i would like to use Servoy’s transactions,
since they also take care of refreshing the records on screen etcetera.
It should work as long as the onRecordDelete event is handled as soon as a record is deleted (it should but I haven’t tested).
Go to the table(s) that the form is using and add a method to the onRecordDelete event with just the code:
This proves that Servoy transactions do work with deleteRecord.
I was mislead by
If you use Servoy in memory transactions (autoSave = false) records are deleted straight ahead when you call deleteRecord(), it’s not possible to cancel a record deletion, only records creations and records updates can be rolled back. This is documented in Servoy manuals and online help.
If you want to be able to rollback records deletion you should use DB transaction instead.
i dis a simple test using one method on a button :
This does not delete the record, so transactions do work for deleteRecord.
I guess i misunderstood your remark.
So I am fine now, i can start a transaction when the user starts editing on the form and then let him/her
save or cancel. This will include new records, edited records and deleted records !
Hans Nieuwenhuis:
So I am fine now, i can start a transaction when the user starts editing on the form and then let him/her
save or cancel. This will include new records, edited records and deleted records !
Yes but keep in mind that DB servers handles transactions in different way, some lock only the touched records, some others lock the entire table and if I remember correctly there’s a DB server that locks records even for read. That’s why everybody is trying to avoid db transactions: you loose DB indipendency and with some Db server you have unexpected results.
But did you try the mixed approach I explained in my post? Is it working?
Hans Nieuwenhuis:
The cancel does a rollback of the edited records and of the Servoy transaction.
This works for edit/add as well as for delete !!
No, I’m sorry but it does not.
You can’t rollback a delete if you use a Servoy in memory transaction, you can only rollback deletes if you use DB transactions.
Check your admin pages, if you can rollback deletes you’ll see a DB transaction listed there.
But if hans just starts a db transaction (databasemanager.startTransaction()) and a servoy transaction (databasemanager.setAutoSave(false)) then it does work fine
You almost never have changed records in the database (but not committed yet) because we dont save updates or inserts so others wont really block on your transaction that often
and only deletes are send and monitored by the db transaction.
Then you can save everything (databasemanger.setAutoSave(true)) and stop the transaction.
(or do a rollback)
jcompagner:
You almost never have changed records in the database (but not committed yet) because we dont save updates or inserts so others wont really block on your transaction that often and only deletes are send and monitored by the db transaction.
True, but “almost never” doesn’t mean always.
Let’s take MS SQL Server as example, the locking mechanism defaults to READ UNCOMMITTED, here’s how it works:
So if one user deletes a record other users will see the deletion instantly and if the first user rollback the transaction they could be working on inconsistent data.
My personal opinion is that once you decide to use transactions you need to be very careful and you sorta loose database indipendence, maybe is not an issue for Hans but it could very well be in the future. Just my 2 cents.
So if one user deletes a record other users will see the deletion instantly and if the first user rollback the transaction they could be working on inconsistent data.
With Servoy on Oracle when a user deletes a record, other users do not notice this. (autosave off and use transactions)
Only when the users that deleted the record commits the transaction will the other users notice it.
That is exactly how i would want it.
I know it is a different story with Sql-server, that is why I use Oracle.
Yes, Oracle is different, that’s why I told you that you’d loose DB indipendence, I just wanted to point out that if this technique is okay for you it might not be ok for other readers.
we have READ COMMITTED the problem is that that is implemented (in my eyes) wrongly in many databases, i think from top of my head only postgresql and oracle are doing it right.
What other do wrong is that when 1 person have uncommitted data the other persons if it does a read query on that same data will block until the first person does a commit
oracle/postgresql will just read the committed data of the data before the change (what i find logical in the READ COMMITTED transaction level)
So yes in the example hans uses above only when the user deletes a record and then doesnt press save or cancel pretty quickly after that then we have a window
that others could be blocked
Johan, so you set the READ COMMITTED isolation in the JDBC layer? For every database brand? It would be very helpful to know if you set it in the JDBC layer or if it sticks to the database default.