Handling AF Table Write Conflicts in PI AF SDK: Dealing with Table Locking in ACE Applications

Working with AF Tables via the AF SDK is typically a straightforward process. However, when applications attempt to write to AF Tables and encounter locking issues—particularly in environments running ACE (Advanced Computing Engine) calculations—the experience can quickly become frustrating.

A common error encountered is:

Table '[TableName]' with UniqueID '[GUID]' is locked by user NT AUTHORITY\SYSTEM

This error usually indicates that your target AF Table has been checked out (locked) by another user or process, preventing writes until it is checked back in. Understanding the table locking mechanism and how to manage it in your PI System applications ensures robust and error-free AF Table operations.

Understanding AF Table Check-Out and Locks

AF Tables employ a check-in/check-out mechanism to maintain data integrity when multiple users or processes might modify table data simultaneously. When a table is checked out, it's locked for editing by others until the current holder checks it back in.

The NT AUTHORITY\SYSTEM lock often comes from a background process (like a service), but can also happen if your own application (especially ACE-based ones) does not check the table back in after making edits.

ACE Applications and AF Table Writes

ACE applications often run in a context where multiple calculations (contexts) could potentially access and modify the same AF Table. While your ACE code might seem single-threaded, ACE itself can manage several executions in parallel, increasing the likelihood of concurrent access issues.

Common Causes of AF Table Locking

1. Multiple threads or processes trying to modify the same table concurrently.
2. Not explicitly checking-in changes after modifying the table.
3. AF SDK objects are created and persist at module scope, increasing the window during which the table remains checked out.

Best Practices to Avoid AF Table Lock Conflicts

1. Always Check Table Check-Out Status

Before writing to an AF Table, verify if the table is already checked out. The AF SDK provides the CheckOutInfo property for this purpose:

' Check if table is checked out by this thread
If flow2EDatabase.Tables(WORKLIST_MESSAGES_TABLE).CheckOutInfo.IsCheckedOutThisThread Then
    ' Proceed with writing
Else
    ' Handle or wait until table is available
End If

2. Explicitly Check-In After Writing

After modifying the table, ensure you check in your changes:

' Assume you've already added the DataRow
table = flow2EDatabase.Tables(WORKLIST_MESSAGES_TABLE).Table()
'table.Rows.Add(dr) ' Your existing code
table.CommitChanges() ' Commit changes to AF Server
table.Parent.CheckIn("Updated by ACE application") ' Check table in to release lock

3. Limit the Lifetime of AF SDK Table Objects

Create AF SDK objects (like AFTable) only for as long as you need them within the calculation context. Avoid holding them open as module-level or static variables.

4. Design for Single Access or Implement Retrying

If possible, architect your application so only one context or thread writes to a table at any given time. Alternatively, implement logic that retries the write operation after a brief delay if a lock is detected.

5. Monitor and Handle Exceptions Gracefully

Handle exceptions thrown due to locking, and provide informative logging so you can trace issues when locks do occur.

Try
    ' Attempt table write and check-in
Catch ex As Exception
    ' Log error message and details
End Try

Summary

AF Table write locks in ACE and other PI System environments are not uncommon, but they are manageable with a clear strategy:

• Always check the checkout status.
• Check-in after writing.
• Keep AF SDK object lifetimes short.
• Avoid sharing SDK objects across contexts.
• Gracefully handle and log errors.

By following these best practices, your AF Table operations via AF SDK will be robust—even in complex, parallel-processing environments like those driven by ACE.

---

References:

• PI AF SDK Documentation
• OSIsoft Community: Handling AFTable Locks

Feel free to share your own experiences or questions in the comments below!