SQL Query VI Fetch Mode Change in LabVIEW 2022 SP1: Transaction Isolation Implications

Question

When using the SQL Query VI in LabVIEW 2022 SP1 with Fetch Mode set to 'Stream As Needed', the VI returns data in configurable chunks while keeping the ODBC cursor open until the VI is closed or the Maximum Rows limit is reached.
The goal is to determine how this streaming behavior interacts with transaction isolation levels on a non‑autocommit connection, specifically whether the read lock is held for the entire cursor lifetime or released after each chunk, and what impact this has on concurrent update transactions. What lock duration does the VI impose when Stream As Needed is active? Does adjusting Chunk Size or enabling autocommit alter the lock scope? Are there documented limits or recommended settings to avoid blocking concurrent updates while still benefiting from chunked retrieval?

Tasadduq Burney · Answer

In LabVIEW 2022 SP1, the SQL Query VI's 'Stream As Needed' mode maintains an active ODBC cursor to deliver data in chunks. The duration of database locks is governed by the database engine's transaction isolation level rather than the LabVIEW VI's internal chunking logic.

Lock Duration and Isolation Scope
When using a non-autocommit connection, the lock is typically held for the entire lifetime of the cursor. Because 'Stream As Needed' keeps the cursor open to fetch the next set of data:

Repeatable Read / Serializable: Read locks are generally held until the transaction is committed or the cursor is fully exhausted. This will block concurrent update transactions on the affected rows.
  Read Committed: Read locks may be released after each chunk is read, depending on the specific ODBC driver and RDBMS implementation, but the cursor remains active, which can still overhead.

Impact of Configuration Settings
Adjusting the Chunk Size affects the amount of data retrieved per network round-trip and local memory allocation, but it does not alter the underlying lock scope of the transaction.
Enabling Autocommit significantly changes the behavior; the database will commit the transaction immediately after the statement execution. This often releases locks much sooner but may prevent the ability to perform atomic operations across multiple SQL VI calls.

Recommended Settings for Concurrency
To avoid blocking concurrent updates while utilizing chunked retrieval, consider these strategies:

Use Snapshot Isolation: If supported by your database (e.g., SQL Server), use snapshot isolation to allow reads to not block writes.
  Fetch All Mode: If the dataset is small enough, use 'Fetch All' to retrieve all data into memory at once, allowing the cursor to close and locks to release immediately.
  Explicit Closing: Ensure the SQL Query VI is closed or the transaction is committed as soon as data processing is complete to free server-side resources.

Note: Specific locking behavior varies based on the ODBC driver and the underlying RDBMS (SQL Server, PostgreSQL, MySQL, etc.).

SQL Query VI Fetch Mode Change in LabVIEW 2022 SP1: Transaction Isolation Implications

1 answer

Lock Duration and Isolation Scope

Impact of Configuration Settings

Recommended Settings for Concurrency

Your perspective belongs here.

1 question comment