Hi,
i have following questions:
When I use function dal.new.object(...) and dal.save.object(...) to create new records, other processes (e.g. ttaad4100) can show my new records. I have not jet committed the transaction with commit.transaction(). In my mind, on a FP3-System, another processes can only show the records after calling commit.transaction() (same like db.insert(...)).
Is this a new functionality in FP5 or is this a bug? Is this configurable?
Many thanks for help.
Joerg

Refer to the programmer's manual-

dal.save.object()
Syntax:
#include <bic_dam>

function long dal.save.object (string tbl.name [, long error.flag])

Description

Saves a record of the given table. In case a dal.new.object() or a dal.copy.object() was done, the record is inserted in the database. In case a dal.change.object() was done, the record is updated in the database.

In case DAL2 Field dependencies have been defined in the DAL, this function takes care that dependent fields will be triggered in the right order so that they can update themselves.

Note that this is done based on the values of the fields that have been set by calling dal.set.field()

Before the record is saved all (changed) fields will be validated.

The sequence of the actions is as follows:

Table level permission is checked.
For all fields set, the fieldname.make.valid() is executed, e.g. to perform rounding of data.
All dependent fields are triggered to update themselves.
Record level permission is checked.
The actual save (insert or update) is done.

Hi NPRao,
thanks for the fast answer. I know this text and I miss a statement to the visibility of data changes for other processes or users before a commit is calling. As a result, other users can already see data changes, if they are not yet committed. Independent from all DAL-functionality is this a huge difference between the dal.- and db.-functions.

Regards,
Joerg

Hi NPRao ,

he is talking about dirty read issue of database....

joerg,

i am totally novice in this area .. .. but i read somewhere that this is depend on DB..... that is some databases allow dirty ready and some don't or may they have an option on DB level to activate or deactivate this feature

Locking :

Database inconsistencies can arise when two or more processes attempt to update or delete the same record or table. Read inconsistencies can arise when changes made during a transaction are visible to other processes before the transaction has been completed – for example, the transaction might subsequently be abandoned. To avoid such inconsistencies, Infor ERP Enterprise supports the following locking mechanisms:

Record locking
Table locking
Application locking
Record locking
To ensure that only one process at a time can modify a record, the database driver locks the record when the first process attempts to modify it. Other processes cannot then update or delete the record until the lock has been released. However, they can still read the record.

While one process is updating a table, it is important that other processes retain read consistency on the table. Read consistency means that a process does not see uncommitted changes. Updates become visible to other processes only when the transaction has been commited. Some database systems do not support read consistency, and so a dirty read is possible. A dirty read occurs when one process updates a record and another process views the record before the modifications have been committed. If the modifications are rolled back, the information read by the second process becomes invalid.

Supported features
INFORMIX ORACLE DB2 SQL Server
locking row row row row
dirty read yes no
consistent read no yes
transactions yes yes yes yes

Delayed locks
Locking a record for longer than required can result in unnecessarily long waiting times. The use of delayed locks solves this problem to a great extent.

A delayed lock is applied to a record immediately before changes are committed to the database and not earlier. When the record is initially read, it is temporarily stored. Immediately before updating the database, the system reads the value of the record again, this time placing a lock on it. If the record is already locked, the system goes back to the Retry points and retries the transaction. If the record is not locked, the system compares the content of the record from the first read with the content from the second read. If changes have been made to the record by another process since the first read, the error EROWCHANGED is returned and the transaction is undone. If no changes have occurred, the update is committed to the database.

You place a delayed lock by adding the keyword FOR UPDATE to the SELECT statement (see Infor ERP Enterprise SQL). For example:

table tpctst999
db.retry.point()
SELECT pctst999.*
FROM pctst999 FOR UPDATE
SELECTDO
pctst999.dsca = "...."
....
db.update(tpctst999, DB.RETRY)
ENDSELECT
Table locks
Infor ERP Enterprise provides a table locking mechanism, which enables you to lock all the records in a specified table. A table lock prevents other processes from modifying or locking records in the table but not from reading them. This is useful when a particular transaction would otherwise require a large number of record locks. You use the db.lock.table() function to apply a table lock.

Application locks
An Application locks: overview prevents other applications and users from reading and/or modifying an application's data during critical operations. It is not part of a transaction and so is not automatically removed when a transaction is committed. Instead, an application lock is removed when the application ends or when appl.delete() is called.

Most of the modern Databases do not allow dirty read.

Here are some links on the topic for reference -

On Transaction Isolation Levels (http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html)

Oracle Dirty Reads (http://www.dba-oracle.com/t_oracle_dirty_reads.htm)

Does oracle allow the uncommitted read option? (http://stackoverflow.com/questions/208236/does-oracle-allow-the-uncommitted-read-option)

Isolation (database systems) (https://en.wikipedia.org/wiki/Isolation_%28database_systems%29)

SET TRANSACTION Syntax (https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html)

Hello,
many thanks for the interesting information and links.

I found this:
- Infor Enterprise Server Technical Reference Guide for SQL Server Database Driver (U8173K US) document (11.07.2013):

Isolation level
The MSQL driver uses the read uncommitted isolation level by default Multi-row read requests do not acquire any type of lock (shared or exclusive) unless explicitly stated in the query syntax. Queries such as INSERT, DELETE, and UPDATE acquire an exclusive lock implicitly. A SELECT WITH LOCK request acquires an update lock. Only in case of lookup references are shared locks acquired. The locks are retained until the transaction is committed or aborted.

- KB 1446649 (21.10.2013):
1) Is it possible to change MSQL driver isolation level from read uncommitted to read committed ?
No that is not possible. The driver takes care when locking is needed.
2) If yes, How is it done?
No it can't be done
3) Does it have any effect or risk that may cause malfunctions in the ERP?
The driver takes care when locking is needed for updates, insert,etc. so it wouldn't be possible to perform some transactions.

The big question:
Why use the db.-functions read committed and the dal.-functions read uncommitted. Is this configurable? Does anyone have an answer? It's always just one database driver active.

Many thanks for help and kind regard
Joerg

Hi,

I am wondering, why this is bothering? Is there any specific requirement where you do not want to show uncommited records in the table before commit?

exactly what i was thinking :)
Hi,

I am wondering, why this is bothering? Is there any specific requirement where you do not want to show uncommited records in the table before commit?

Hi Bhushan and benito,

on the one hand I need this functionality for implementing a variable check. This condition can store the user as normal 3gl-function. From this function a 3gl-program is generated and makes the test (similar to Exchange conditions). Running this 3gl-program represents a new process and it's nice that I can make the test before the commit, because several tables are affected.

On the other hand, this behavior requires an additional status field (e.g. "document completed added") for large structures (e.g. head and lines). Otherwise data would already select, that don’t have completed added.

We have now tested DAL1 on ERP5 with SQL-Server. The changes are not visible until after the commit was calling.

Conclusion
ERP5-SQL-Server, db./dal.-functions (dal1): Changes always visible only after commit
LN(FP5)-SQL-Server, db.-functions: Changes always visible only after commit
LN(FP5)-SQL-Server, dal.-functions (dal1/dal2): Changes visible before commit

Question: Is from FP5 (and higher) it, the changes via DAL for other processes / users are always visible before the commit?

kind regards
Joerg

Hi,

Based on the return value of dal.save.object() /db.insert() you can always know if the record is inserted or not. Also, a select statement for uncommitted record should work fine for testing. May be I am not able to figure out your requirement.

For the question you asked, its more dependent on the Porting Set rather than the Feature Pack. So, if you can provide the related porting set or TIV versions, that will help people figure out the answer.

Hi Bhushan,

maybe a short sample:

Process1----------------Process2
Case1
db.rery.point()
dal.new(...)------------at this time, Process2 can see the record from Process1
dal.new(...)------------at this time, Process2 can see the record from Process1
dal.new(...)------------at this time, Process2 can see the record from Process1
commit.transaction()

Case2
db.rery.point()
dal.new(...)------------at this time, Process2 can see the record from Process1
dal.new(...)------------at this time, Process2 can see the record from Process1
dal.new(...)------------at this time, Process2 can see the record from Process1
abort.transaction()-----at this time, the 3 records are deleted

Case3
db.rery.point()
db.insert(...)----------at this time, Process2 can not see the record from Process1
db.insert(...)----------at this time, Process2 can not see the record from Process1
db.insert(...)----------at this time, Process2 can not see the record from Process1
commit.transaction()----at this time, Process2 can see the records from Process1

Case4
db.rery.point()
dal.new(...)------------at this time, Process2 can not see the record from Process1
dal.new(...)------------at this time, Process2 can not see the record from Process1
dal.new(...)------------at this time, Process2 can not see the record from Process1
commit.transaction()----at this time, Process2 can see the records from Process1

The Process2 don't know the return codes from Process1 and this it's not relevant, Process2 knows nothing about Process1. The Process2 found only the records from Process1. Hope this sample helps to understand.

I only need to know, in which conditions Case1 resp. Case 4 is selected and why are db.- and dal.-functions treated differently (Case1 and Case3)? If it depends on the database/driver, why db.-functions use read committed and dal.-functions use read uncommitted? Only one database driver is active?

Okay, i send more system information.

Thanks for help and kind regards,
Joerg

Joerg,

Are process-1 and process-2 started by the same or different user?

If its the same, test the process-2 with a different user id.

From a database perspective, the database transaction isolation is at the user id level (for basics, there is more visibility as System DBA etc, much advanced topic).

Hi NPRao,

we have tested following cases:
- one user in one bshell login session
- one user in different bshell login sessions
- more users in different bshell login sessions

In all cases, the same result.

Kind regards,
Joerg