Transactions in datasnap

[Joao Cadilhe]

Can you explain me how Unidac transactions works with datasnap? I can't find enough information in Unidac Help.

Thanks in advance.

Joao Cadilhe.

[Plash]

UniDAC transactions work with TDataSetProvider and TClientDataSet components in the same way as when you use TUniQuery directly.
When you open a query with InterBase/Firebird the transaction is started if it is not already active.

When you execute an update statement with InterBase/Firebird the transaction is also started if it was not active. After execute UniDAC commits transaction if it was started automatically before execute (UniDAC calls CommitRetaining instead of Commit if the transaction is used by another opened or executing query.). UniDAC does not commit the transaction automatically if it was started manually (if you call StartTransaction method).

[Joao Cadilhe]

I did a Datasnap test application with Unidac and IBX to test transactions with Firebird 2.1 database.

Database has one table:

CREATE TABLE TEST (
TEST_ID INTEGER NOT NULL,
TEST_NAME VARCHAR(30) NOT NULL
);

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);

I'm using IbExpert Professional Database Statistics (Under Services Menu) for database transactions monitoring. Each Database Statistics refresh uses five transactions.

I had these results:

IBX TESTS:

Transactions before open test application:
Oldest transaction 25234
Oldest active 25235
Oldest snapshot 25235
Next transaction 25236
(Next - Oldest active = 1)

Transactions after open test application:
Oldest transaction 25242
Oldest active 25243
Oldest snapshot 25243
Next transaction 25244
(Next - Oldest active = 1)

Transactions after insert routine (insert 1000 records):
Oldest transaction 26248
Oldest active 26249
Oldest snapshot 26249
Next transaction 26250
(Next - Oldest active = 1)

Transactions after update routine (update 1000 records):
Oldest transaction 27255
Oldest active 27256
Oldest snapshot 27256
Next transaction 27257
(Next - Oldest active = 1)

Transactions after delete routine (delete 1000 records):
Oldest transaction 28261
Oldest active 28262
Oldest snapshot 28262
Next transaction 28263
(Next - Oldest active = 1)


UNIDAC TESTS:

Transactions before open test application:
Oldest transaction 28267
Oldest active 28268
Oldest snapshot 28268
Next transaction 28269
(Next - Oldest active = 1)


Transactions after open test application:
Oldest transaction 28275
Oldest active 28276
Oldest snapshot 28276
Next transaction 28277
(Next - Oldest active = 1)

Transactions after insert routine (insert 1000 records):
Oldest transaction 28276
Oldest active 28277
Oldest snapshot 28277
Next transaction 29283
(Next - Oldest active = 6)

Transactions after update routine (update 1000 records):
Oldest transaction 29282
Oldest active 29283
Oldest snapshot 28277
Next transaction 30289
(Next - Oldest active = 1006)???????

Transactions after delete routine (delete 1000 records):
Oldest transaction 30288
Oldest active 30289
Oldest snapshot 28277
Next transaction 31295
(Next - Oldest active = 1006) ??????

IBX or FIBPLUS works very well with Datasnap, but as you can see in the tests, number of opened (uncommited) transactions increases very fast whith UniDac and i think it's very dangerous for Firebird Servers.

Usually IBX and FibPlus with Datasnap opens transactions and commits then (hard commit) for every Select, Insert, Update or Delete operations (no active (uncommited) left in aplication).

I see, in my test application, at least, 3 transactions always active with Uniconnection.ActiveTransactions property.

I ordered Unidac, but i can't use it in production, because of these transactions problems. Can you Help me?

PS: Sorry for the long post.

[Plash]

Next transaction' in Statistics increases every time as CommitRetaining is called. When you call ApplyUpdates after changing 1000 records, UniDAC executes an update statement and calls CommitRetaining for each record. So 'Next transaction' in Statistics increases but number of active transactions remains same.

In my test application the transaction starts when I open TClientDataSet component. When I call ApplyUpdates, CommitRetaining is called for each record. After this the difference 'Next transaction' - 'Oldest Active' = 1000. But there is only one active transaction all time while application works.

You can control the transaction manually by calling StartTransaction and Commit methods. In this case CommitRetaining is not called, and Statistics looks fine.

Code: Select all

  UniConnection.StartTransaction;
  ClientDataSet.Open;
  UniConnection.Commit;

  UniConnection.StartTransaction;
  ClientDataSet.ApplyUpdates(-1);
  UniConnection.Commit;

[Joao Cadilhe]

Thank you Plash for your time.

The point is "UniDAC executes an update statement and calls CommitRetaining for each record". My applications are 3 tier, not client server (Uniconnection is in application server, clientdatasets in client application). I don't think manual transaction control in this case is a good idea. I think that the solution is let Datasetprovider control transactions as usually it does like in IBX or FibPlus case.

From Bill Todd's "Building Applications With ClientDataSet and InterBase Express" (12th Annual Borland Developer's Conference):

"From this very basic explanation you can see the advantages of this architecture. First, transactions are very short lived. When you open a ClientDataSet that is connected to a DataSetProvider the provider in tern opens the dataset component it is connected to. When opened the dataset component executes its SQL SELECT statement and returns the records to the provider which then sends the records to the ClientDataSet and closes the dataset and commits the read transaction. When you call the ClientDataSet's ApplyUpdates method the changes are sent to the provider which generates the necessary SQL statements, starts a transaction, sends the SQL statements to the database server and commits the transaction. This makes both the read and update transactions very short which means that other users are not blocked by record locks held by long running update transactions. Short transactions also means that the number of transactions open at any one time will be smaller and, therefore, the load on the database server will be less."

You can see complete article in: http://www.dbginc.com/tech_pprs/ibxcds.html

[Joao Cadilhe]

I tried to use BeforeApplyUpdates and AfterApplyUpdates events of Datasetprovider to start/commit transaction but the test results remains the same.
I ordered Unidac without source code.
Do you think is very difficult to change Unidac iprovider sources to let Datasetprovider controls transactions (like i exposed in last post)?

I think that if this problem is solved Unidac will be perfect (with PostgreSQL support in the near future) to me.

Thanks in advance.

Joao Cadilhe.

[Plash]

With the current version of UniDAC you need to use separate transactions for reading and updating data if you want short living transactions.
Place two TUniTransaction components on the form or data module. First component will be the read transaction. Set its ReadOnly property to True. Then assign this transaction to the Transaction property of all TUniQuery, TUniTable, etc. components.
Second TUniTransaction component will be the update transaction. Assign this transaction to the UpdateTransaction property of all UniDAC components.

When you open first query in the application, the read transaction is started. It remains active until you close connection. But it is not bad because this transaction is read-only.

When you call ApplyUpdates the update transaction is started, update SQL statements are executed for each record, and then the update transaction is commited. This works exactly as IBX.


We'll consider a possibility to add the feature of closing transaction after a dataset is opened like IBX does. In this case you will not need to have separate transactions for reading and writing. Maybe I can say you what changes in UniDAC sources you should make to achieve this behaviour.

[Joao Cadilhe]

"When you call ApplyUpdates the update transaction is started, update SQL statements are executed for each record, and then the update transaction is commited. This works exactly as IBX."

OK! I did tests again using separate transactions and it's working fine.

"We'll consider a possibility to add the feature of closing transaction after a dataset is opened like IBX does"

It's not necesssary. Unidac works very well with separate transactions in all tests I have done.

Thank you very much, Plash.

Joao Cadilhe.

[Joao Cadilhe]

I found a problem:

I have two datamodules in my server application.

Case 1:
UniConnection and InterbaseProvider in first DataModule and Uniquery with read and update transactions in the second datamodule (using Uniconnection of the first). In this case opened transactions increase on firebird server.
I see with dbmonitor that commit retaining is called.

Case 2:
UniConnection, InterbaseProvider,read and update transactions in first DataModule and Uniquery in the second datamodule using read and update transactions of the first datamodule.
In this case transactions are OK.
I see with dbmonitor that commit is called.

1 - I need to know if i can use the update and read transactions of the first datamodule with all uniquery in the second one.

2 - I think that Unidac, with Datasnap, could always use commit instead of commit retaining, since Uniquery, in this case is always unidirectional and data cache is in the clientdaset. Do you think it's possible in future Unidac release?

Thanks.

Joao Cadilhe.

[Plash]

We have found the problem in UniDAC that the read transaction is used instead of the update transaction when TClientDataSet.ApplyUpdates is called. We'll fix this problem in the next build of UniDAC.

1. You can use the transactions from the first datamodule in all other datamodules and forms. There is no sence to create separate transactions in each datamodule.

2. UniDAC calls CommitRetaining instead of Commit if the transaction is already active when you call ApplyUpdates. Probably it is caused by the bug that I have described above.

[Joao Cadilhe]

Thank you, Plash.

Joao Cadilhe.

[Challenger]

We have released UniDAC 2 Beta with PostgreSQL support. We are looking forward to your comments and suggestions.