| Subject | Re: [IBO] loosing records Help |
|---|---|
| Author | Helen Borrie |
| Post date | 2002-01-10T05:48:59Z |
At 08:24 PM 09-01-02 -0800, Daniel Bertin wrote:
Post is a DATASET method which stores a new version (or a new record) as a pending change in the database. At that point, it can be seen only by the transaction which posted it. It is neither committed to ( = "stored permanently in") the database, nor cancelled.
If your TRANSACTION (or IBODatabase) has Autocommit set True, then every Post will be followed automatically by an attempt to commit; otherwise, the transaction stays open and pending until you explicitly call COMMIT or ROLLBACK.
Now, if you are using CachedUpdates, then the dataset buffer is effectively deactivated and the current transaction ends. The user works in the cache buffer and then make a calls to ApplyUpdates. That calls StartTransaction, which temporarily deactivates autocommit behaviour, even if your transaction/IBODatabase does have Autocommit set True.
Then, ApplyUpdates([...]) will POST the caches for the named datasets. If any of these posts fails, you will get an ISC error and all of the changes (including good ones) remain pending.
At this point your application needs to intervene by intercepting the ISC error, interpreting it, and providing the user with a way to fix it. Here you would typically send the user a message identifying the problem record and telling her what to do about it. Following that, your code would call Abort (not CancelUpdates) to resume user access to the cached records.
You would do all of this inside a try..except block. Beyond this block (in error-free territory) you would call COMMIT in order to commit the changes to the database.
Even if you are not using cached updates, you still have to ensure that a COMMIT actually occurs. A commit (actually, commitretaining) that is signalled by a call to Post when Autocommit is true still needs to be checked to intercept errors from the database because, if an error (or locking conflict) occurs, the Commit will fail. This failure applies to all records in the transaction that had changes pending, not just the bad record.
1. Insert, Edit and Delete are client-side methods. The server has no knowledge of them. All the server knows is that a transaction has a certain set of records from one or some tables. That's all. So, after the SELECTed dataset reaches the client, the application has them stored in some buffers and the server knows that an uncommitted transaction has them. If the dataset buffers are in insert, edit or delete state, the server has no knowledge of it.
2. Now, a user performs some DML operation on a record in the client buffers. Now the buffer "knows" that something has changed, but the server still doesn't know anything more than it knew when the transaction started.
3. Next, the user posts some changes. Now, the client program sends details of these changes across to the server and the server, in its cache, creates a new record version for each affected record. If possible, it will lock these records. Then, if the changes are valid, there will be no errors and the Post has succeeded. But the records are still only "new record versions", pending a successful Commit.
4. Next (through Autocommit or an explicit call to Commit) the client attempts to commit these new record versions to the database. Even now, the commit can fail through locking constraints on further records posted since the last call to Post. Remember, if one record fails in the Commit operation, all of the pending records will fail also.
So, if your users are genuinely NOT seeing their work committed, then you have a hole in your application somewhere that is either failing to handle failed commits or is actually failing to call commit by one means or another.
But be sure that these apparently failed transactions really did fail, and that you are not simply looking at datasets which your application code failed to refresh after a Commit. The data that your users see in the buffer really are just a snapshot of records that fitted the criteria of the SQL statement at the moment they were requested. It is up to your application to get a fresh snapshot when the user needs one.
There are plenty of properties and methods in IBO to get a fresh view for the user - refresh, invalidate, buffer synchro flags, to name but a few...
regards,
Helen
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________
>Hi all,Daniel, cool down, take deep breaths. :))
>I 'm in a panic,
>does any one know of any instances of loosingNo. But you can "lose" records if they were never committed to the database at all.
>records(rows) in a table without deleting?
>I complete a activity record by setting a complete flag to Y, Store thePost <> Commit.
>values of the record in variables, post to the table,
>then I. Insert into a IBOTable, store the variable values into the fields
>and post the changes.
Post is a DATASET method which stores a new version (or a new record) as a pending change in the database. At that point, it can be seen only by the transaction which posted it. It is neither committed to ( = "stored permanently in") the database, nor cancelled.
If your TRANSACTION (or IBODatabase) has Autocommit set True, then every Post will be followed automatically by an attempt to commit; otherwise, the transaction stays open and pending until you explicitly call COMMIT or ROLLBACK.
Now, if you are using CachedUpdates, then the dataset buffer is effectively deactivated and the current transaction ends. The user works in the cache buffer and then make a calls to ApplyUpdates. That calls StartTransaction, which temporarily deactivates autocommit behaviour, even if your transaction/IBODatabase does have Autocommit set True.
Then, ApplyUpdates([...]) will POST the caches for the named datasets. If any of these posts fails, you will get an ISC error and all of the changes (including good ones) remain pending.
At this point your application needs to intervene by intercepting the ISC error, interpreting it, and providing the user with a way to fix it. Here you would typically send the user a message identifying the problem record and telling her what to do about it. Following that, your code would call Abort (not CancelUpdates) to resume user access to the cached records.
You would do all of this inside a try..except block. Beyond this block (in error-free territory) you would call COMMIT in order to commit the changes to the database.
Even if you are not using cached updates, you still have to ensure that a COMMIT actually occurs. A commit (actually, commitretaining) that is signalled by a call to Post when Autocommit is true still needs to be checked to intercept errors from the database because, if an error (or locking conflict) occurs, the Commit will fail. This failure applies to all records in the transaction that had changes pending, not just the bad record.
>the table has a filter which is set to complete = N, so that users onlyHow can you tell that the record definitely gets written to the table? You could check that by querying for it from a different transaction, e.g, through IB_SQL, for example. And/or, in development, you can watch it in the ib_monitor.
>have access to not complete records.
>
>the record definitely gets written to the table (as far as I can tell) and
>the process continues for about 200-300 records during the course of a day.
>every so often when the user returns to find the record(previouslyNo log file. But, as long as COMMIT gets called and it succeeds then the changes will be written permanently into the database. Possibly what is happening here is that you are ignoring database errors and assuming that everything else got committed...?
>inserted) it has disapeared.I looked in the table and no trace of it. only
>the completed one for that particular truck ID. this really puzzles me.
>does FB have a log file somewhere?
>has anyone ever heard of this type of behavior? when I post a record, do IOK, no cached updates, so you ARE working inside an isolated transaction. Now, where are these records?
>directly write to the table (cache updates off) does the record get stored
>in a buffer somewhere? where did it go????
1. Insert, Edit and Delete are client-side methods. The server has no knowledge of them. All the server knows is that a transaction has a certain set of records from one or some tables. That's all. So, after the SELECTed dataset reaches the client, the application has them stored in some buffers and the server knows that an uncommitted transaction has them. If the dataset buffers are in insert, edit or delete state, the server has no knowledge of it.
2. Now, a user performs some DML operation on a record in the client buffers. Now the buffer "knows" that something has changed, but the server still doesn't know anything more than it knew when the transaction started.
3. Next, the user posts some changes. Now, the client program sends details of these changes across to the server and the server, in its cache, creates a new record version for each affected record. If possible, it will lock these records. Then, if the changes are valid, there will be no errors and the Post has succeeded. But the records are still only "new record versions", pending a successful Commit.
4. Next (through Autocommit or an explicit call to Commit) the client attempts to commit these new record versions to the database. Even now, the commit can fail through locking constraints on further records posted since the last call to Post. Remember, if one record fails in the Commit operation, all of the pending records will fail also.
So, if your users are genuinely NOT seeing their work committed, then you have a hole in your application somewhere that is either failing to handle failed commits or is actually failing to call commit by one means or another.
But be sure that these apparently failed transactions really did fail, and that you are not simply looking at datasets which your application code failed to refresh after a Commit. The data that your users see in the buffer really are just a snapshot of records that fitted the criteria of the SQL statement at the moment they were requested. It is up to your application to get a fresh snapshot when the user needs one.
There are plenty of properties and methods in IBO to get a fresh view for the user - refresh, invalidate, buffer synchro flags, to name but a few...
regards,
Helen
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________