V2 SQL Driver settings
proiv dashboard
|
V2 SQL Driver settings |
proiv dashboard |
|
The V2 driver is an optional, updated SQL Server driver for PROIV. It was introduced because Microsoft is deprecating support for SQL cursors. V2 uses key-based updates instead of SQL cursors, ensuring continued compatibility with current and future versions of SQL Server.
How Does V2 Differ from V1?
|
Feature |
V1 Driver (Default) |
V2 Driver (Optional) |
|
Update Mechanism |
SQL Cursor |
WHERE clause with key values |
|
Row Targeting |
Always precise |
Depends on key uniqueness |
|
Locking Model |
Pessimistic only |
Optimistic or Pessimistic (user choice) |
|
Concurrency Handling |
Serializes updates |
May allow parallel or conflicting writes (only with optimistic locking) |
|
Risk of Multi-Row Update |
Low |
Higher if key is non-unique |
V1 (Default): Uses SQL cursors and always operates in pessimistic locking mode. This means only one user can update a row at a time; others are blocked until the lock is released
V2 (Optional): Can operate in either optimistic or pessimistic mode, depending on your settings in the PROIV Dashboard. With locking hints enabled, V2 behaves like V1 (pessimistic). With hints disabled, V2 uses optimistic locking.
How it works: Rows are locked as soon as they are read for update. Only one user can update a row at a time; others are blocked until the transaction is complete.
Benefits: Prevents conflicting changes and ensures data integrity.
Drawbacks: May cause more blocking and slower performance in high-concurrency environments.
V1: Always uses pessimistic locking.
V2: Behaves like V1 when locking hints are enabled.
How it works: Multiple users can read and attempt to update the same data at the same time. Updates are not locked in advance; if two users try to update the same row, the last one to commit will succeed, and the other may fail or overwrite changes.
Benefits: Can improve performance and allow more parallel updates.
Drawbacks: Increases the risk of conflicting updates or lost changes, especially in products not specifically designed for optimistic locking. Data integrity is not always guaranteed.
V2: You can choose this mode by disabling locking hints in the Dashboard.
Important:
V1 and V2 (with pessimistic locking enabled) are designed to be functionally equivalent. Both provide the same level of safety and data integrity. Switching to V2 is optional for now, but may become the default in future release. For now, V1 is still fully supported and still the default driver.
Optimistic locking (V2 with locking hints disabled) is available as a choice for users who want greater concurrency and potential performance benefits, but it comes with different risks and is not the default.
Locking Hints and Isolation Levels
Enable Locking Hints: This setting controls whether PROIV adds any locking-related hints (such as UPDLOCK, NOLOCK, etc.) to SQL queries. The effect of these hints depends on the isolation level set for your SQL Server connection.
Isolation Level:
READ_COMMITTED (default): With this isolation level, you will not be able to read values that have been updated in other transactions but not yet committed. Locking hints like NOLOCK can override this, allowing you to read uncommitted data (but with risk of "dirty reads").
SNAPSHOT: This isolation level uses row versioning. Readers do not block writers and vice versa. However, if two concurrent transactions attempt to update the same row, the second transaction will be rolled back by SQL Server, losing any uncommitted changes.
Practical Impact:
If "Enable Locking Hints" is off, PROIV does not add any hints, and SQL Server uses its default behaviour for the chosen isolation level.
If "Enable Locking Hints" is on, PROIV adds hints that can change how data is locked and read, affecting concurrency, blocking, and the risk of reading uncommitted data.
How to Enable the V2 Driver and Choose a Locking Model
You can enable the V2 driver in the PROIV Dashboard under SQL Server Advanced Options. After enabling V2, you can choose your preferred concurrency (locking) model by setting the relevant options:
Pessimistic Locking: Enable both "Select For Update" and "Enable Locking Hints" for V2 to behave like V1, with safe, serialized updates.
Optimistic Locking: Disable "Enable Locking Hints" to allow greater concurrency and potential performance benefits, but with different risks.
You can switch back to the default driver (V1) at any time if needed. In the future, if Microsoft deprecates cursor support, V1 will also be deprecated and V2 will become the standard.
What Should Users Expect?
Compatibility: V2 ensures your PROIV system will continue to work with SQL Server as Microsoft removes support for cursors.
Locking Model Choice: You can choose between pessimistic and optimistic locking in V2. V1 always uses pessimistic locking.
Parallel Updates: With optimistic locking, multiple users can update data at the same time, but if they update the same row, one update may overwrite another.
Key Uniqueness Matters: Updates using non-unique keys (alternate indexes) may affect multiple rows. Always use primary keys or unique indexes for updates.
Compatibility: All your existing PROIV functions will continue to work. No changes to your applications are needed.
Understanding "Select For Update" and "Enable Locking Hints"
Both of these options are set in the PROIV Dashboard:
Select For Update: When enabled, PROIV tries to lock the row being read for update. Only one user can update that row at a time. If two users try to update the same row, one will be blocked (or fail) until the other finishes. This helps prevent conflicting changes and ensures data integrity.
Enable Locking Hints: When enabled, PROIV adds SQL Server-specific hints (like UPDLOCK, NOLOCK) to SQL queries. These hints control how SQL Server locks data during reads and updates, affecting concurrency and blocking.
What happens if you only enable one?
Only "Select For Update" enabled: PROIV will try to lock rows for updates, but without locking hints, SQL Server may use its default locking behaviour. This can result in less predictable locking: sometimes users may be blocked, but sometimes conflicting updates can still occur. Data integrity is not fully guaranteed, and deadlocks or update failures are possible.
Only "Enable Locking Hints" enabled: Locking hints (like UPDLOCK) are added to queries, but without "Select For Update," PROIV does not explicitly lock rows for updates. This allows more parallel updates, but if two users update the same row at the same time, one update may fail or overwrite the other. In some cases (like SNAPSHOT isolation), the second update may be rolled back by SQL Server.
Best Practice:
For safest and most predictable behaviour, always enable both "Select For Update" and "Enable Locking Hints" in the PROIV Dashboard. This ensures proper row locking and data integrity during updates.
Best Practices:
Use Primary Keys for Updates: Always update using the primary key or a unique alternate index.
Avoid Non-Unique Alternate Indexes: Do not use alternate indexes for updates unless they include all primary key fields.
Fallback to V1 Driver: If you are unsure, use the default V1 driver to retain cursor-based behaviour.
Test Your Updates: Make sure your test cases cover updates via alternate indexes, partial key selections, and concurrent update scenarios.
Workarounds:
If you experience unexpected behaviour (such as updates affecting multiple rows or errors when using non-unique keys):
Switch to the default driver in the PROIV Dashboard. This restores cursor-based updates and mitigates the risk.
Frequently Asked Questions
Q: Why was the V2 driver introduced?
A: Microsoft is deprecating SQL cursors. V2 ensures your system remains compatible.
Q: Will my updates always target the correct row?
A: With V2, updates are precise if you use a unique key. If you use a non-unique key, multiple rows may be updated.
Q: What happens if two users update the same row at the same time?
A: With V2 in optimistic mode, both updates may proceed in parallel. The last update to commit will be the one that remains. With pessimistic mode (locking hints enabled), one user waits for the other to finish.
Q: Can I switch back to V1 if I have problems?
A: Yes. Simply change the setting in the PROIV Dashboard.
Q: Is V2 enabled by default?
A: No. You must explicitly select V2 in the Dashboard.
Q: What do "Select For Update" and "Enable Locking Hints" do?
A: "Select For Update" locks rows for updates, preventing conflicting changes. "Enable Locking Hints" adds SQL Server-specific hints to queries, controlling how data is locked and affecting concurrency.
Q: How do isolation levels affect locking?
A: The isolation level (such as READ_COMMITTED or SNAPSHOT) determines how SQL Server handles concurrent reads and writes. Locking hints can override or modify this behaviour, affecting whether you see uncommitted data or experience blocking.
V2 SQL Driver Configuration
The V2 SQL Driver for Microsoft SQL Server offers high compatibility with the existing "DEFAULT" driver but with potential gains in performance. It moves away from using cursors when processing records in PROIV to using explicit criteria for the keys.
The V2 SQL Driver uses the same locking hints as the "DEFAULT" driver and supports the PROIV transaction locking model. Its behaviour in some non-default configurations (different transaction isolation level etc) of the Microsoft SQL driver may differ from "DEFAULT". It is the user's responsibility to check the behaviour of the V2 SQL Driver is acceptable in their application. The existing "DEFAULT" Microsoft SQL Driver will continue to be supported can be reverted back to.
Care must be taken when creating an alternate index to update a SQL file: see Alternate Index Mapping. As the V2 SQL Driver will use the keys of the alternate index as criteria for SQL statements and not the keys of the SQL table.
Use Primary Keys for Update - Always perform updates using the primary key or a unique alternate index.
Avoid Non-Unique Alternate Indexes - Do not use alternate indexes for updates unless they include all primary key fields.
Update Logic Pattern - Select using alternate index if needed.
- Update using primary key to ensure uniqueness.
Related Topics
Configuring Virtual Machine Settings
Topic ID: 700062