| Subject | Re: [IBO] Transaction problems |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-12-12T00:42:38Z |
At 02:10 AM 12/12/2009, you wrote:
The advantage of that was that people could develop desktop-style apps for other databases, including InterBase and Oracle, that allowed the developer to ignore transactions and work in an "auto-committing" environment where the same transaction was started and committed over and over. The disadvantage - far greater - was that the characteristics of transactional database engines were not available; worse still, the BDE model caused database garbage to build up rapidly, to a point where everything just slowed down and died.
When one is writing applications for multiple users, that model presents major problems. That's why, very early in Delphi's evolution, dedicated components for transactional db engines sprouted in their dozens. Although Borland provided a BDE driver for IB for a while, it was never a serious option. Borland stopped supporting it at IB 5.6.
There's a lot of history between 1998 (when IB 5.1 was released) and now but, in short, converting a VCL-style app written for a non-transactional db engine to an effective application for a transactional db engine is not a trivial exercise. IBO provides you with the TDataset series of components (TIBO*) as a tool for this transition. What it cannot do is correct the wrong assumptions your existing, non-transactional code makes about transactions.
"Post" is a step between the local dataset and the database. For IB and Firebird, it causes an INSERT, UPDATE or DELETE statement to be passed to the database, where a new record (in the case of INSERT) or a new record version (for UPDATE) or a record stub (for DELETE) is stored at the server in the "space" isolated by your transaction.
What has happened in your current situation is that your app has inserted or appended this new record into the local dataset. It might or might not have been posted yet: it exists in the dataset's local buffer and, if "posted", it also exists in the database, in the space isolated by the transaction in which it was posted. That transaction can see it, so a succeeding "Edit" or "Delete" operation within the *same* transaction will work.
However, if your app then starts another transaction, while the original one is still uncommitted, and tries to post an update or delete for that record, then you will get the message that you saw. If you have started operating on the "same" dataset without refreshing its local buffer: when you try to post this record, that exists only in this stale buffer, of course it does not exist on the server side as a most-recently committed version that can answer the request from the new transaction.
For working with non-ISAM databases like IB, Firebird, Oracle. PG, etc., you really must acquire a strong appreciation of what's happening on the server side. You must learn to wrap tasks inside database transaction work units that are well in sync with the bounds of the logical transaction at the client side. There are some resources at the IBO website, at www.ibobjects.com/TechInfo.html that could help you to figure this out.
Also don't overlook the need to handle exceptions. It is totally essential. Once you have more than one client instance running your executable, the most common exceptions will be related to locking conflicts (different transactions simultaneously accessing tables in different ways and possible using different isolation levels). You do need to understand transaction isolation, too....
hth
Helen
>Using IBO 4.9.7, BCB6, Windows Vista, InterBase 2009."Record not found for [deletion | update]" occurs when you have inserted a record into your local dataset and then you try to post an update or delete on that record without having "posted" the record.
>
>I am porting an app using the VCL which was written for another
>database system (NexusDB). There, it works well since years.
>
>When I ported this to IBO using InterBase 2009, the basic functions
>also work. This includes "simple" additions and updates of records,
>whether framed by transactions or not.
>
>However, on some action sequences, I get the VCL error
>on updating records: "Datensatz wurde zum Aktualisieren nicht gefunden"
>which translates like "Data record was not found for making-it-current".
>The sequence is: Locate, Edit, Post. The Locate and the Edit areThe VCL was designed for Paradox and other ISAM databases (like NexusDB) that don't do transactions. To work, it required the BDE (which was the Paradox engine) which creates Paradox temp tables that it works on. The "transaction" was treated as a logical structure that existed only in the local client application; the BDE drivers would take care of managing that framework on behalf of the client.
>successful, which I confirmed by tracing. The error occurs on Post.
>Then, the same error rises on the following Commit, obviously leaving
>the transaction open, which at last causes the discarding of all further
>database actions executed done on the EXE file run.
>
>(I use a single transaction object created on opening of the database,
>paralleling the direct application of StartTransaction and Commit to
>the database object as it is possible with the other database system.)
The advantage of that was that people could develop desktop-style apps for other databases, including InterBase and Oracle, that allowed the developer to ignore transactions and work in an "auto-committing" environment where the same transaction was started and committed over and over. The disadvantage - far greater - was that the characteristics of transactional database engines were not available; worse still, the BDE model caused database garbage to build up rapidly, to a point where everything just slowed down and died.
When one is writing applications for multiple users, that model presents major problems. That's why, very early in Delphi's evolution, dedicated components for transactional db engines sprouted in their dozens. Although Borland provided a BDE driver for IB for a while, it was never a serious option. Borland stopped supporting it at IB 5.6.
There's a lot of history between 1998 (when IB 5.1 was released) and now but, in short, converting a VCL-style app written for a non-transactional db engine to an effective application for a transactional db engine is not a trivial exercise. IBO provides you with the TDataset series of components (TIBO*) as a tool for this transition. What it cannot do is correct the wrong assumptions your existing, non-transactional code makes about transactions.
>What went wrong?Rather, it's a case of something not "going right" for IB, because you don't understand transactions. It's essential to understand that operations in a transactional database must be wrapped in transactions. Understand also that the notions of "Insert", "Edit" and "Post" are record-level operations on local datasets. You can have many of these operations inside a single transaction: they will not be made permanent in the database until the wrapping transaction is committed.
"Post" is a step between the local dataset and the database. For IB and Firebird, it causes an INSERT, UPDATE or DELETE statement to be passed to the database, where a new record (in the case of INSERT) or a new record version (for UPDATE) or a record stub (for DELETE) is stored at the server in the "space" isolated by your transaction.
What has happened in your current situation is that your app has inserted or appended this new record into the local dataset. It might or might not have been posted yet: it exists in the dataset's local buffer and, if "posted", it also exists in the database, in the space isolated by the transaction in which it was posted. That transaction can see it, so a succeeding "Edit" or "Delete" operation within the *same* transaction will work.
However, if your app then starts another transaction, while the original one is still uncommitted, and tries to post an update or delete for that record, then you will get the message that you saw. If you have started operating on the "same" dataset without refreshing its local buffer: when you try to post this record, that exists only in this stale buffer, of course it does not exist on the server side as a most-recently committed version that can answer the request from the new transaction.
For working with non-ISAM databases like IB, Firebird, Oracle. PG, etc., you really must acquire a strong appreciation of what's happening on the server side. You must learn to wrap tasks inside database transaction work units that are well in sync with the bounds of the logical transaction at the client side. There are some resources at the IBO website, at www.ibobjects.com/TechInfo.html that could help you to figure this out.
Also don't overlook the need to handle exceptions. It is totally essential. Once you have more than one client instance running your executable, the most common exceptions will be related to locking conflicts (different transactions simultaneously accessing tables in different ways and possible using different isolation levels). You do need to understand transaction isolation, too....
hth
Helen