[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I keep from getting a deadlock error in sql server when several users are logged into a coldfusion application using the same username and password?

Posted on 2009-04-30
8
Medium Priority
?
650 Views
Last Modified: 2013-12-24
Hello,

I have the following script (in the code window below) which gives me a deadlock error when
I run it under certain conditions. If I run it with 1 user logged in, as username: user1 and password: pwd1 then it behaves fine, as expected with no error.
However, if I run it with several users logged in at once using the same username and password (e.g. all users logged in with username: user1 and password: pwd1), then I get a deadlock error.

Question: How can I keep from getting the deadlock error?
               Should it be troubleshot at the coldfusion code level or
               at the SQL query level?  Would wrapping the cftransaction tag
               in a cflock tag as in

               <cflock name="lockname" type="exclusive" timeout="30">
                   <cftransaction>
                       .......
                       .......
                   </cftransaction>
              </cflock>

         solve the problem?

Any help would be appreciated.

Thanks



<cfoutput>
    <cfset wTempAffiliateCode = "">
    <cftry>
        <cflock name="AffStdLock" type="readonly" timeout="30" throwontimeout="yes">
            <cfset wTempAffiliateCode = session.AffiliateCode>
        </cflock>
 
        <cfset wUUID = #CreateUUID()#>
 
        <!---
            Create a transaction to insert the data (master record and 15 detail records
            so that the whole operation will be treated as 1 unit of processing i.e. if the
            whole thing is successful then all of the data is committed but if any part of
            it throws an error, then the whole thing is rolled back. Threfore the master
            and detail records all go in as one logical record.
        --->
        <cftransaction>
 
            <!--- Insert master record --->
 
            <cfquery name="InsertRec" datasource="#request.AppDataSource#" >
                INSERT INTO AffStd_TAB
                (
                    AffiliateCode,
 
                    ProgramTitle,
                    TransactionDescription,
 
                    ProgramType,
                    TargetAgeRange,
 
                    Tuuid
                )
                VALUES
                (
                    '#wTempAffiliateCode#',
 
                    '#form.fProgramTitle#',
                    '#form.fTransactionDescription#',
 
                    '#form.fProgramType#',
                    '#form.fTargetAgeRange#',
 
                    '#wUUID#'
                )
 
            </cfquery>
 
            <!--- Get the master record's PK --->
            <cfquery name="GetPK" datasource="#request.AppDataSource#" >
                SELECT PK
                FROM AffStd_TAB
                WHERE Tuuid = '#wUUID#'
            </cfquery>
 
            <cfset wNewPK = #GetPK.PK#>
 
            <!--- Insert the detail records --->
            <cfloop index="i" from="1" to="15" step="1">
                <cfquery name="InsertDetailRec" datasource="#request.AppDataSource#" >
                    INSERT INTO AffStd_TAB_Detail
                    (
                        MasterRecPK,
                        AFieldName,
                        AFieldValue
                    )
                    VALUES
                    (
                        '#wNewPK#',
                        '#Evaluate("form.fAFieldName#i#")#',
                        '#Evaluate("form.fAFieldValue#i#")#'
                    )
                </cfquery>
            </cfloop>
 
        </cftransaction>
 
        <cfcatch type="Any">
            <cfmail to="xxxx@xxxx.org" from="xxxx@xxxx.org" subject="Error Inserting Recorde" type="html">
                <p>Greetings,</p>
                <p>An error has occured while attenpting to insert an Affiliate master record and corresponding detail records into tables: AffStd_TAB and AffStd_TAB_Detail</p>
                <p>A dump of the cfcatch variable is below.</p>
                <p><cfdump var="#cfcatch#"></p>
            </cfmail>
        </cfcatch>
    </cftry>
</cfoutput>

Open in new window

0
Comment
Question by:mfreeman2
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24276729
try using this one.
<cfquery name="GetPK" datasource="#request.AppDataSource#" >
                SELECT PK
                FROM AffStd_TAB WITH(NOLOCK)
                WHERE Tuuid = '#wUUID#'
            </cfquery>

Open in new window

0
 
LVL 36

Expert Comment

by:SidFishes
ID: 24279463
you might try an use

<cftransaction isolation="serializable">

more info here

http://www.garyrgilbert.com/blog/index.cfm/2008/7/15/Cftransaction-Explained
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24286644
Please give this a try, several changes as follows.

Changed CFLOCK to scope="session" rather than a named lock.  Names are unique across the entire application.  Reduced timeout to 10 seconds.

Changed how you got your Primary Key field.  Rather than fetching a newly inserted record (a good way to have a lock problem) I used the scope_identity() function to grab the ID

Removed evaluate() function  and replaced it with simple structure syntax.  It's just faster and will get you out of your transaction quicker..

I am curious why you are using CreateUUID() ?    What's the point of having this over just the unique ID from the session or the master table...
<cfoutput>
    <cfset wTempAffiliateCode = "">
    <cftry>
        <cflock scope="SESSION" type="READONLY" timeout="10" throwontimeout="yes">
            <cfset wTempAffiliateCode = session.AffiliateCode>
        </cflock>
 
        <cfset wUUID = CreateUUID()>
 
        <!---
            Create a transaction to insert the data (master record and 15 detail records
            so that the whole operation will be treated as 1 unit of processing i.e. if the
            whole thing is successful then all of the data is committed but if any part of
            it throws an error, then the whole thing is rolled back. Threfore the master
            and detail records all go in as one logical record.
        --->
        <cftransaction>
 
            <!--- Insert master record --->
 
            <cfquery name="InsertRec" datasource="#request.AppDataSource#" >
                INSERT INTO AffStd_TAB
                (
                    AffiliateCode,
 
                    ProgramTitle,
                    TransactionDescription,
 
                    ProgramType,
                    TargetAgeRange,
 
                    Tuuid
                )
                VALUES
                (
                    '#wTempAffiliateCode#',
 
                    '#form.fProgramTitle#',
                    '#form.fTransactionDescription#',
 
                    '#form.fProgramType#',
                    '#form.fTargetAgeRange#',
 
                    '#wUUID#'
                )
 
            </cfquery>
			<!--- get the master record's primary key ---->
		    <cfquery name="GetPkey" datasource="#request.datasource#">
		      select SCOPE_IDENTITY( ) as ID
		    </cfquery>  
		    <cfset wNewPK = GetPkey.ID>
 
            <!--- Insert the detail records --->
            <cfloop index="i" from="1" to="15" step="1">
                <cfquery name="InsertDetailRec" datasource="#request.AppDataSource#" >
                    INSERT INTO AffStd_TAB_Detail
                    (
                        MasterRecPK,
                        AFieldName,
                        AFieldValue
                    )
                    VALUES
                    (
                        '#wNewPK#',
                        '#form["fAFieldName#i#"]#',
                        '#form["fAFieldValue#i#"]#'
                    )
                </cfquery>
            </cfloop>
 
        </cftransaction>
 
        <cfcatch type="Any">
            <cfmail to="xxxx@xxxx.org" from="xxxx@xxxx.org" subject="Error Inserting Recorde" type="html">
                <p>Greetings,</p>
                <p>An error has occured while attenpting to insert an Affiliate master record and corresponding detail records into tables: AffStd_TAB and AffStd_TAB_Detail</p>
                <p>A dump of the cfcatch variable is below.</p>
                <p><cfdump var="#cfcatch#"></p>
            </cfmail>
        </cfcatch>
    </cftry>
</cfoutput>

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mfreeman2
ID: 24291124
Hello gdemaria,
Thanks first of all for your response. The answer to your question "why am I using CreateUUID()" is: At the time, I was not sure whether either @@identity or scope_identity() would get me absolutely and positively the id of the RECORD THAT I JUST INSERTED as opposed to the id of the last record inserted into the table. That is, after my master record is inserted but before I select the id of that record, another master record may have been inserted and I end up getting the id of THAT OTHER master record instead of the master record that I inserted. I think I now understand that @@identity WILL in fact return the id of the LAST master record entered (which may or may not be mine) but scope_identity() will in fact return the id of the RECORD THAT I ENTERED". It is my understanding that CreateUUID() creates a universally unique identifier that is guaranteed to be unique across the server. Therefore my logic was to store this value in the record and then select back the record where this stored value equals the value that I just stored in the record. This would gurantee me getting back the record that I wanted.

What I think I understand from your response and from subsequent reading is that scope_identity() WILL in fact get me the id of the master RECORD THAT I JUST INSERTED as opposed to the last record inserted in the master record table by any process. Am I correct in assuming this is true?

mfreeman2
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 24291362
Yes, you're right.  

scope_identity()  is designed to get you the last identify value created during that same session
@@identity does not ensure it and is no longer the best practice after sql server 2000

Btw, you can actually put it in the same cfquery statement if you like...



<cfquery name="InsertRec" datasource="#request.AppDataSource#" >
                INSERT INTO AffStd_TAB
                (
                    AffiliateCode,
 
                    ProgramTitle,
                    TransactionDescription,
 
                    ProgramType,
                    TargetAgeRange,
 
                    Tuuid
                )
                VALUES
                (
                    '#wTempAffiliateCode#',
 
                    '#form.fProgramTitle#',
                    '#form.fTransactionDescription#',
 
                    '#form.fProgramType#',
                    '#form.fTargetAgeRange#',
 
                    '#wUUID#'
                )
 
               select SCOPE_IDENTITY( ) as ID
            </cfquery>
            <cfset wNewPK = InsertRec.ID>

Open in new window

0
 

Author Closing Comment

by:mfreeman2
ID: 31576773
Thanks so much gdemaria. I accept your answer and gladly reward you with the points from this question.

Thanks again,
mfreeman2
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24295755
If you are using CF8, why don't you just just use the result attribute?

<cfquery name="InsertRec" datasource="#request.AppDataSource#" result="insertResults">
         INSERT INTO TableName (.....)
         VALUES (...)
</cfquery>

<cfdump var="#insertResults.NameOfYourIdentityCoumn#">
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24298334
Correction (ms sql):

<cfquery name="InsertRec" datasource="#request.AppDataSource#" result="insertResults">
         INSERT INTO TableName (.....)
         VALUES (...)
</cfquery>
<cfdump var="#insertResults.IDENTITYCOL#">
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question