This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice This article is not considered official documentation for K2 software and is provided "as is" with no warranties.

Issue

SQL SmartObject List methods set to Rollback on failed transaction, returns the following error:

Image

Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

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 rollbackIf you set this property to true but rollback is retained, then you will not get an error.

Image

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.

SELECT * FROM [SmartBroker].[SmartObject]
where [SmartObjectXML].value('(/smartobjectroot/methods/method/@transaction)[1]', 'nvarchar(100)') = 'rollback'

or

SELECT * FROM [SmartBroker].[SmartObject]
WHERE CAST([SmartObjectXML] AS NVARCHAR(MAX)) like '%transaction="rollback"%'

2. Update the transaction Continue.

UPDATE [SmartBroker].[SmartObject]
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

UPDATE [SmartBroker].[SmartObject]
SET [SmartObjectXML] = REPLACE(CAST([SmartObjectXML] AS NVARCHAR(MAX)), 'transaction="rollback"', 'transaction="continue"')
WHERE CAST([SmartObjectXML] AS NVARCHAR(MAX)) like '%transaction="rollback"%'



Remember: Do not modify any database definition or database content unless specifically instructed to do so by K2. No changes to the K2 Database definition or content are supported unless specifically instructed by K2. 
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