Options

How do I read only COMMITTED transactions in SQL

c.kestasc.kestas Member Posts: 15
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

Answers

  • Options
    krikikriki Member, Moderator Posts: 9,090
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    c.kestasc.kestas Member Posts: 15
    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.
  • Options
    krikikriki Member, Moderator Posts: 9,090
    Find last record in "G/L Register" with "To Entry No." <> 0.
    This should be the last commited entry no.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    kinekine Member Posts: 12,562
    the timeout can be disabled in c/al code. see timeout command .
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    Scott_FrappierScott_Frappier Member Posts: 90
    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
    Scott Frappier
    Vice President, Deployment Operations

    Symbiant Technologies, Inc.
    http://www.symbiantsolutions.com
  • Options
    c.kestasc.kestas Member Posts: 15
    
    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.
  • Options
    adakkjadakkj Member Posts: 1
    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'
    
  • Options
    rmv_RUrmv_RU Member Posts: 119
    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.
    Looking for part-time work.
    Nav, T-SQL.
Sign In or Register to comment.