cftransaction problem

The busier the page, the more single-threaded you are going to be using cflock. Can you get away from the lock completely?

Either nesting will not make a difference.  The question is does the lock need to be outside the loop.  If not then you would be better off locking inside the loop.  That way it is only aquired as needed and more threads can fit through during idle times.

The only time you need the lock is around the actual write (and possibly read) of variables/space that is shared.

I agree. You shouldn't need to do the lock if it is just database IO that is happening... the database should manage the locks.  If you need the CFLock for memory area accessing, try doing that seperately from the <CFTRANACTION> stuff.

Maybe:
<cfloop>
    <cflock  timeout="30" type ="Exclusive">
...................... pretty extensive processing requiring memory locking..........
    </cflock>
  <cftransaction>
......... pretty intensive stuff using database transactions <cfquery> stuff......
  </cftransaction>
<cfloop>

If you aren't doing anything with <cfquery>, then <cftransaction> doesn't apply (to my understanding).

i'm using the lock because inside the loop a unique ID is generated for each iteration

then the ID is added to the db

thing is, part of the processing (after updating the db) is sending emails with a link containing that ID as a variable.
if i don't lock it, the wrong ID could be sent to the wrong person

>>if i don't lock it, the wrong ID could be sent to the wrong person
No that is not true.  Once you create the ID you store it in a variable - that is local and will not be overwritten by others.

SHow the code and we can help.

ok, this is the skeleton code:

<cfloop index=i from=1 to=#arrayLen(arApp)#>

      <cftransaction>

      <cflock name="myLock" timeout="60" type ="Exclusive">

      <cfquery name="getID" datasource="#mdsn#">
            get max(id)+1
      </cfquery>
      <cfset reqID = getID.reqID>
      
      <!--- reconstitute the ldapDN replacing ~ with , --->
      <cfset goodDN = REReplace(arApp[i], "~", ",", "ALL")>

      <!--- check if req is duplicate --->
      <cfquery name="checkDupe" datasource="#mdsn#">
            check for dupe
      </cfquery>

      <cfif checkDupe.recordCount gt 0>
            <cfset dupeList = listAppend(dupeList,"#checkDupe.reqID#~#checkDupe.status#")>
      <cfelse>
      
            <cfquery name="getProfile" datasource="#mdsn#">
                  get some admin data for emails and processing
            </cfquery>

            <cfif getProfile.profileID eq 2004>
                  <cfquery name="newReq" datasource="#mdsn#">
                  insert sql data with new ID
                  </cfquery>
            <cfelse>                  
                  <cfquery name="newReq" datasource="#mdsn#">
                  insert sql data with new ID
                  </cfquery>
            </cfif>
            
            <!--- process LDAP operations. first check for dupes --->
            <cfif getProfile.profileID eq 2004>
                  <cfldap
                  action="query"
                  name="getGroup"
                  start="#getProfile.ldapDN#"
                  attributes="member"
                  filter="member=#newPC#"
                  scope="base"
                  >
                  <!--- not a dupe, process --->
                  <cfif getGroup.recordCount eq 0>
                        <cfldap
                        action="modify"
                        modifytype="add"
                        attributes="member=#newPC#"
                        dn="#getProfile.ldapDN#"
                        separator=";"
                        > 
                  </cfif>

<CFMAIL>

email containing a link containing the reqID:

http://#application.variables.myHost#/portal/reqbrow.cfm?ReqID=#reqID#<p>

</cfmail>
            </cfif>                  

      </cfif>

</cflock>
</cftransaction>

</cfloop>

OK, I didn't realize you were getting the ID out of the database. I would just lock the entire database transaction portion then, if this is really necessary (which it may be). I'm assuming the database you are using doesn't have a sequencer that can be used. For instance, in Oracle, there are sequences for this very purpose and the entire need for a lock would go away. I suspect  sql server does to, but I don't know the mechanism. Access doesn't.

So, assuming you are using access, you can do this. Just lock around all the database stuff though, and then unlock and commit the transaction. Then do the ldap lookup and the mail.

If you are using Oracle, I can help with the sequence. If you are using sql server, then we can search EE for help on how to do that.

Well that is too "skeletoon" to give actual advice on what needs to be locked, but you can definately cut down the locks by a lot even from what you showed.

The only time the lock is needed is around the pulling of the old ID up to the insertion point.

Note that putting an email message inside a transaction is useless as you can never orllback an email.  Actually since you have no commit or rollback conditions there is no need for the transaction that I see.

hmm, i thought <cftransaction> is used for single threading a chunk of code, insuring this way that variable values can't pass to a different user.

mrichmon, you mean that, even if several users are running the same chunk of code (with the same variable name, reqID) the variable value will be kept different for each user?

RCorfman, i'm using SQL server, sure, i could lock just the ID generating query, my problem is, how do i keep that ID attached to a particular user until the whole transaction ends (ldap processed and emails  sent)

Once you generate and retrieve the id, it's yours. No other user will get that same generated sequence value. That is their purpose. You generate and retrieve it, then use it for the remainder of what you need to use it for.

As far as cftransaction, the purpose of that is to ensure that all that you do using <CFquery> to update, insert, delete, etc on the database will either all be applied, or all rollback. This is only related to the database portion of what you are doing.

If you want to ensure that no other portion of the cold fusion code is accessing the database at the same time, then you can <cflock> all the database portions, but then release the lock and to the ldap lookup and send the email. The data that you retrieved regarding the id is local to the current cold fusion request, you don't need to stop all the other requests while you complete. The ID won't get mixed up with other web requests.

>>mrichmon, you mean that, even if several users are running the same chunk of code (with the same variable name, reqID) the variable value will be kept different for each user?
YES

Local variables are local - you only have to worry about data in shared scopes (i.e. application, database, etc)

cftransaction is only related to databases as rcorfman mentnioned.

>>my problem is, how do i keep that ID attached to a particular user until the whole transaction ends (ldap processed and emails  sent)

The problem is that you are misunderstanding how variables can get overwritten.  It cannot happen in your case once you pass the insert so really you don't need to worry about it .

In fact, you could remove this problem completely by changing the column from an integer in SQL server to an identity column - whihch will automatically do the +1 for you and prevent multiple ones from the same ID.

mrichmon, this will fill in a hole in my understanding too. If it is changed to an identity column (I assume this is like an access autonumber), how do you get the value back that was assigned during your transaction?

>>If it is changed to an identity column (I assume this is like an access autonumber)
Yes, Can be used the same, but can also be a little more complex if needed

>>how do you get the value back that was assigned during your transaction?
SELECT SCOPE_IDENTITY()

SOme will say to use SELECT @@Identity

This works 90% of the time, especially in simple cases, but if you have other things fire such as a trigger, then you will get the wrong value unless you use the SCOPE_IDENTITY() which keeps track of each thread.  @@Identity just gets the last identity set in any part of the connection.

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

Thank you. And to aid anyone else looking at this question in the future. In Oracle, you would select the ID first, then pass it in as part of your insert statements...
select sequence_name.nextval from dual;
I know this isn't relevant to this instance of this question, but it is if the DB were Oracle.

thanks everybody! it's gonna be hard to test this, it'll take me a while. hope it's ok if i split the points between rcorfman and mrichmon

yep :o)

Actually, the split didn't seem to happen correctly. I got all the points. I don't 'mind', but it seems more appropriate to have split them. I suspect this was an oversight...

imari,

RCorfman is correct.  If you meant to split the points between us, let me know and I can unaccept this question so you can split.

mrichmon

mrichmon, please do, thanks

sorry for the delay, hope it works this time. i've entered 125 points for mrichmon and 125 for RCorfman but it still forced me to pick just one as accepted answer.

thanks again!

Yes it worked.  The other comment(s) with points gets marked as an assisted answer.  

Thanks!