<CFLOCK> & <CFQUERY> Best Practices 1

[grx21]

Hello Everybody,
Could someone please explain when it is necessary to use <cflock> on a query and when it is not necessary to use it? Recently I was going over the code left over by a previous programmer and I noticed that he used <cflock> on just about everything including all of his <cfquery>. I would really appreciate it.

Thank you,

GRX21

 

[Silvano]

you don't have to use cflock with cfquery tags;
proper use of cflock is whenever session and application variables or CFX tags are used; Sylvano
dsylvano@hotmail.com

 

[grx21]

That's what i thought also. I didn't think that it was necessary to use <cflock> on every single <cfquery>. Thanks for the reply.

 

[serbonf]

I personally use CFLOCK when I have to read/modify some APPLICATION level variables.
Let's suppose that you are the SysAdmin of a certain website, and you have to introduce a new feature which requires to suspend for a little while the access to those guys who usually update the database.
A good thing to do is to disable the possibility to modify the database disabling those templates parts that allow the record changes/insertion.
Well, you may have an APPLICATION.ModifyDatabase flag that controls the users access to those templates.
You will store the new flag status into the first record of your general_application_data table.
Then at the very beginning of each template where are possible some modifications to the database the program will read this flag from that table to know if it there is a green status set for the database modifications and stores it into the APPLICATION.ModifyDatabase variable.

<CFQUERY NAME=&quot;RefreshGlobals&quot; DATASOURCE=&quot;#application.ds#&quot; ... etc>
SELECT TOP 1 * From general_application_data
</CFQUERY>
<CFLOCK TIMEOUT=&quot;1&quot; SCOPE=&quot;APPLICATION&quot; TYPE=&quot;Exclusive&quot;>
<CFSET APPLICATION.ModifyDatabase = RefreshGlobals.ModifyDatabase>
</CFLOCK>

You may also repeat this check with a simple meta refresh call to an empty template stored somewhere (for instance into a very small invisible IFrame onto your main document).

A good programming habit is to lock the flag before reading it, to prevent the master program to modify it in this way:

<CFLOCK TIMEOUT=&quot;1&quot; SCOPE=&quot;APPLICATION&quot; TYPE=&quot;Exclusive&quot;>
<CFSET ModifyDBAllowed = APPLICATION.ModifyDatabase>
</CFLOCK>

Then you may redirect the flow to some enabled or disabled piece of code that manages the QUERY updates.

Take in mind that the code I have written here is a simple example. The general management features are a little bit more complicated.

I hope I have been clear.

Sergio

 

[Silvano]

interesting concept you have here, serbonf...
only one thing though, when you read the application variable you may and should use read only type, e.g.:

<CFLOCK TIMEOUT=&quot;1&quot; SCOPE=&quot;APPLICATION&quot; TYPE=&quot;ReadOnly&quot;>
<CFSET ModifyDBAllowed = APPLICATION.ModifyDatabase>
</CFLOCK>

here is simple scenario for that:

- User 1 Locks the session scope with a read lock
- Attempts to lock the session scope with an exclusive lock
- Deadlock: Attempts to lock the session scope with an exclusive lock, but cannot because the scope is already locked for reading



Sylvano
dsylvano@hotmail.com