How to get rid of Exception The statement or function must be executed in the context of a user transaction?

Question

How to get rid of Exception The statement or function must be executed in the context of a user transaction?

I'm working on asp.net 4.0 web application.

On button click at the same moment with different browser, I'm getting this exception. To reproduce the issue I have created multithread and able to reproduce the issue. web application calls wcf service for database activity.

System.Data.SqlClient.SqlException (0x80131904): The statement or function must be executed in the context of a user transaction. The statement has been terminated.

btnClick(Object sender, EventArgs)
{
foreach (string strProductGroupId in strArrProductGroupIds)
                                {
for (int i = 0; i < arrStrPosIds.Length; i++)
                                        {
                                            bCloningSuccess = m_ServiceObj.InsertPosClonnedDetailForAPosInGroup(OldPOSDetails, m_UserName, Convert.ToInt64(arrStrPosIds[i]), lProductGroupId);
}
}
}

WCF Code:

        public bool InsertPosClonnedDetailForAPosInGroup(string[] arrStrOldPosDetail, string strUserName, long lClientID, long lProductGroupId)
    {
        bool bReturnVal = false;
        try
        {
            bool bDeleteAllRulesFromPos = DeleteAllTheRulesPresentOnPosAccToProductGroup(lClientID, lProductGroupId);

            if (true == bDeleteAllRulesFromPos)
            {
                bReturnVal = InsertClonnedPOSDetails(arrStrOldPosDetail.ToList(), strUserName, lClientID);
            }
        }
        catch (Exception ex)
        {
            if (m_isErrorEnabled)
            {

                m_log.Error("\n ----------------------------Exception Stack Trace--------------------------------------");
                m_log.Error("Exception occured in method :" + ex.TargetSite);
                m_log.Error(ex.ToString());

            }
            bReturnVal = false;
        }
        finally
        {
            if (m_reader != null)
            {
                m_reader.Close();
            }
            if (m_con != null)
            {
                m_con.Close();
            }
            if (m_command != null)
            {
                m_command.Dispose();
            }
        }
        return bReturnVal;
    }

c#

asp.net

sql-server

wcf

asked on Stack Overflow Mar 11, 2015 by

vivek jain • edited Mar 11, 2015 by

vivek jain

2 Answers

I had the same message, troubleshooting it, i found:

This is mistake when using sp_releaseapplock or sp_releaseapplock in SQL Server. If sp_releaseapplock @LockOwner value is Transaction(default), it must be executed inside a transaction scope.

[ @LockOwner= ] 'lock_owner' Is the owner of the lock, which is the lock_owner value when the lock was requested. lock_owner is nvarchar(32). The value can be Transaction (the default) or Session. When the lock_owner value is Transaction, by default or specified explicitly, sp_getapplock must be executed from within a transaction.

sp_releaseapplock (Transact-SQL)

answered on Stack Overflow Mar 17, 2015 by

Bruno Matuk

you must see sp_getapplock sample code(sp_getapplock must be in begin transaction code).

this link and this link has example of that code

first link sample:

BEGIN TRANSACTION;  
DECLARE @result int;  
EXEC @result = sp_getapplock @Resource = 'Form1',   
               @LockMode = 'Shared';  
EXEC @result = sp_getapplock @Resource = 'Form1',   
               @LockMode = 'Exclusive';  
EXEC @result = sp_releaseapplock @Resource = 'Form1';  
COMMIT TRANSACTION;

and second link sample:

DECLARE @RC INT
Begin tran
Exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive'
            , @LockOwner='Transaction', @LockTimeout = 15000
SELECT @@SPID [session_id], @RC [return code], GETDATE()
waitfor delay '00:00:08'            
commit

answered on Stack Overflow Nov 11, 2019 by

Ali Rasouli

User contributions licensed under CC BY-SA 3.0