How do I read only COMMITTED transactions in SQL

c.kestas

I have a codeunit running under NAS that monitors the G/L Register table. Once a new register appears, the codeunit gathers the G/L entries under that register and sends them to another system via Message Queues.
The backend database is SQL Server 2K

The problem I have is that the codeunit reads uncommitted transactions. What I want is to read the records in the G/L register AFTER they have been committed, i.e. avoid dirty reads.

I read the on-line help on TRANSACTIONTYPE but I don't feel any wiser.
I don't want to change the posting routines if possible. Just somehow tell the codeunit to ignore uncommitted transactions.

Thanks

kriki

TRANSACTIONTYPE::Snapshot;

And after that statement you can read your database and be sure to have only COMMITED data. BUT YOU CANNOT MAKE CHANGES TO THE DB!
If you need to do that.
Best is after reading all, do a COMMIT and do your changes. Or if this is not possible : use TRANSACTIONTYPE::UPDATE; But this last can give problems with performance.

kine

Or you can use the command LOCKTABLE to request lock on readed records. In this case, if you try to read uncommited record, the process will wait till the lock is released (the record is commited or rolled back) or the timeout occures. Of course, this will create lock on the record after you read it, you need to do your process as short as possible...

c.kestas

Thanks for the quick reply.

I tried to simulate a large posting (one that would take say a minute to complete) and discovered that the NAS codeunit waits until the posting is committed. After some time it times-out and gererates an error of the form:
Table so..and..so cannot be changed because it is locked by another user.

This kind of error will cause the NAS service to stop.
Isn't there a way to simply ignore uncommitted transactions, instead of waiting until they are committed / rolled-back?

Please note that I am not making any changes to the DB. I just do a find on a table.

kriki

Find last record in "G/L Register" with "To Entry No." <> 0.
This should be the last commited entry no.

kine

the timeout can be disabled in c/al code. see timeout command .

Scott_Frappier

c.kestas:

I would consider moving the functionality outside to an ADO connection. Make it so that there is code that is like the following:

OnRun()
GLRegister.FIND('+');
IF IsGLRegisterCommitted(GLRegister) THEN
  MESSAGE('Entry %1 has been committed!',GLRegister."No.")
ELSE
  MESSAGE('Entry %1 has _not_ been committed!',GLRegister."No.");

IsGLRegisterCommitted(VAR GLRegister : Record "G/L Register") : Boolean
IF NOT ISCLEAR(ADOSQLConn) THEN
  CLEAR(ADOSQLConn);

IF CREATE(ADOSQLConn) THEN BEGIN

  DataSource := 'SRV02';
  InitialCatalog := 'Symbiant';

  ConnectionString := 'PROVIDER=SQLOLEDB.1;Persist Security Info=False;Trusted_Connection=Yes;Initial Catalog=' +
    InitialCatalog + ';' + 'Data Source=' + DataSource + ';';

  ADOSQLConn.ConnectionString := ConnectionString;
  
  ADOSQLConn.IsolationLevel := 4096; // Read Committed
  ADOSQLConn.CursorLocation := 3;

  ADOSQLConn.Open;
  IF (ADOSQLConn.Errors.Count = 0) AND (ADOSQLConn.State = 1) THEN BEGIN
    GLRegisterQuery := 'SELECT [No_] FROM [' + ConvertCompany(COMPANYNAME) + '$G_L Register] WHERE [No_] = ' +
      '''' + FORMAT(GLRegister."No.") + '''';

    IF NOT ISCLEAR(ADOGLRegRecSet) THEN
      CLEAR(ADOGLRegRecSet);

    CREATE(ADOGLRegRecSet);
    ADOGLRegRecSet.CursorType := 0;
    ADOGLRegRecSet.LockType := 1;

    ADOGLRegRecSet := ADOSQLConn.Execute(GLRegisterQuery);

    WHILE ADOGLRegRecSet.EOF = FALSE DO
      BEGIN
        ADOGLRegRecFields := ADOGLRegRecSet.Fields;
        ADOGLRegRecField := ADOGLRegRecFields.Item('No_');
        IF FORMAT(ADOGLRegRecField.Value) = FORMAT(GLRegister."No.") THEN
          EXIT(TRUE);
        ADOGLRegRecSet.MoveNext;
      END;
  END;
END;

EXIT(FALSE);

ConvertCompany(CompanyName : Text[80]) : Text[250]
InvalidCharacters := '."\/''';
EXIT(CONVERTSTR(CompanyName,InvalidCharacters,'_____'));

Basically, you would pass in the G/L Register that you are monitoring to verify that it is "Committed" to the database. If you have an open transaction that has not been committed, you should not have any records in the recordset (or a timeout will occur because the lock on the last record is prohibiting you from getting the record...which is an exclusive lock).

Somone with more experience with ADO may be able to streamline the commands...but I tested it and it works. The timeout issue (because of an exclusive lock) is the toughest part to get over...but if you're running NAS, it would cause the NAS Server to restart, which should be fine.

Another way that you may be able to get over this would be to get the record, issue a LOCKTABLE in C/AL code, retry to get the record, and if you cannot gain a lock on the register, then it potentially has not been committed to the database.

Good luck!

- Scott

c.kestas

GLRegister.SETRANGE("Submission Status", GLRegister."Submission Status"::" ");

  GLRegister_TimeOut := TRUE;
  COMMIT;
  CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Snapshot;
  SELECTLATESTVERSION;

  IF GLRegister.FIND('-') THEN BEGIN
     COMMIT;
     CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::UpdateNoLocks;
     GLRegister_TimeOut := FALSE;
    //Do the processing

Hi Scott,

Thanks for the tip.
I have however managed in the meantime to sort it out within C/AL.
The trick is to switch to Snapshot, try to access the record and then switch back to UpdateNoLocks.

My fear was that, if the register was taking too long to commit, the codeunit would error out, but it doesn't. It patiently waits until the posting is complete and the register unlocked.

Thanks again. I'll keep you suggestion in mind for future reference.

adakkj

Hi,
I have similar issue. I work with NAV2013 R2 and c.kestas’s solution dosen't work, maybe cause NAV version difference. I also use DotNet object way based on Scott Frappier’s solution and its work:)! But I wonder if really NAV 2013 R2 can't read only committed data without SQL and DotNet tricky way? I also won't wait for unlock table with report properties Transactiontype=Update.

My solution base on:
http://mibuso.com/blogs/ara3n/2011/01/1 ... tc-in-nav/

Objective to read only committed LOG data and send it to outer system by NAS report. I know that NAS will be the one process that modify this table, other processes only insert to LOG table.

ServerName := 'serverName';
NAVDB := 'SQLDataBaseName';

ConnectionString := 'Data Source='+ServerName+';'
+ 'Initial Catalog='+NAVDB+';'
+ 'Trusted_Connection=True;';

SQLConnection := SQLConnection.SqlConnection(ConnectionString);

SQLConnection.Open;
SQLCommand := SQLConnection.CreateCommand();
IntReadyNotyficationStatus:=WebPortalNotificationLOG.Status::"Notify Ready";
SQLCommand.CommandText := 'SET TRANSACTION ISOLATION LEVEL SNAPSHOT; select * From dbo.[PRAXIS SZKOLENIE$WebPortal Notification LOG] WHERE Status<>'+FORMAT(IntReadyNotyficationStatus);
SQLReader := SQLCommand.ExecuteReader;

WHILE SQLReader.Read() DO BEGIN
  WebPortalNotificationLOG.GET(SQLReader.GetInt32(1)); 
  message:=SendToOuterSystem(WebPortalNotificationLOG);
  WebPortalNotificationLOG."Return Message":=message;
  WebPortalNotificationLOG.Status:=WebPortalNotificationLOG.Status::"Notify Ready";
  WebPortalNotificationLOG.MODIFY;
END;

SQLConnection.Close;

CLEAR(SQLReader);
CLEAR(SQLCommand);
CLEAR(SQLConnection);
-----------------------------------
DotNet object

Name	DataType	Subtype	Length
SQLConnection	DotNet	System.Data.SqlClient.SqlConnection.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
SQLCommand	DotNet	System.Data.SqlClient.SqlCommand.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
SQLReader	DotNet	System.Data.SqlClient.SqlDataReader.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

rmv_RU

c.kestas wrote:

I have a codeunit running under NAS that monitors the G/L Register table. Once a new register appears, the codeunit gathers the G/L entries under that register and sends them to another system via Message Queues.
The backend database is SQL Server 2K

The problem I have is that the codeunit reads uncommitted transactions. What I want is to read the records in the G/L register AFTER they have been committed, i.e. avoid dirty reads.

I read the on-line help on TRANSACTIONTYPE but I don't feel any wiser.
I don't want to change the posting routines if possible. Just somehow tell the codeunit to ignore uncommitted transactions.

In my opinion it's bad way to ignore unposted transactions. In your case you can greatly decrease locking time by checking last record "To Entry No." field with dirty read and then locking record only if the value isn't zero.