cftransaction vs cflock 1

[GUJUm0deL]

Whenever I do a <cfquery> for INSERT or UPDATE statements, I always wrap the tags around <cftransaction> and <cftry>/<cfcatch>. Do I also need to do a <cflock> as well?

Is it better to do this:
<cflock><cftransaction><cftry>

Over this:
<cftransaction><cftry>

How does one affect over the other when dealing with, say 10 people, hitting the SUBMIT button which would trigger the action page?

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[cfSearching]

Whenever I do a <cfquery> for INSERT or UPDATE statements, I always wrap the tags around <cftransaction> and <cftry>/<cfcatch>.

<cftransaction> is intended for use with multiple statements. So all statements succeed or fail as a single unit. If you are just doing a single insert/update, there is no reason to use cftransaction at all.

Do I also need to do a <cflock> as well?

cflock is really not intended for use with database queries or as a substitute for transactions. If you need transactions, let your database handle it. Most major databases support them.

----------------------------------

http://cfsearching.blogspot.com/

 

[GUJUm0deL]

When I use the <cftransaction> tag, it's only on multiple INSERT INTO or UPDATE statements, so if one fails they all fail.

About <cflock>, let's say I have 10 ppl trying to log in at the same exact time, once they all hit the submit button, I run the username/password combo against the dB to see if a match exists. If a match exists, I create a series of session vars (name, email, usertype, userstatus, lastloggedin, phone) which I carry over through their session. When I create the session vars, I wrap that code around <cflock>.

What I'd like to know is:
1. Is that considered best-practice?
2. Are there other instances that I should do a <cflock>?

Say, the same 10 ppl update their personal info by changing their email address. Should the UPDATE statement be wrapped in <cflock> as well? I wouldn't think so cause in my WHERE clause, I do something like: WHERE userid = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.thhisuid#" null="no">

The WHERE clause pretty much guarantees that the dB won't update user2's info with user1.

Does that make sense?

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[cfSearching]

About <cflock>, let's say I have 10 ppl trying to log in at the same exact time, once they all hit the submit button, I run the username/password combo against the dB to see if a match exists. If a match exists, I create a series of session vars (name, email, usertype, userstatus, lastloggedin, phone) which I carry over through their session. When I create the session vars, I wrap that code around <cflock>.

The users are on different browers (ie different sessions) and all have different usernames. So you would you need a lock there? Typically you only need to lock session variables when you are concerned about race conditions.

Say, the same 10 ppl update their personal info by changing their email address. Should the UPDATE statement be wrapped in <cflock> as well? I wouldn't think so cause in my WHERE clause, I do something like: WHERE userid = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.thhisuid#" null="no">

The WHERE clause pretty much guarantees that the dB won't update user2's info with user1.

Unless I am missing something, I would not use cflock here at all. Yes, the WHERE clause should ensure that the correct record is updated. However, it does not protect against two users updating their information with the same email address, at the same time.

To avoid that scenario your database query should only update the record if the email is not already in use. A few ways of doing that:

- using a transaction
- using a single query with an exists clause (which is an implicit transaction)
- use a unique constraint. that would prevent any duplicates by rejecting them





----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

Typo correction:
... So why would would you need a lock there?

----------------------------------

http://cfsearching.blogspot.com/

 

[GUJUm0deL]

The users are on different browers (ie different sessions) and all have different usernames. So you would you need a lock there? Typically you only need to lock session variables when you are concerned about race conditions.

So having the <cflock> there is meaningless? Does it do any extra processing or use up extra resources if <cflock> is there?

Unless I am missing something, I would not use cflock here at all. Yes, the WHERE clause should ensure that the correct record is updated. However, it does not protect against two users updating their information with the same email address, at the same time.

But wouldn't those two users have two different userid's? And having that WHERE clause would protect me from the scenario, no?

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[cfSearching]

So having the <cflock> there is meaningless? Does it do any extra processing or use up extra resources if <cflock> is there?

Yes.. unless I am missing something. Sessions are not shared the same way as the application scope. So if my co-worker "Joe" logs in at his computer (with his username) and I log in at my desk (with a different username), there is no conflict. Unless race conditions are a concern, there is usually no need for a lock in that situation.

Yes, code with cflock does do extra processing compared to code without it. The result may or may not be negligible. That depends on your timeout and exactly what you are locking. However, it is still technically extra processing.

But wouldn't those two users have two different userid's? And having that WHERE clause would protect me from the scenario, no?

No. It only ensures the correct record number is updated. It does not control what information you put in those records. With a plain update, there is nothing to prevents two users from updating their information with the same email address. Not unless you are using constraints, transactions, etcetera.






----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

Example

Record# , User name, email
12, Joe, joe@someplace.com
25, Leigh, leigh@someplace.com

The WHERE clause will ensure Joe's query updates record #12 and Leigh's query updates record #25. It will not stop both of them from modifying their information to use the same email.

12, Joe, same@someplace.com
25, Leigh, same@someplace.com


----------------------------------

http://cfsearching.blogspot.com/

 

[GUJUm0deL]

Oh, so your example was more geared toward making sure the record being updated is a unique email and not having the two users' info swapped. I see what you mean. Lemme think about how I would want to avoid that situation.

Thanks for clearing up the <cflock> misconception. I will remove the <cflock> from that session vars creation since there is no threat of having one users session mixed with another users. BTW, I had timeout=10, so it wasn't that much of a time lag too.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[cfSearching]

Well, as long as the lock timeout is small, and only encompasses session variables (not queries, etcetera), it should not cause a problem if you keep the locks. The OnSessionStart method of Application.cfc automatically locks the session scope automatically anyway. But like I said, locking is only critical if you are concerned about race conditions or are using an very old version of CF. Older versions had serious problems when you did not use locking.



----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

Oh, so your example was more geared toward making sure the record being updated is a unique email and not having the two users' info swapped.

Yes.

----------------------------------

http://cfsearching.blogspot.com/

 

[GUJUm0deL]

Thanks dude.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[cfSearching]

Welcome!

----------------------------------

http://cfsearching.blogspot.com/