I have a function that will set a status_id (int) in a record and will add/remove a record depending on the new set status_id.
So when autosave = false and I change the status and a record needs to be added to the foundset, the record will be added to the foundset and after commit it will be added to the database. Or it will not be added after cancel (rollback).
BUT when I change the status and a record should be deleted from the foundset, it will directly be deleted from the database (autosave = false) So after cancel (rollback) the record is allready been deleted from foundset and database. I think the delete from database should be done after commit when autosave = false.
Is there a way to fix this? Or a good working work around?
You can start a transaction before deleting the records, in the case the user cancel you rollback the transaction and you get your deleted records back, if the user commits you commit the transaction and the recs are actually deleted from the DB.
I suggest you to check how your DB Server handles transactions though, not all DB servers use the same approach.
Thanks for the reply, but starting a transaction is not an option, because of the table lock. The whole table will be locked instead of just the record.
That is why I use the autosave = false. I can’t understand why it does support adding record(s) after commit but doesn’t support deleting record(s) after commit.
MichelvGent:
Thanks for the reply, but starting a transaction is not an option, because of the table lock. The whole table will be locked instead of just the record.
That is why I use the autosave = false. I can’t understand why it does support adding record(s) after commit but doesn’t support deleting record(s) after commit.
How do you know it locks the whole table and not just the row, or a page ?
Also I believe in MSSQL you have some control over how eager the locking is on a specific table. What version of MSSQL is this?
If you use MS-SQL 2005 or 2008 then you can set the server to not lock the whole table when a transaction has been started but to return the last committed values for the table.
You need to turn on read committed snapshot mode with a sql query like:
if CAST(serverproperty('ProductVersion') as varchar) not like '[1-8].%'
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE DBName SET MULTI_USER;
'
Exec(@sql)
end
go
Where you change DBName for your database name. This setting applies only to that database. Also note this does not work for SQL Server 2000 or earlier.
I agree that it should be default behaviour, but maybe it is because it uses the tempdb system table to store the row versions and there can be a performance hit for certain queries with this function turned on (although nothing I’ve ever noticed).
we are looking if we can change this behavior
but currently deletes have to be handled by a db transaction.
problem with deletes is that some deletes are done on whole foundsets
So we just send 1 sql statement to delete a complete set of records, this can be the foundset itself or a cascade related records delete.
This can then not be supported anymore if autosave is off, we really need to delete all records 1 by 1
Besides that now a delete of one record will make sure that all foundsets pointing to that record will also update itself, this has to be handled differently because we dont actually delete the record
but this is not so hard.
and last but not least all queries done after the delete have to filter out the deleted records… so every query that we then do on the deleted records table must add something like and pk not in (x,y,z)
jcompagner:
… and last but not least all queries done after the delete have to filter out the deleted records… so every query that we then do on the deleted records table must add something like and pk not in (x,y,z)
Argh! I can foresee a LOT of problems with DB servers like DB2-AS/400…
I’d stick to the KISS model
voidrollbackTransaction([rollbackEdited])
Rollback a transaction started by databaseManager.startTransaction().
Parameters
{Boolean} [rollbackEdited] – also rollback deletes that are done between start and a rollback call that are not handled by autosave false and rollbackEditedRecords() (default true)
The part in bold (“that are not handled by autosave false”) is not clear. If I use rollbackTransaction(), should everything following the startTransaction() be rolled back, without regard to autosave true or false?
I guess it’s just an error in the documentation, EVERYTHING that is done on the DB between a start and rollback of the transation must be undone.
If my understanding is correct the client will send every query through the same server connection so, if there is a db transaction active and you rollback, every change must be undone.
You are reading documentation for a future version.
In Servoy 6 you can optionally skip reverting edited records when you do a db transaction rollback.
NIcola, I agree with you that it >should< but when I see conflicting information on this forum and/or the documnentation/wiki, I prefer to confirm. Assumptions can get you into trouble. The environment where we came from before Servoy worked exactly as expected. Start transaction, do some things, roll back and everything always rolled back. The comments in this thread make this sound a little more iffy.
With in-memory transaction (i.e. autosave == off) then delete are still send to the backend database. You won’t be able to roll those back unless you actually started a database transaction first.
So that is the only difference between the 2 transaction models. In-memory will works fine but when you do deletes you want to use a database transaction.
So why not use database transactions all the time ? They lock records, pages or whole tables (depending on your database vendor and settings).
So you want your database transactions to be a short as possible. So lets say you use it in an edit window where the transaction started when the dialog opened and will end when the dialog closes (rolled back or committed). Now what happens if someone opens this window and goes for lunch or the bathroom for x minutes .
You will have a (potential) problem. So in-memory transactions are very nice to use in this case because they lock NOTHING. Only downside is a delete is send straight to the backend. (Servoy, any specific reason for this ?)
Another way to attack this issue is to use a delete flag on your record and use a table filter on this. So instead of actually deleting your record(s) you flag them as deleted and filter them out.
Of course this requires a periodic cleanup action to get rid of deleted records of a certain age (using the modification date for instance.
ROCLASI:
So why not use database transactions all the time ? They lock records, pages or whole tables (depending on your database vendor and settings).
So you want your database transactions to be a short as possible. So lets say you use it in an edit window where the transaction started when the dialog opened and will end when the dialog closes (rolled back or committed). Now what happens if someone opens this window and goes for lunch or the bathroom for x minutes .
You will have a (potential) problem. So in-memory transactions are very nice to use in this case because they lock NOTHING. Only downside is a delete is send straight to the backend. (Servoy, any specific reason for this ?)
This was my question in the first place. I don’t want to use the database transactions because I don’t want to depend on db vendor or settings. This is why I like the in-memory transactions.
I hope Servoy will support the rollback deleted records using in-memory transaction.