Page 1 of 2
How to work with transactions
Posted: Sat 29 Aug 2009 11:09
by rfwoolf
Hi guys
I haven't tested this on all tables but it's happening in one case...
If I run two clients, on client 1 I add a record, client 2 won't see it.
So I shut down client 1, and suddenly client 2 can see it.
I am sure this has something to do with transactions. In my application I haven't done any 'transactions'.
Is there a way to get my app to post transactions automatically?
How do I manually star, close, and roll back a transactions?
In other words, how are transactions handled using your components?
What can I do to fix this?
Here are my settings:
On my TIBCConnection, in Object inspector I open 'DefaultTransaction',
set DefaultCloseAction to 'taCommit',
IsolationLevel is iblReadCommitted
Posted: Mon 31 Aug 2009 09:00
by Plash
Check the AutoCommit property of TIBCConnection and TIBCTable. The both properties should be set to True. In this case the second client will see the record that you have added in the first client.
You can manually manage transactions using the StartTransaction, Rollback, Commit, RollbackRetaining, CommitRetaining methods of the TIBCConnection or TIBCTransaction component. If the transaction is not active, it is started automatically when you open or execute a query. If AutoCommit is enabled, the changes are always commited automatically after query execution. If you start transaction manually, IBDAC commits changes using CommitRetaining to keep the transaction active.
Posted: Mon 31 Aug 2009 09:08
by rfwoolf
Thank you for your response, I have been waiting this whole weekend
In my TIBCConnection Autocommit has always been True (by default) and all my design-time datasets have Autocommit set to True (by default), but during my tests the problem persisted, but, in some cases my TIBCQueries are craeted at Runtime, for examlpe
Code: Select all
var
aQuery : TIBCQuery;
begin
aQuery := TIBCQuery.create(self);
aQuery.Connection := IBCConnection1;
aquery.SQL.Text := 'WHATEVER';
aquery.execsql;
aQuery.free;
end;
Could it be that I have to also set Autocommit to try, e.g.
aQuery.Autocommit := true;
?? But surely this is true by default?
I will run another test to confirm.
Posted: Mon 31 Aug 2009 09:48
by rfwoolf
i tried again and the problem persists,
I then made sure that all my run-time queries have Autocommit set explicitly to True.
..and the problem persists.
Posted: Mon 31 Aug 2009 11:03
by rfwoolf
Is there a way to do a showmessage('Transaction open') and showmesasge('Transaction Closed') do debug what is happening with the transactions?
...sigh I haven't been able to work on this since Friday because of this problem. Please help
Posted: Mon 31 Aug 2009 11:17
by rfwoolf
Posted: Mon 31 Aug 2009 14:34
by rfwoolf
I am so frustrated... I keep on testing and tying to open and close transactions
Posted: Mon 31 Aug 2009 15:48
by rfwoolf
Sigh...
Posted: Mon 31 Aug 2009 19:05
by rfwoolf
listening
Posted: Mon 31 Aug 2009 19:09
by rfwoolf
Now I have this problem...
---------------------------
lock conflict on no wait transaction
deadlock
update conflicts with concurrent update
concurrent transaction number is 3483
At procedure 'reserve_seat' line: 55, col: 2.
---------------------------
OK
---------------------------
I need help!!!!
Posted: Tue 01 Sep 2009 08:23
by Plash
You should almost always use iblReadCommited or iblReadOnlyReadCommited. Other isolation levels are used in rare cases.
IBDAC has documentation. If you are using Delphi 2005 or higher, the IBDAC help is embedded to the IDE help. You can select "InterBase | IBDAC help" or "Help | RAD Studio Help" to see the IBDAC help.
I think, your problem is not related to the isolation level of transaction. Please create a small sample that demonstrates the problem, and send it to support*devart*com. We will check it.
Posted: Tue 01 Sep 2009 08:48
by rfwoolf
Is Delphi BDS 2006 okay? Or must I strictly send the sourcecode?
Posted: Tue 01 Sep 2009 09:49
by uko
rfwoolf wrote:Now I have this problem...
---------------------------
lock conflict on no wait transaction
deadlock
update conflicts with concurrent update
concurrent transaction number is 3483
At procedure 'reserve_seat' line: 55, col: 2.
---------------------------
OK
---------------------------
I need help!!!!
Without seeing any source code, everyone can only guess what is going on. For your last problem: two transactions trying to update the same table and are waiting for each other to be committed.
For your original problem: have you inserted the new record by a grid? Then make sure that at least the new data get posted by the control (change to a new record for example should do the trick)
[Note: posting committing! Only when auto commiting is active]
I'm not sure how much experience you have with databases and transactions. It will be helpful if you read a little bit about it and then work through DevArts demos. If database programming is new to you, then don't use a complex application with stored procedures, triggers first but try to get the knowledge by small sample applications.
best regards,
Ulrich
Posted: Tue 01 Sep 2009 10:08
by rfwoolf
Thanks for your reply uko
The "lock conflict" problem seems to appear if I specifically ask it to commit or commitretaining with autocommit TRUE on everything.
But if I don't ask it to commit then I don't get an error but the transaction problem persists.
To answer your question, the insert is being done with an INSERT statement with a TIBCQuery created at runtime.
I will attach the sourcecode for you.
Then the fetch statement is done using a SELECT statement.
The fetch statement cannot see the new record inserted by the INSERT statement, unless I terminate the application.
INSERT STATEMENT
Code: Select all
aQuery := TIBCQuery.Create(self);
aQuery.connection := FrmMainMenu.ZConnectRemote;
aQuery.Autocommit := true;
aQuery.Close;
aQuery.SQL.Clear;
aQuery.SQL.Add('INSERT INTO "TblBookings" ("ID", "FirstName", "LastName", ' +
'"Age", "Gender", "TotalAmount", "Discount", "AmountPaid", "SalesPerson", "TimeStampOpened", ' +
'"TimeStampUpdated", "Status", "ContactTel", "ContactMobile", "PassengerNotes", "IsASpecialCarePerson") VALUES (' +
EdtBookingID.Text + ',' + //id
QuotedStr(EdtFirstName.Text) + ',' + //FirstName
QuotedStr(EdtLastName.Text) + ',' + //LastName
EdtAge.Text + ',' + //Age
QuotedStr(Gender) + ',' + //Gender
CurrToStr(EdtTotalFees.AsCurrency) + ',' + //TotalAmount
CurrToStr(EdtDiscount.ascurrency) + ',' + //Discount
CurrToStr(EdtAmountPaid.ascurrency) + ',' + //AmountPaid
Quotedstr(EdtBookingAgent.Text) + ',' + //SalesPerson
'current_time' + ',' + //TimeStampOpened
'null' + ',' + //TimeStampUpdated
Quotedstr('Confirmed') + ',' + //Status
QuotedStr(EdtContactTel.Text) + ',' + //ContactTel
QuotedStr(EdtContactMobile.Text) + ',' + //ContactMobile
QuotedStr(EdtPassengerNotes.Text) + ',' + //PassengerNotes
QuotedStr(IsASpecialCarePerson) + ')'); //IsaSpecialCarePerson
aQuery.ExecSQL;
SELECT STATEMENT
Code: Select all
procedure TFrmBookingsTable.JvWizardInteriorPage1NextButtonClick(
Sender: TObject; var Stop: Boolean);
begin
btnvalidatesearchcriteria.click;
with QryBookings do
begin
// QryBookings.connection.Transactions[0].Commit;
close;
SQL.Clear;
SQL.Add('SELECT');
SQL.Add('"TblBookings"."ID",');
SQL.Add('"TblBookings"."Status",');
SQL.Add('"TblBookings"."FirstName",');
SQL.Add('"TblBookings"."LastName",');
SQL.Add('"TblBookings"."Age",');
SQL.Add('"TblBookings"."TotalAmount",');
SQL.Add('"TblBookings"."AmountPaid",');
SQL.Add('"TblFlightBookings"."ID" as "FlightBookingID",');
SQL.Add('"TblFlightBookings"."Class",');
SQL.Add('"TblFlightBookings"."FlightID",');
SQL.Add('"TblFlightBookings"."KindCode",');
SQL.Add('"TblFlightSchedule"."Origin",');
SQL.Add('"TblFlightSchedule"."Destination"');
SQL.Add(' FROM "TblBookings"');
SQL.Add(' JOIN "TblFlightBookings" ON "TblFlightBookings"."BookingID" = "TblBookings"."ID"');
SQL.Add(' JOIN "TblFlightSchedule" on "TblFlightBookings"."FlightID" = "TblFlightSchedule"."ID"');
//we use a stupid WHERE statement so that all further statements can start with 'AND
SQL.Add(' WHERE "TblBookings"."ID" 0 ');
if CheckBookingID.Checked = true then
SQL.Add(' AND "BookingID" = ' + EdtBookingID.Text);
if CheckFlightID.Checked = true then
SQL.Add(' AND "FlightID" = ' + EdtBookingID.Text);
if CheckOrigin.Checked = true then
SQL.Add(' AND "Origin" LIKE ''%' + EdtOrigin.Text + '%''');
if CheckDestination.Checked = true then
SQL.Add(' AND "Destination" LIKE ''%' + EdtDestination.Text + '%''');
if CheckFirstName.Checked = true then
SQL.Add(' AND "FirstName" LIKE ''%' + EdtFirstName.Text + '%''');
if CheckLastName.Checked = true then
SQL.Add(' AND "LastName" LIKE ''%' + EdtLastName.Text + '%''');
open;
if Recordcount <= 0 then
FrmMainMenu.ShowAMessage('Sorry. No bookings returned using your search critera.', 'Information');
end;
end;
Posted: Tue 01 Sep 2009 11:02
by uko
Ok,
Are both queries running in the same transaction? From the code you posted I would say yes and both using the default transaction. Is this right? When the second query is executed: are you really sure that the first insert query has been executed before?
And how do you know, that the second statement doesn't see the new record? What happens when you execute a simple query only on TblBooking (without joins on other tables and where clauses). Can you see the record in this case? I'm asking because the second query contains joins on IDs. Are all those IDs valid? (when the first query creates a new record, how and when are the corresponding IDs in TblFlightBookings and TblFlightSchedule created?)