We have been using LLBL to execute a large and complicated transaction in our application. However, we have been running into deadlock issues on the server during the transaction, presumably because of the amount of communication that occurs between the clients and server during this transaction (combined with many users and slow connections in some cases).

Our solution was to put the code for this transaction onto the server in a CLR stored procedure so it can execute entirely on the server. We opted for this approach to reduce the amount of time and reduce the number of errors that could be introduced if we converted the existing code into T-SQL. I have successfully loaded all of the LLBL .dlls to the server, but I am getting errors when trying to use the context connection because only one context connection can be open at one time.

My questions are:

First, has anyone seen similar deadlock behavior with large transactions in LLBL and is there a good way to solve the issue without moving the transaction to the server?

If not, is there any LLBL support for the context connection or CLR procedures in general? Has anyone run into similar problems with the CLR procedures?

Thanks.

epiazza wrote:

First, has anyone seen similar deadlock behavior with large transactions in LLBL and is there a good way to solve the issue without moving the transaction to the server?

Could you please elaborate more on the "deadlock" and "large transactions" part? Maybe an snippet example would be helpful here, including the LLBLGen SQL generated during runtime. Did you experiment some improvement using CLR procedures over normal T-SQL procedures?

epiazza wrote:

If not, is there any LLBL support for the context connection or CLR procedures in general? Has anyone run into similar problems with the CLR procedures?

Could you post some snippet of your CRL procedure and the code that call it? Also, the details of the error you got. And... what LLBLGen version and RuntimeLibraries are you using?

I'm able to get my CLR function working by passing it a normal connection string instead of the context connection so I'm not TOO concerned about that, just was curious if you guys had any experience using LLBL with CLR functions or using the context connection (and any known problems, advice, etc.) I am also interested in hearing about how to get the "LLBLGen SQL generated during runtime" you mentioned in your post. Any help with anything below is appreciated, thanks.

Could you please elaborate more on the "deadlock" and "large transactions" part? Maybe an snippet example would be helpful here,

The deadlock problem is happening inside a function that updates many tables using many stored procedures through LLBL. By large transaction I mean that roughly 15 or 20 complex stored procedures are called in addition to saves through the entity objects, all wrapped in a single transaction. I don't think posting the code would clear a whole lot up but I posted a snippet below, note that each function (SaveEdits, SaveSpecialDistricts, LogChanges, etc.) calls one or more stored procedures or entity saves.

...
                SaveVoter(voterRegistration, effectiveBeginDate, cancelDate, transferringVoterID, willSunsetVN, isDistrictStandardDirty, _
                reasonCodeID, validationDate, voterAddressID, clientVersionNumber, printIDCard, Trans)

                SaveSpecialDistricts(specialDistricts, effectiveBeginDate, VoterAddressID, clientVersionNumber, Trans)

                LogChanges(voterRegistration, editAction, newStatusID, changeFormCode, changeDate, clientVersionNumber, Trans)

                DAL.StoredProcedureCallerClasses.ActionProcedures.ProcessEvent(newStatusID, eventCode, Trans)
...

including the LLBLGen SQL generated during runtime.

I may not be familiar with how to get access to the LLBLGen SQL generated at runtime, can you be more specific on how to do so? I can step into the LLBL generated code for each procedure that is called but don't know if that would be much help. Thanks.

Did you experiment some improvement using CLR procedures over normal T-SQL procedures?Could you post some snippet of your CRL procedure and the code that call it?

As I stated in my first post, the reason we are going to CLR over T-SQL is because we could reuse all of our existing .net code instead of converting it all to T-SQL which would be both time consuming and error prone. Basically, I just loaded the LLBL assembly into SQL server and wrote a procedure to call my original .net function. Then I call that procedure from the client. The CLR procedure looks more or less like this:

    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub spVoter_SaveNew(<SqlFacet(MaxSize:=MAX_PARAMETER_SIZE)> <Out()> ByRef voterRegistrationXML As SqlString, _
                ByVal printIDCard As SqlBoolean, ByVal hasValidDocs As SqlBoolean, ByVal clientVersionNumber As SqlString, ByVal transferringVoterID As SqlInt32, <SqlFacet(MaxSize:=MAX_PARAMETER_SIZE)> <Out()> ByRef registrationQueueXML As SqlString)

        ' Deserialize the DAL Entities
        Dim voterRegistration As DAL.EntityClasses.VoterRegistrationEntity
        voterRegistration = CType(DeserializeFromXMLString(voterRegistrationXML.Value, GetType(DAL.EntityClasses.VoterRegistrationEntity)), DAL.EntityClasses.VoterRegistrationEntity)
        Dim registrationQueue As DAL.EntityClasses.RegistrationQueueEntity
        registrationQueue = CType(DeserializeFromXMLString(registrationQueueXML.Value, GetType(DAL.EntityClasses.RegistrationQueueEntity)), DAL.EntityClasses.RegistrationQueueEntity)

        ' Call BL SaveNew function
        voterNameId = BL.VoterRegistration.Voter.SaveNew(voterRegistration, printIDCard.Value, hasValidDocs.Value, clientVersionNumber.Value, _
                transferringVoterID.Value, registrationQueue)

        ' Serialize the DAL Entities
        voterRegistrationXML = SerializeToXMLString(voterRegistration)
        registrationQueueXML = SerializeToXMLString(registrationQueue)

    End Sub

Also, the details of the error you got.

The first error I got was "A .NET Framework error occurred during execution of user-defined routine or aggregate "spVoter_SaveEdits": System.InvalidOperationException: The context connection is already in use."

This is because only one context connection can be open at a time. I overwrote the CreateConnection() function in LLBL so that it returns the existing context connection instead of trying to create a new one. Then I get errors that other LLBL.ORM functions are trying to open a connection that is already open. I think this is because they are assuming they are getting a new connection from CreateConnection.

And... what LLBLGen version and RuntimeLibraries are you using?

Designer version: 1.0.2004.2 Runtime libraries version: 1.0.2004.2

Thanks again for any help.

To get the produced SQL at runtime please check Dynamic Query Engine tracing

Use a Transaction object, which opens a new connection (which is then the Context's connection) and add all objects to use to the Transaction object, as you should. This will make sure only 1 connection is opened. (I assume you use selfservicing).

I also think that the slowness could be caused by the fact that things were executed inside a transaction which shouldn't be inside a transaction (e.g. start transaction, then read a lot of data, then save something). Also your deadlocking function, it should add the objects it is saving to the current Transaction object, also data which is READ. Otherwise you'll get multiple connections which could lead to deadlocks.

As you currently get multiple connections, your code is reading and writing to the DB with code which doesn't do things correctly: the code doesn't add everything to the same Transaction object, as stated in the manual.