Hey folks,
Supporting a gateway with many (hundreds) bi-directional transaction groups reading from OPC-UA and writing to the same table on 5-15 second interval. It's been this way for years (10+) and has seemingly worked without issue. After a recent MSSQL upgrade, we're seeing transaction group errors that appear to be associated with deadlock.
My initial reaction is that best practice would to be to condense these writes into a single transaction if able (gateway script & stored procedure), and that these errors are the result of multiple transactions trying to lock a table simultaneously.
Hoping for your thoughts / feedback / best practices. Am I off base? Is there a better solution?
TIA.