SQL SmartObject List methods set to Rollback on failed transaction, returns deadlock error
kbt161728
PRODUCTIssue
SQL SmartObject List methods set to Rollback on failed transaction, returns the following error:
Symptoms
You can simulate the error for the list method in dbo.SimpleTable if 'Use Native SQL Execution' is set to false and transaction is also set to rollback. If you set this property to true but rollback is retained, then you will not get an error.
Troubleshooting Steps
If this is set to rollback, it causes a deadlock. This is reproducible in SQL Management Studio:
begin tran
go
CREATE TYPE [tmp_cd75e2ebb5794467b39691937010c611] AS TABLE(
Value0 Int NOT NULL,
Value1 Int NOT NULL )
go
declare @myPK [tmp_cd75e2ebb5794467b39691937010c611];
go
rollback
This looks to be a Microsoft SQL issue: https://dba.stackexchange.com/questions/52900/can-i-create-a-user-defined-table-type-and-use-it-in-the-same-transaction.
For now, switching the transaction type to Continue would be the workaround.
If there are a lot of SmartObjects involved, you may use the script below to update this in bulk. Please remember to make a back-up of the K2 database before proceeding:
1. Get SmartObjects which has methods set to Rollback.
where [SmartObjectXML].value('(/smartobjectroot/methods/method/@transaction)[1]', 'nvarchar(100)') = 'rollback'
or
WHERE CAST([SmartObjectXML] AS NVARCHAR(MAX)) like '%transaction="rollback"%'
2. Update the transaction Continue.
SET [SmartObjectXML].modify('replace value of (/smartobjectroot/methods/method/@transaction)[1] with "continue"')
where [SmartObjectXML].value('(/smartobjectroot/methods/method/@transaction)[1]', 'nvarchar(100)') = 'rollback'
or
SET [SmartObjectXML] = REPLACE(CAST([SmartObjectXML] AS NVARCHAR(MAX)), 'transaction="rollback"', 'transaction="continue"')
WHERE CAST([SmartObjectXML] AS NVARCHAR(MAX)) like '%transaction="rollback"%'
Direct read access or modification of the data in the K2 database is also not supported, unless specifically instructed by K2, except for the ServerLog table where direct read access is supported.
http://help.k2.com/onlinehelp/k2blackpearl/devref/current/default.htm#Database_Reference.html
