Subject Re: [ib-support] Re: Deadlock and wait
Author Woody
From: "Alexander V.Nevsky" <alex_vnru@...>
Sent: Thursday, July 26, 2001 2:37 PM
> Hi, Woody. My post is rather late, but why you (and many other
> people) use the same transaction to show user large amount of records
> in the grid and for performing changes? I use <read_commited read>
> transaction for first and <concurrency nowait> for second:

Actually, it's not a large amount of data. I use queries that only pull one
record for display. I use Interbase procedures for locating and returning
the PK for the record I need and then use that in a query to only pull that
record. However, it's the same principle when 2 people pull the same record
which can and does happen in multi-user systems. The only grids I use for
multiple records are for setup and lookup table editing which doesn't occur
often and generally only an administrator handles it in off times. After
changing from Access (I know, don't laugh) to Interbase, I changed all my
tables to queries and wrote procedures for retrieving the next or previous
record and only pull the information needed at the time of a request. But,
even in pulling one record, the transaction must be left open to display the
data unless I copy it into another structure in the program.

> 1. User looks at data.
> 2. User begins edit
> 2.1. Snapshot started
> 2.2. Current row re-fetched by another query within snapshot
> and is showed in another controls (popup form or panel)
> 2.3. Changes are made in controls
> 2.4. Try to save changes. Variants:
> 2.5.1. Success - Commit snapshot
> 2.5.2. Lock conflict
> If changes are saved in single query then Commit
> else Rollback snapshot;
> GoTo :) 2.1.
> You can save within controls user's changes and show him
> new data (reconsile) or simply show new data only.
> 2.6. Refresh row in main dataset

I understand the concept of what you are saying, but it is difficult in deed
to always compare the information you just pulled out of a table with
multiple fields including blobs before actually trying to update anything. I
thought that's what transaction control is for. Also, in order to use this
concept, you must store the data somewhere outside of any data aware
controls such as tables, queries, controls (data-aware), etc.. That means
coming up with objects for storing the information in the interim. Not a
pretty site if you have to go through an app that's already complete.

> It allows you commit read transaction to free resources in moments
> when it is suitable for logic of your application.

See previous point.

> And you can even
> avoid effors on reconsiling by forcing early lock conflict and
> informing user that raw is busy via dummy update before 2.2. It's not
> good tactics, but in some cases acceptable when large memo columns are
> edited.
> Yes, it's retro style. You want edit within grid, I know. If so,
> you can emulate it by doing all stuff around saving in snapshot on
> MainDataSet.BeforePost and then do MainDataSet.Cancel within
> BeforePost. And Refresh on AfterCancel. BTW, FIBPlus (descendat from
> FIB like IBX) allows to assign different transaction for modify
> objects of dataset (little spam :).

As I said earlier, I'm not using grids and the only way to avoid keeping a
transaction open is to copy the data to an intermediate buffer of some type
(client dataset, object structure, etc.). Having separate transactions would
be double the trouble, IMO, if you can't close the read transaction in the
first place.