Board index » delphi » Transactions using Paradox tables-Record Lock Failed - Losing faith in BDE

Transactions using Paradox tables-Record Lock Failed - Losing faith in BDE

Well, now that I've looked through the other transaction oriented
problems with Delphi and Paradoxon this newsgroup, I'm beginning to
think it just doesn't work right and can NOT be trusted!

This is important, because I'm losing faith in the BDE.  Please
somebody tell me if this is my fault or not.

ONLY WHEN IN A TRANSACTION (TDatabase.Starttransaction), when
performing two consecutive sql update statements, the second recieves
an error, "Record Lock Failed".  If you reword the sql statement to do
the same thing, but slightly differently, it works fine.  If you don't
use sql, but use code, there are no problems.  THIS CAN BE DUPLICATED
using Delphi's Database Explorer tool (as well as using a TQuery
component in a Delphi app, which is my problem).

Any explanation would help.  Thanks!

Details...
1) I'm using Delphi 3.0 with BDE 4.x(the updated version, Build 5.83).
2) Create a paradox table (TESTTAB.DB), with a primary key (needed for
transactions); See below for table structure and data
3) Create an alias that points to the directory with TESTTAB.DB
4) Start Database Explorer and set 'Options->Transaction Isolation' to
'Dirty Read'
5) Using Database Explorer, start a transaction on that alias
6) Enter/execute the following sql statement...
"update TESTTAB set MYCOUNT = MYCOUNT - 1 where ID = 1"
7) Enter/execute the following sql statement...
"update TESTTAB set LINK_ID = NULL where LINK_ID not in (select ID
from TESTTAB where MYCOUNT > 0)
8) Commit the transaction.
9) All works fine.

Now, go back to step 5, then 6, and replace 7 with...
7) Enter/execute the following sql statement...
"update TESTTAB set LINK_ID = NULL where LINK_ID = 1 and LINK_ID not
in (select ID from TESTTAB where MYCOUNT > 0)"

There it is... "Record lock failed"

Note: When using the first sql statement in code, I get an exception
at address 00000000 when the table tries to do a refresh!

DB Table (TESTTAB.DB)...
Field Name | Type | Size | Key
------------------------------
ID           I            *
MYCOUNT      I
LINK_ID      I

Data...
ID | MYCOUNT | LINK_ID
1   1          1
2   3          1
3   3          1

FYI

If you replace the sql statements with the following, which does the
same thing, all works fine...
(Make sure you're in a transaction, with the isolation level set to
'Dirty Read', and execute the procedures right after each other)

1) Replace step 5 with...

procedure DoFirstQuery;
var
        LocateSuccess: Boolean;
begin
        try
                with tblTest do
                begin
                        Active := True;
                        LocateSuccess := Locate('ID', edtOnID.Text,
[]);
                        edit;
                        FieldByName('MYCOUNT').AsInteger :=
FieldByName('MYCOUNT').AsInteger - 1;
                        post;
                end;
        finally
                tblTest.Refresh;
        end;
end;

2) Replace step 6 with...

procedure DoSecondQuery;
var
        LookupResults: Variant;
begin
        try
                with tblTest do
                begin
                        Active := True;
                        LookupResults := Lookup('ID', edtOnID.Text,
'MYCOUNT');
                        if LookupResults = 0 then
                        begin
                                first;
                                while not eof do
                                begin
                                        if
FieldByName('LINK_ID').AsInteger = StrToInt(edtOnID.Text) then
                                        begin
                                                edit;

FieldByName('LINK_ID').Clear; //Sets to NULL
                                                post;
                                        end;
                                        next;
                                end;
                        end;
                end;
        finally
                tblTest.Refresh;
        end;
end;

Steven D'Abrosca
Retail Analytics Inc
r...@shore.net

 

Re:Transactions using Paradox tables-Record Lock Failed - Losing faith in BDE


Hi, Steve.  I am just a beginer at transaction processing in Delphi.  However
I think you should look at Tdatabase (on_line help) if you haven't already.  

I was studying this and it seems like you may need to set up a explicit
TDatabase component and set up multiple sessions using the Tsessionc
component - which will aloow you to set up multiple transactions using
Delphi's multi-threading capabilities.

There is a lot - a whole lot - of information under TDatabase and Tsession.

Thre is even more under the BDE Configuration help file and the Interbase
help file - sqlref32.hlp - and other such hlp files.  Let me know if this
helps.

Regards, -= Lou Rizzuto =-

Quote
Steven D'Abrosca wrote:

> Well, now that I've looked through the other transaction oriented
> problems with Delphi and Paradoxon this newsgroup, I'm beginning to
> think it just doesn't work right and can NOT be trusted!

> This is important, because I'm losing faith in the BDE.  Please
> somebody tell me if this is my fault or not.

> ONLY WHEN IN A TRANSACTION (TDatabase.Starttransaction), when
> performing two consecutive sql update statements, the second recieves
> an error, "Record Lock Failed".  If you reword the sql statement to do
> the same thing, but slightly differently, it works fine.  If you don't
> use sql, but use code, there are no problems.  THIS CAN BE DUPLICATED
> using Delphi's Database Explorer tool (as well as using a TQuery
> component in a Delphi app, which is my problem).

> Any explanation would help.  Thanks!

> Details...
> 1) I'm using Delphi 3.0 with BDE 4.x(the updated version, Build 5.83).
> 2) Create a paradox table (TESTTAB.DB), with a primary key (needed for
> transactions); See below for table structure and data
> 3) Create an alias that points to the directory with TESTTAB.DB
> 4) Start Database Explorer and set 'Options->Transaction Isolation' to
> 'Dirty Read'
> 5) Using Database Explorer, start a transaction on that alias
> 6) Enter/execute the following sql statement...
> "update TESTTAB set MYCOUNT = MYCOUNT - 1 where ID = 1"
> 7) Enter/execute the following sql statement...
> "update TESTTAB set LINK_ID = NULL where LINK_ID not in (select ID
> from TESTTAB where MYCOUNT > 0)
> 8) Commit the transaction.
> 9) All works fine.

> Now, go back to step 5, then 6, and replace 7 with...
> 7) Enter/execute the following sql statement...
> "update TESTTAB set LINK_ID = NULL where LINK_ID = 1 and LINK_ID not
> in (select ID from TESTTAB where MYCOUNT > 0)"

> There it is... "Record lock failed"

> Note: When using the first sql statement in code, I get an exception
> at address 00000000 when the table tries to do a refresh!

> DB Table (TESTTAB.DB)...
> Field Name | Type | Size | Key
> ------------------------------
> ID           I            *
> MYCOUNT      I
> LINK_ID      I

> Data...
> ID | MYCOUNT | LINK_ID
> 1   1          1
> 2   3          1
> 3   3          1

> FYI

> If you replace the sql statements with the following, which does the
> same thing, all works fine...
> (Make sure you're in a transaction, with the isolation level set to
> 'Dirty Read', and execute the procedures right after each other)

> 1) Replace step 5 with...

> procedure DoFirstQuery;
> var
>         LocateSuccess: Boolean;
> begin
>         try
>                 with tblTest do
>                 begin
>                         Active := True;
>                         LocateSuccess := Locate('ID', edtOnID.Text,
> []);
>                         edit;
>                         FieldByName('MYCOUNT').AsInteger :=
> FieldByName('MYCOUNT').AsInteger - 1;
>                         post;
>                 end;
>         finally
>                 tblTest.Refresh;
>         end;
> end;

> 2) Replace step 6 with...

> procedure DoSecondQuery;
> var
>         LookupResults: Variant;
> begin
>         try
>                 with tblTest do
>                 begin
>                         Active := True;
>                         LookupResults := Lookup('ID', edtOnID.Text,
> 'MYCOUNT');
>                         if LookupResults = 0 then
>                         begin
>                                 first;
>                                 while not eof do
>                                 begin
>                                         if
> FieldByName('LINK_ID').AsInteger = StrToInt(edtOnID.Text) then
>                                         begin
>                                                 edit;

> FieldByName('LINK_ID').Clear; //Sets to NULL
>                                                 post;
>                                         end;
>                                         next;
>                                 end;
>                         end;
>                 end;
>         finally
>                 tblTest.Refresh;
>         end;
> end;

> Steven D'Abrosca
> Retail Analytics Inc
> r...@shore.net

Other Threads