Hi friends,

I need to know if exists any best practice when I have to use the commit.transaction() instruction, As far as if I know open severals "for update" when I use commit.transaction() all "for update" are closed, but sometimes this isn't work fine for me, becuase I need to do it for each table.

The solution is sending commit.transation in the end, but is it correct?

If I process a lot information and only at the end send commit exists any problem?

what should I do in this case?

Thank's

Refer to the latest design principles document for more info (you get it with dfe installation) and search with keywords 'Transaction Management' and 'commit.transaction'.

Size of database transactions
Problem

For optimal performance, database transactions must not be too small and not too large. However they must always be logical transactions.
Large transactions will also have relatively long locking times. Depending on the frequency of updates done by other users, this can cause locking problems.
Other problems can also occur, for example, run-time tools (bshell, driver, and so on) consume more memory and CPU, more rollback segments are needed in the RDBMS.

Transaction management
It is recommended that transaction management is in the 4GL GUI script. The reasons are:
Printing and raising questions relates very much with transaction management.
For customization, possibly only the UI / 4GL script must be changed and not the lower layers (DLLs, DALs, INTDLLs). Commit.transaction() in the generic functions of lower layers will disturb transaction management.
Foreign applications define logical transactions in the foreign application, which may not be disturbed by commit.transaction() in a lower layer.

Database handling
A good Functional Design and a good Data model are prerequisites for error free code.
Some ideas to think about:
Are relations normalized ?
Is the naming of tables and table fields logical ?
Is the naming of tables and table fields in line with other modules, packages (e.g. a table should be named Purchase Parameters instead of Purchase Configuration or Purchase Setup)
Is the primary key really unique (a UTC date, time domain is not unique even when expressed in milliseconds).
Transaction management (database) must be correctly implemented, this means that:
You must know that Transaction management is typically performed by the standard program (BaanERP: 4GL Engine) (Session type maintain) or in the GUI script (Session Type print&script type 4, update&script type 4), and where the standard program defines the retry.point and places the commit, and when the programmer must handle it, based on its environment, performance and so on.
You must know what a logical transaction is, and what can be separated. For example, if a function returns goods to a warehouse and closes a project, the return of goods may be successful and closing is allowed to fail. In this case, apparently two logical transactions exist.
Code works with retries, think of variable values, printing to reports, and so on.
User interaction within database transactions is forbidden. Also zooms, suspends, or process communication (in general scheduling caused functions) are not allowed due to the bshell CPU bonus ticks functionality.
Wildcards in select lists should be prevented as much as possible.
Technically it is possible to use db statements (e.g. db.update instead of dal.update) in BaanERP , however if the session relates to a DAL with checks and related actions they are not performed. So db.statements can be used when:
No DAL is present for that session
Performance is at stake and you are absolutely sure that no checks or related actions exist and will ever exist on the fields you update e.g. a description.
Recursion could take place
Records of the same table are with checks interconnected
When you neglect the rules the effects are huge, table relations can be missing (e.g. no warehouse orders created for a production order), illegal input in the database etc.
It is not allowed to use db.next/gt etc in BaanERP. The use of SQL is mandatory, because SQL is more powerful, offers better readability, gives better performance for level 2 driver which results in less calls in Baan software.
Since database records are often stored in global variables, a select within a select may corrupt the values of the first one. You can work around this, using the following order of preferred solutions:
Binding/aliasing, change the function
On main table, change the calling
Save the record pointer, change the calling
Set oriented, using function sum()