Tim,

I found a problem with transactions when I did some further tests with my
500K records table.  I was trying to do random updates of a blob field in
the table.

   for i := 1 to 1000 do
   begin
     if i mod 2 = 1 then key := i else key := Table.RecordCount - i;  //
try to simulate a zig-zag update of records
     if Table.FindKey([key]) then
     begin
       Database.StartTransaction;
       Table.Edit;
       Table.FieldByName('DOC_XML').AsString := 'abc';
       Table.Post;
       Database.Commit;
     end;
   end;

I encountered a AV error in FindKey above.  The interesting part is that
there will be no AV error if I take out the StartTransaction and Commit
statements.   More strangely, if I change it to sequential updates (i.e. use
key :=  i; instead of the zig-zag update), there will also be no AV error
even if I maintain the StartTransaction and Commit statements.

PK

Why do you open a transaction for this single field to edit? Wouldn't it be better to put the complete for-while into the transaction?

<<Why do you open a transaction for this single field to edit? Wouldn't it be better to put the complete for-while into the transaction?>>

The point is to simulate a user randomly updating the database.  Usually an application opens a transaction for each update operation rather than starting one when the user logs in and
commit when the user logs out.

PK

PK


If all you're doing is a simple edit on a single table then a transaction is OTT. Edit locks the record so no-one else can in much the same way as a transaction but that will lock the table.

Not sure exactly what difference optimistic and pessimistic locking might make, but in either case I think it will be the same as for a transaction with what you're doing.

Roy Lambert

Roy Lambert wrote:

> PK
>
>
> If all you're doing is a simple edit on a single table then a transaction is OTT. Edit locks the record so no-one else can in much the same way as a transaction but that will lock the table.
>
> Not sure exactly what difference optimistic and pessimistic locking might make, but in either case I think it will be the same as for a transaction with what you're doing.
>
> Roy Lambert
>

Roy,
   So you're saying he's wrapping a transaction inside of another
transaction?

Dave

Dave

> So you're saying he's wrapping a transaction inside of another
>transaction?

Nope. What I'm saying is that a transaction is not necessary for a single record edit. A transaction locks the WHOLE table and prevents anyone else altering any record for the duration of the transaction. Edit (certainly in pessimistic mode) will lock the RECORD and prevent anyone else from altering it. I think in optimistic mode and 8708 will be generated if someone else tries to post the record.

Since PK (nice name that) is only altering one field in one record a transaction in OTT. At least that's my belief.

Roy Lambert

<<If all you're doing is a simple edit on a single table then a transaction is OTT. Edit locks the record so no-one else can in much the same way as a transaction but that will lock the table.
Not sure exactly what difference optimistic and pessimistic locking might make, but in either case I think it will be the same as for a transaction with what you're doing.>>

Roy,

As I said in my original post, this is not a real application.  This is a test of the use of transactions.  In a real life situation, the update will definitely more than one table update (e.g. master-
detail update) which will need to use transaction.   The objective of my post is to let Tim know that there may be a problem with transactions, rather than looking for alternatives of doing
updates without transactions.

PK

While you may have encountered a problem with transactions in general, one
of the more common uses of transactions is to guarantee that a batch of
updates is accomplished without error.  So, in your test, I would include 5
or 10 or some random number of updates in each transaction.  What Roy said
about single updates is true so why bother wrapping the single upate in a
transaction?

B Miller

"PK" <pokka_chi@yahoo.com> wrote in message
news:9426CF53-DCA8-4245-A202-B7424B9B8AB9@news.elevatesoft.com...
> <<If all you're doing is a simple edit on a single table then a
> transaction is OTT. Edit locks the record so no-one else can in much the
> same way as a transaction but that will lock the table.
> Not sure exactly what difference optimistic and pessimistic locking might
> make, but in either case I think it will be the same as for a transaction
> with what you're doing.>>
>
> Roy,
>
> As I said in my original post, this is not a real application.  This is a
> test of the use of transactions.  In a real life situation, the update
> will definitely more than one table update (e.g. master-
> detail update) which will need to use transaction.   The objective of my
> post is to let Tim know that there may be a problem with transactions,
> rather than looking for alternatives of doing
> updates without transactions.
>
> PK
>

<< I found a problem with transactions when I did some further tests with my
500K records table.  I was trying to do random updates of a blob field in
the table.

   for i := 1 to 1000 do
   begin
     if i mod 2 = 1 then key := i else key := Table.RecordCount - i;  //
try to simulate a zig-zag update of records
     if Table.FindKey([key]) then
     begin
       Database.StartTransaction;
       Table.Edit;
       Table.FieldByName('DOC_XML').AsString := 'abc';
       Table.Post;
       Database.Commit;
     end;
   end;

I encountered a AV error in FindKey above.  The interesting part is that
there will be no AV error if I take out the StartTransaction and Commit
statements.   More strangely, if I change it to sequential updates (i.e. use
key :=  i; instead of the zig-zag update), there will also be no AV error
even if I maintain the StartTransaction and Commit statements. >>

Could you send me the table that you're using (or the instructions on how to
create it) ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

<<While you may have encountered a problem with transactions in general, one
of the more common uses of transactions is to guarantee that a batch of
updates is accomplished without error.  So, in your test, I would include 5
or 10 or some random number of updates in each transaction. >>

I have a habit of doing testing step by step.  My original plan was to start with one update first and then proceed to multiple updates when it passed
the single update transaction.

<<What Roy said about single updates is true so why bother wrapping the single upate in a
transaction?>>

Actually, I understand what Roy said.  I just think that it's better to alert Tim of this in case this is a genuine problem.

PK