Recent

Author Topic: IBX and Resources  (Read 3882 times)

RedOctober

  • Sr. Member
  • ****
  • Posts: 450
IBX and Resources
« on: May 09, 2018, 06:20:33 am »
Platform:  Lazarus 1.8.2, FPC 3.0.4, IBX 2.3.0

Questions about IBX:

- When a TIBTransaction is opened, is it locking other users out of selecting, or updating the records it's connected TIBQuery and TIBDatasets have pulled back from the database using their SQL statements?

- If so, doesn't this use up lock resources on the Firebird server for each user that has an open transaction, which are apparently limited, meaning that if enough users simply query the database, it could crash, even if no user is doing an update?

This means that TIBTransactions are stateful, and there is a definite limit to how many users can be connected to the database at any given time.

The best way for client server setups to work is stateless.  Where thousands of users can query the DB and get the little record subset they need, make any changes to a single record, then update the database with those changes.

Is there a stateless way to use TIBX components directly, or must I use TIBX components to query the back end database, then programmatically transfer all the field data to a TBufDataSet, then do the reverse for updates?

Is there a way to "connect" a TBufDataSet to the database so it can receive the results of a query and not involve having an open transaction waiting for the user to make changes, then update?






tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: IBX and Resources
« Reply #1 on: May 13, 2018, 12:21:51 am »
I believe that this is a Firebird question rather than an IBX question. A TIBTransaction is just a wrapper for Firebird's transaction management API.

Firebird's transaction isolation mechanism is there to allow multiple users to view and update data with minimal conflicts and to ensure that each sees a consistent view on their data. Locks only come into being when data is updated and then depend on the transaction parameters. For these see the Firebird documentation.

A common set of transaction parameters allows for a "Read Committed" isolation. That is when you run a select query you see not just the state of the database when you started your transaction (plus your updates) but also any committed changes made by other users. On the other hand, you can also have snapshot isolation where you can't see any changes made since the start of a transaction.

Locks come into place when you update data. When you update a record, it is now locked for changes by other transactions until you have committed the transaction. if another transaction tries to update a locked record, it either waits synchronously for the transaction holding the lock to commit, or returned with a lock wait error. Transaction parameters determine which outcome is selected.

You should still be able to read a locked record - however, you do not see the change until the change is committed.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: IBX and Resources
« Reply #2 on: May 13, 2018, 07:51:01 am »
Is there a stateless way to use TIBX components directly, or must I use TIBX components to query the back end database, then programmatically transfer all the field data to a TBufDataSet, then do the reverse for updates?

Is there a way to "connect" a TBufDataSet to the database so it can receive the results of a query and not involve having an open transaction waiting for the user to make changes, then update?

Are you talking about this?
"TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible immediately, but they are not sent to the database server. What does happen is that the changes are maintained in a change log. When the '.ApplyUpdates' method is called, then all changes in the change log are sent to the database. Only then will database server know of the changes. The changes are sent to the server within a transaction of TSQLTransaction. Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the database server."
http://wiki.freepascal.org/SqlDBHowto

Code: [Select]
Program EditData;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
    db, sqldb, sqlite3conn;
var
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query : TSQLQuery;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.DatabaseName := 'test_dbase';
  Query := TSQLQuery.Create(nil);
  Query.DataBase := AConnection;
  Query.SQL.Text := 'select * from tblNames Order By ID';
  Query.Open;

  Query.First;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Name Number 1';
  Query.Post;

  Query.Next;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Name Number 2';
  Query.Post;

  Query.Next;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Name Number 3';
  Query.Post;

  Query.UpdateMode := upWhereAll;         // defined in db
  Query.ApplyUpdates;
  ATransaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

RedOctober

  • Sr. Member
  • ****
  • Posts: 450
Re: IBX and Resources
« Reply #3 on: May 13, 2018, 03:58:18 pm »
Hi Valdir.

Short answer: Yes!

I'm using IBX components at the moment because they have more details available in the TIBDatabase component.  My understanding is that I cannot use TSQL* and TIBX* components in the same form, because of unit naming conflicts.  Also, my understanding is that I can only connect a TSQLQuery to a TIBConnection (from the same component pallete) and not to a TIBDatabase from the IBX pallete.  Is my understanding on both accounts correct?

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: IBX and Resources
« Reply #4 on: May 15, 2018, 02:07:17 pm »
I'm using IBX components at the moment because they have more details available in the TIBDatabase component.  My understanding is that I cannot use TSQL* and TIBX* components in the same form, because of unit naming conflicts.  Also, my understanding is that I can only connect a TSQLQuery to a TIBConnection (from the same component pallete) and not to a TIBDatabase from the IBX pallete.  Is my understanding on both accounts correct?
Yes, you should not mix IBX and SQLDB in the the same project.

 

TinyPortal © 2005-2018