I have found that TUpdateSQL.Apply and TUpdateSQL.ExecSQL do not
UnPrepare the query before returning. I have to manually call
MyUpdateSQL.Query[UpdateKind].UnPrepare.

I was wondering - is this documented behavior? If so, where is it
documented? D4.03 and BDE5.10. I've looked in the online help, but found
nothing.

For the interested - why I need to make sure the query is unprepared:
With MSSQL 6.5/7.0 via BDE+ODBC, I get deadlocks in certain situations.
In this case, the deadlock occurs when I have a query joining tables
Agare and Handlaggare, using cached updates. In the OnUpdateRecord
handler:

  case UpdateKind of
  {...}
  ukInsert: begin
    Z1DB.StartTransaction;
    try
      OwnerUpdateSQL.Apply(UpdateKind);
      HandlUpdateSQL.Apply(UpdateKind); { <<<---Deadlock!!! }
      Z1DB.Commit;
      UpdateAction:=uaApplied;
    except
      Z1DB.Rollback;
      raise;
    end;
  end;
  {...}

However, if I insert UnPreapre calls, everything works fine:

  case UpdateKind of
  {...}
  ukInsert: begin
    Z1DB.StartTransaction;
    try
      OwnerUpdateSQL.Apply(UpdateKind);
      OwnerUpdateSQL.Query[UpdateKind].UnPrepare;
      HandlUpdateSQL.Apply(UpdateKind);   { No deadlock! }
      HandlUpdateSQL.Query[UpdateKind].UnPrepare;
      Z1DB.Commit;
      UpdateAction:=uaApplied;
    except
      Z1DB.Rollback;
      raise;
    end;
  end;
  {...}

Any comments?

Kjell

Kjell,

When I started reading your message, I thought that this was just
another reason not to use MSSQL, but then I saw your code in the
OnUpdateRecord handler.

Do NOT....ever....start a transaction inside OnUpdateRecord. What you
want is something like this...OUTSIDE... OnUpdateRecord:

Database.StartTransaction
try
        Dataset.ApplyUpdates;
        Database.Commit;
        Dataset.CommitUpdates;
except
        Database.RollBack;
        raise;
end;

ApplyUpdates starts the process that leads to OnUpdateRecord. You want
all of this to happen inside a transaction, not the other way around.
As it is, you are starting a new transaction for every record that
needs updating. At the very least, that eliminates the need for cached
updates. At worst (in your case) the database thinks there's
contention for the same data and so the deadlock.

UpdateSQL does not unprepare for performance reasons. The underlying
query assumes that there will be more than one iteration of the query
and so it prepares on the first iteration but not on subsequent.
Prepare and unprepare both take time at the database and, if you run
the same query over and over, it's not required for each run. So
Delphi saves you time by not doing the prepare/unprepare each time.

You want it this way. That is, if it was otherwise, you would not like
it. You can use this same trick to speed up your application
generally. Suppose you have a query that looks up customers and the
SQL never changes but the query is run again and again. If you
manually prepare that query, then it will hesitate on the first run
(the prepare) but subsequent runs will be quick. Of course, you have
to be sure to manually unprepare when you destroy the form the query
is on.

Phil Cain
--

Yes, I know that's the way it's supposed to be done. However, what I'm
doing is to ApplyUpdates in the AfterPost event handler, so that every
changed record is immediately posted to the DB, just like it would in a
live dataset without cached updates. I actually don't want/need cached
updates for the caching. I need it to be able to update the multi-table
dataset, nothing else.

So, what I'm saying is that the ApplyUpdates will never apply changes to
more than one record. Thus, there is no performance loss in doing the
transaction/prepare/unprepare in the OnUpdateRecord handler. If there
aren't any other reasons for not doing it in there, I'd like to keep it
there.

Furthermore, in the real code, I do check InTransaction in the
OnUpdateRecord handler, so that if there is already a transaction in
progress, the handler won't try to start another one, nor commit or
rollback. But there is a true need to make sure the update is done under
a transaction, because the OnUpdateRecord updates two or three tables
for each dataset record, and this multi-table update *must* be atomic.
I.e. if the update to table B fails, the update to table A *must* be
rolled back. It's a safety measure to make sure there is a transaction
in there, even if the programmer forgets (or skips) a transation around
the ApplyUpdates call.

I also understand why the UpdateSQL doesn't unprepare (for performance
reasons). But the question is where *do* the UpdateSQL queries get
unprepared?? Or are they left in a prepared state forever? It can't be
the CommitUpdates call, because that is a dataset method, not a
UpdateSQL one. Since a dataset can have only one UpdateSQL linked to it
(not the other way around), the dataset.CommitUpdates can't unprepare
more than one UpdateSQL.

So, where the heck are the UpdateSQL queries unprepared??

Wouldn't it be better if the UpdateSQL required manual prepare and
unprepare just like TQuery? That way, the programmer would have full
control.

Kjell

Kjell,

Over the last 18 months or so, I've spend maybe 20 hours wandering
around in the cached updates code for the VCL. That's not enough time
to become an expert, but I've learned a number of things. Among them:

1. You _can_ attach more than one UpdateSQL to a dataset. You do that
by leaving the Dataset.UpdateObject blank and using the
TUpdateSQL.Dataset property instead. You have to do this in code
because that property is not published. Point more than one TUpdateSQL
to a dataset and the TDataset.ApplyUpdates fires both. (But remember
to unlink before you destroy the form that the TUpdateSQLs are on.)

2. TQuery does not require prepare/unprepare manually. It will do it
automatically if you don't.

3. You can take as much control over the TUpdateSQL queries as you
want. The TUpdateSQL.Query property points to TQuery objects and you
can prepre/unprepare and set properties to your heart's content. Be
aware, however, that those queries use the attached, driving query as
a template. I do not know, however, if it copies all the properties
from the driving query. I have written one routine that manually
prepares these. I'm not absolutely sure how much good it did, but
Delphi didn't complain.

4.If a TUpdateSQL query is unprepared, then it must be at the end of
ApplyUpdates or in CommitUpdates. The code for that will be in the
dbtables unit with the other code for TBDEDataset which handles all
the cached update process. The queries are created, configured and run
there and they can be unprepared there.

5. A transaction in OnUpdateRecord is a transaction within a
transaction. You are bound to have trouble with that. Your explicit
TDatabase.StartTransaction is in TDatabase. Anything in ApplyUpdates
is in TBDEDataset. The database object knows nothing of the BDE
object. This imperfect mutual awareness is just playing with fire. If
you insist on starting a transaction in OnUpdateRecord, then I know I
can't help you.

6. I don't use the OnAfter... OnBefore.... events with cached updates.
I suppose it can be done if the OnAfterPost event that you use is for
a dataset that plays a master role with regard to the other datasets
you are updating. If you do that, then you should start your
transaction and commit it there, not in OnUpdateRecord. Put the whole
transaction block in one place but outside OnUpdateRecord. Then you
can be sure there will always be a transaction.

If you do all that and still get a deadlock, then it's something else
in your code. One useful trick is to try to reproduce your problem in
a test app, outside the context of your main app.

Good Luck.

Phil Cain
--

The real question remains unanswered: Do the TUpdateSQL queries ever get
unprepared? If so, where? If not, shouldn't they? Is this issue
documented anywhere?

Further comments below.

This is simply not true. If I leave the UpdateObject property blank
(nil), I can't open the dataset as live, without attaching an
OnUpdateRecord handler. If I do attach an OnUpdateRecord handler, no
UpdateSQL will be triggered even if UpdateObject *is* assigned! This is
because the handler is supposed to do that instead.

On the other hand, if I assign UpdateObject with one of the UpdateSQLs I
need, and in code set all the UpdateSQLs' DataSet to point to the query,
only the one in the UpdateObject property is triggered.

Trust me, I just tried it! There is no way a TQuery can automatically
trigger more than one TUpdateSQL. You have to code an OnUpdateRecord
handler to do that. If you don't believe me, you can go ahead and check
the source for TUpdateSQL.SetDataSet and
TBDEDataSet.CachedUpdateCallBack. SetDataSet does not inform the TQuery
that it has been attached to the TUpdateSQL, so there's no way the
TQuery can trigger the TUpdateSQL. Also, CahcedUpdateCallBack calls the
OnUpdateRecord handler if assigned, otherwise calls UpdateObject.Apply
if UpdateObject is assigned. There's no code there to cycle through
multiple update objects. Nada.

Old news. The difference is that it will unprepare automatically if it
prepared automatically. Apparently UpdateSQL only prepares, but never
unprepares. That's what's giving me a headache.

I don't understand your point here. Why would I want to manually prepare
the UpdateSQL queries? The TUpdateSQL.ExecSQL already does that! That
was the problem!

Wrong. I checked the source for both. No call to UnPrepare or anything
that could lead to a call to UnPrepare. All it does is call a BDE api
function with a phase1 or phase2 commit command respectively. OK, so
perhaps there's a callback from the BDE? Wrong again. There is one,
TBDEDataSet.CachedUpdateCallBack. But that does nothing but call
OnUpdateRecord or UpdateObject.Apply (whichever is assigned). No
Unprepare there. I also searched the entire DBTables source for
UnPreapre (case insensitive, not whole words), and I simply can't find
an UnPrepare call that can be called during ApplyUpdates, CommitUpdates
or by the TUpdateSQL object itself.

I can only assume that the TUpdateSQL queries are not unprepared until
the TUpdateSQL object is destroyed. Is this a bug that should be
reported to Borland?

It is only a trans within a trans if there is an outer trans to begin
with. In my code there isn't (perhaps there should be). Furthermore, my
OnUpdateRecord code actually checks for an ongoing transaction before
trying to start another:

  WasInTrans:=DB.InTransaction;
  if not WasInTrans then DB.StartTransaction;
  try
    {do the work}
    if not WasInTrans then DB.Commit;
  except
    if not WasInTrans then DB.Rollback;
  end;

This way, if the ApplyUpdates call is done in a transaction, as you
strongly recommend, the OnUpdateRecord does not start another
transaction (which is impossible as far as I know, at least with some
servers). This is fine, because atomicity is guaranteed for the
OnUpdateRecord work by the already present transaction. If, however, the
ApplyUpdates call is not done in a transaction, the OnUpdateRecord
*must* guarantee atomicity on its own accord, and it will! Anything
wrong with this?

I do note that the docs for the BDE api function DbiApplyDelayedUpdates
for applying delayed (cached) updates says that it should be called in a
transaction. But I assume this is because the entires set of updated
records must be rolled back if an error occurs, because there is no way
of marking only part of the cache as committed. In my case, I know that
there is always exactly one record that's modified, so that problem is
null and void, thus no reason to put a transaction around the
ApplyUpdates call. Right?

Why shouldn't it work? Like I said, I don't want caching! I'm forced to
use it to be able to have a live dataset, that's all. To make it appear
like a "normal" non-cached live dataset, I'd like to ApplyUpdates after
every post (and delete of course).

The deadlock is some kind of limitation in MSSQL/ODBC. I get the
deadlock with VB/Access as well, so I can rule out BDE and Delphi. Not
sure whether it's ODBC or MSSQL itself though. I've bumped into the same
deadlock problem several times now, and it's very annoying. I can only
recommend to use another server. ...and make sure no queries remain
prepared unless I really want them to. Hence this discussion.

Thanks! I already have a working solution, but I'd like to know if I've
found a bug regarding unpreparation of TUpdateSQL queries.

Kjell

My answer is that I don't know. Of course they should. The prepare
step allocates resources in a relational database and the engine has
to be told that the resources are no longer needed or else they just
build up and cumulatively slow the session. All such resources are
temporary and can always be flushed by shutting the engine down and
restarting it, but that's not a desirable solution, no matter how
easy.

All I can tell you now is that I haven't seen the problem yet. But you
can bet that if I see it, I'll dig into the VCL code until I find the
answer and then I'll find a way to fix it.

Couple of things here. If by "live" you mean RequestLive = True then I
have to say that RequestLive never needs to be true in cached updates.
I leave it false by default.

And what I said is true. I do it. Suppose you have the query MyQuery
and cached updates is set to true. You may optionally leave the
UpdateObject blank. Instead, before you open the query, do this:
        MyUpdateSQL.Dataset := MyQuery;
This works. And you may have as many UpdateSQL objects as you like
pointing to MyQuery. I often have more than one.

Finally, OnUpdateRecord is not required, even though it is used (by me
anyway) more often than not. Try this in a test app: One grid, one
query with datasource. Make the query for one table. Set to cached
updates. (So far, no code, right?). Once you get that working, you'll
find that it works but doesn't save. Then put this one line:
        MyQuery.ApplyUpdates:
under a button or in the TForm1.OnCloseQuery event.

You will find (to your surprise, I think) that this works. Your
changes are saved. This is the irreducible minimum. All else is an
elaboration.

If you use the UpdateSQL.Dataset property, then the
TQuery.UpdateObject must be blank. There's a conflict otherwise.

You may be right about this. I've never tried it anyway. That is, when
I have more than one UpdateSQL component pointing to a query, there
are business reasons not to want it to be automatic. The
OnUpdateRecord declaration allows for only one Dataset and one
UpdateKind. A modification to one dataset may be accompanied by an
insert to the other, so I prefer to handle the management manually in
OnUpdateRecord, updating both UpdateSQL components from there.

As I said, I'm not sure what good I got from it. My point was that it
was possible to manipulate the UpdateSQL queries. (In my case I was
scanning and updating a very large file and wanted to keep the local
cache size down. So I iterated the ApplyUpates/CommitUpdates every 10
records. I wanted the UpdateSQL.Query.Prepared to survive that so I
gave the manual approach a try. Not sure if it did anything but I got
the performance I wanted, i.e. about 1 second per record)

Interesting. I checked the documentation I have for
dbiEndDelayedUpdates and there's not help there either. Thanks for the
heads up.

If you really need to know, you can check the
UpdateSQL.Query[uk].Prepared after CommitUpdates.

OTOH, it may be a good idea to leave it alone til the form is
destroyed. But I wouldn't depend on the destructor doing the right
thing with the db engine. If I find this to be a problem, I will
explicitly unprepare those in the destructor just because it's good db
management to do so.

I'm not sure how you are using "atomicity." I just wonder why in
blazes anyone would want to run one transaction for every record in a
feature that is designed to run many records in a transaction. At
best, you defeat the purpose of cached updates and, in defeating
something as complicated as this, there's got to be trouble. (I do not
go where angels fear to tread.)

And so, IMHO, there's no reason to use cached updates, except that you
have one joined query and need to modify more than one table.

But you should be using cached updates to capture a set of records
that need to be updated en masse or not at all. You should want the
rollback so that you can make corrections or blow the changes away. If
you're not designing in that way, then turn cached updates off and
write the code to use another query to do the updates.

In other words, I think you should stop fighting it. Go with the
design or do something else.

Then put ApplyUpdates in a transaction block and solve your problem.
At my client site today, we did some concurrency testing on our new
system. We put two people on the same receiving record and had them
both make changes. We got the expected deadlock and that was because
(and _only_ because) we had two people contending for the same
dataset.

But the good news was that, doing cached updates my way, the deadlock
was only temporary. When the data was saved by the people in the right
order, it was possible to resolve the deadlock and get both changes
saved. I haven't figured out the exact sequence yet, but I know now
that I can turn that deadlock from a fatal error into a resolvable
notification.

Your deadlock comes because you have more than one query (read cursor)
looking at the same data and you're trying to update out of order. I
have no idea how you did that, but I'd bet a meal that it's in your
code.

Good luck.

Phil Cain
--