How to implement a dialog for updating DB with a Cancel (rollback) option
-
Hi, I am trying to implement a dialog for updating some data in a DB. The OK button should actually save the data to the DB, while the Cancel button should discard the changes. The problem is, I need to be able to read the changed but not yet committed data within the dialog. I have read much information about transactions, isolation levels etc. and if I understand it correctly, the only way to achieve this is to wrap all the changes in a single transaction and use the READUNCOMMITED isolation level for it. Then in the OK button handler I'd just commit the transaction and in the Cancel button handler I'd call rollback. However, since I am using the SQLite.NET library, I am only able to use READCOMMITTED or SERIALIZABLE levels. What's more, in some other piece of software I have which uses MS Access database and solves exactly the same problem the transaction is started with the READCOMMITED isolation level and everything works as intended. That really puzzles me. :doh: Do you have any suggestions/advice for this kind of problem?
-
Hi, I am trying to implement a dialog for updating some data in a DB. The OK button should actually save the data to the DB, while the Cancel button should discard the changes. The problem is, I need to be able to read the changed but not yet committed data within the dialog. I have read much information about transactions, isolation levels etc. and if I understand it correctly, the only way to achieve this is to wrap all the changes in a single transaction and use the READUNCOMMITED isolation level for it. Then in the OK button handler I'd just commit the transaction and in the Cancel button handler I'd call rollback. However, since I am using the SQLite.NET library, I am only able to use READCOMMITTED or SERIALIZABLE levels. What's more, in some other piece of software I have which uses MS Access database and solves exactly the same problem the transaction is started with the READCOMMITED isolation level and everything works as intended. That really puzzles me. :doh: Do you have any suggestions/advice for this kind of problem?
Perhaps I misunderstand your question, but the whole idea of using a dialog is to let the user determine the variables. Once this is done, it's easy enough to validate the input in your code, before even creating a query.
My advice is free, and you may get what you paid for.
-
Perhaps I misunderstand your question, but the whole idea of using a dialog is to let the user determine the variables. Once this is done, it's easy enough to validate the input in your code, before even creating a query.
My advice is free, and you may get what you paid for.
The dialog doesn't just show some variables. It lists (complex) objects from the database and should allow adding/deleting/editing them. When he adds a new object he might decide to edit it before leaving the dialog, so I need to be able to store its properties somewhere. Of course I could hold the references to the new objects in my code, but I thought the most elegant way would be to store them in the DB immediately and access them in a unified way. Of course I still need the option to roll back all the changes when the user hits Cancel.
-
The dialog doesn't just show some variables. It lists (complex) objects from the database and should allow adding/deleting/editing them. When he adds a new object he might decide to edit it before leaving the dialog, so I need to be able to store its properties somewhere. Of course I could hold the references to the new objects in my code, but I thought the most elegant way would be to store them in the DB immediately and access them in a unified way. Of course I still need the option to roll back all the changes when the user hits Cancel.
Well, the basic choice is to keep all the data in client memory, or server memory, or write the whole thing to disk, right? If you have lots of clients sending transactions to a database server at the same time, I'd try and do all the work on the client. If the user might use a lot of time performing a transaction, a simple and robust solution could be to store the whole transaction in a temporary table (or structure of tables). That way you can perform some of the validation directly on input in your app, and then when the user clicks ok, you can still run some validation first between transactions from different users, and then commit the transaction to the live tables after that. If the user clicks cancel just delete the object from the temporary table. An added advantage of this method, is that you can think about offering the user "template" or "favorite" transactions.
My advice is free, and you may get what you paid for.
-
Well, the basic choice is to keep all the data in client memory, or server memory, or write the whole thing to disk, right? If you have lots of clients sending transactions to a database server at the same time, I'd try and do all the work on the client. If the user might use a lot of time performing a transaction, a simple and robust solution could be to store the whole transaction in a temporary table (or structure of tables). That way you can perform some of the validation directly on input in your app, and then when the user clicks ok, you can still run some validation first between transactions from different users, and then commit the transaction to the live tables after that. If the user clicks cancel just delete the object from the temporary table. An added advantage of this method, is that you can think about offering the user "template" or "favorite" transactions.
My advice is free, and you may get what you paid for.
Sure, it's always an option (albeit a cumbersome one) to store all the changes in the client code until the user hits OK or Cancel. I just wanted to know whether it is possible to set a "restore point" and later roll back all the changes up to that point. The database is single-user, so there won't be any read/write conflicts.
-
Hi, I am trying to implement a dialog for updating some data in a DB. The OK button should actually save the data to the DB, while the Cancel button should discard the changes. The problem is, I need to be able to read the changed but not yet committed data within the dialog. I have read much information about transactions, isolation levels etc. and if I understand it correctly, the only way to achieve this is to wrap all the changes in a single transaction and use the READUNCOMMITED isolation level for it. Then in the OK button handler I'd just commit the transaction and in the Cancel button handler I'd call rollback. However, since I am using the SQLite.NET library, I am only able to use READCOMMITTED or SERIALIZABLE levels. What's more, in some other piece of software I have which uses MS Access database and solves exactly the same problem the transaction is started with the READCOMMITED isolation level and everything works as intended. That really puzzles me. :doh: Do you have any suggestions/advice for this kind of problem?
The first rule of transactions is: Never have a conversation with the user while a transaction is active! So what you should do is to ask all the necessary questions before you start the db operations, perhaps show what's in the database now and how it's going to be changed etc. After the last yes/no question, then do all the operations at once and if everything goes fine, commit otherwise rollback. In my opinion (I know many disagree) Read uncommitted should be banned for good. In a well designed system there's no need for RU isolation level and as you mentioned many of the database products don't even support it (inlcude Oracle in your list). Having UI conversations etc while you have placed locks in the database typically causes prolonged transactions, slows the system down and decreases parallelism because of locking issues.
The need to optimize rises from a bad design.My articles[^]