Last_insert_id does just that, gets the last inserted id.
No matter who entered it.
This means that under intense traffic, that select may not return the id
associated with that transactions action.
You should find the mysql equivalent to ScopeIdentity()
Or if you are using a newer version of cf, the generatedkeys.
-----Original Message-----
From: Andre Kapp <kap...@gmail.com>
Sent: Monday, December 07, 2009 11:22 AM
To: cf-talk <cf-talk@houseoffusion.com>
Subject: Possible thread issue?
I'v now come across a code problem that I simply cannot understand what is
happending here.
The code is part of a cfc module that is behind a web-service.
This is not inside a loop - just a normal cfc with a function that enter,
process and returns data...
The problem here is the transID.
When you start putting a bit of load on this code, the transID gets
mangled/confused/....
When looking at the first cflog here, the trans id is the correct value is an
thedatabase table.
However, after the values are assigned to stTrans, the trans id is different!
It is that of another tx that happend later on...
Nowere is the transID updated in the code between the two cflog's. The value in
the cflog is correct, as it is this value that I receive on the other side of
the WebService module....
Here is the logfile output
Look for card no xxxx00213
Information","jrpp-5","12/07/09","20:11:27","GCCSVPWS","Process tx for
xxxxxxx00213 with tx id 163537 and Thread.txid is 163537"
"Information","jrpp-0","12/07/09","20:11:27","GCCSVPWS"," Process tx (after
transaction) for xxxxx000346 with tx id 163537 and Thread.txid is 163537"
"Information","jrpp-4","12/07/09","20:11:27","GCCSVPWS"," Process tx (after
transaction) for xxxx38304 with tx id 163537 and Thread.txid is 163537"
"Information","jrpp-7","12/07/09","20:11:27","GCCSVPWS","Process tx for
xxxxxxxx254"
"Information","jrpp-9","12/07/09","20:11:27","GCCSVPWS","Process tx for
xxxxxxx0270"
"Information","jrpp-16","12/07/09","20:11:27","GCCSVPWS"," Process tx (after
transaction) for xxxxxx005097 with tx id 163537 and Thread.txid is 163537"
"Information","jrpp-18","12/07/09","20:11:27","GCCSVPWS"," Process tx (after
transaction) for xxxxxx00239 with tx id 163537 and Thread.txid is 163537"
"Information","jrpp-19","12/07/09","20:11:27","GCCSVPWS"," Process tx (after
transaction) for xxxxxx00304 with tx id 163537 and Thread.txid is 163537"
"Information","jrpp-8","12/07/09","20:11:27","GCCSVPWS","Process tx for
xxxxxxxxx12 with tx id 163538 and Thread.txid is 163538"
"Information","jrpp-9","12/07/09","20:11:27","GCCSVPWS","Process tx for
xxxxxxxxx270 with tx id 163539 and Thread.txid is 163539"
"Information","jrpp-3","12/07/09","20:11:27","GCCSVPWS"," Process tx (after
transaction) for xxxxxxxx0288 with tx id 163539 and Thread.txid is 163539"
"Information","jrpp-7","12/07/09","20:11:27","GCCSVPWS","Process tx for
xxxxxxxxxxx254 with tx id 163540 and Thread.txid is 163540"
"Information","jrpp-5","12/07/09","20:11:27","GCCSVPWS"," Process tx (after
transaction) for xxxxxxxxxx0213 with tx id 163540 and Thread.txid is 163540"
I found a problem when I did code optimizationon the other side of the
webservice. First thought it was the web service, but trace it right down to
this bit of code on the one server.
This is happening on my Mac's VM's running CentOS 5.2 and Coldfusion 8 on top
of that.....
Coldfusion details:
Server Product ColdFusion
Version 8,0,1,195765
Operating System UNIX
OS Version 2.6.18-128.2.1.el5
JVM Details
Java Version 1.6.0_04
Database is MySQL version 5.0
Any any any ideas will be appreciated!
snipped ----
SELECT last_insert_id() AS transid;
</cfquery>
<cfset theTransID = trans_insert.transid>
<cfset Thread.txid = trans_insert.transid>
<cflog file="transdao_cfc" text="Process tx for #cardnumber#
with tx id #theTransID# and Thread.txid is #Thread.txid#" type="information">
<!--- Update acct balance on gateway --->
<cfquery datasource="#variables.dsn#">
UPDATE acct_balances
SET
acct_balance = acct_balance + #arguments.amount#,
acct_balance_available = acct_balance_available +
#arguments.amount#
WHERE
acct_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.acctid#">
</cfquery>
</cftransaction>
<!---------------------------------------------------------------------------
CHECK FOR NEG to POS
---------------------------------------------------------------------------->
<cftry>
<cfif theCurrentActualBalance LT 0 AND theNewActualBalance GTE
0>
<cfquery datasource="#variables.dsn#">
UPDATE fees_collection
SET
settled = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="1">,
settled_trans_id = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#theTransID#">,
settled_dttm = NOW()
WHERE
acct_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.acctid#"> AND
settled = 0
</cfquery>
</cfif>
<cfcatch type="any"><cflog file="transdao_cfc"
text="#cfcatch.toString()#"></cfcatch>
</cftry>
<!--- Set response --->
<cfset stTrans.responsecode = 00 />
<cfset stTrans.transid = theTransID />
<cfset stTrans.success = true />
<cfset stTrans.message = "" />
<cfset stTrans.start_actualbalance = theCurrentActualBalance>
<cfset stTrans.start_availablebalance = theCurrentAvailableBalance>
<cfset stTrans.end_actualbalance = theNewActualBalance>
<cfset stTrans.end_availablebalance = theNewAvailableBalance>
<cflog file="transdao_cfc" text=" Process tx (after transaction)
for #cardnumber# with tx id #stTrans.transid# and Thread.txid is #Thread.txid#"
type="information">
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328928
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
