Skip to main content
M
Movak
Participating Frequently
January 20, 2014
Question

Database locking

  • January 20, 2014
  • 1 reply
  • 1600 views

I am working on a system that signs customers up for workshops. Some of these workshops have limited capacity. The issue comes in that customers could run the same code simultaneously. Two customers could check capacity and find that room is available and the both sign up for the last avilable spot.

One solution would be to put a cflock around the code that checks the capacity and adds the customer if there is room. The problem is that we run on multiple servers updating the same database. It would be possible for customers to similtaneously run the same code from different servers.

This led me to thinking about using database locks but the way I understand CF data sources is that there is no way to ensure sequential queries go to the same database session. Since database locks are database session based this would make database locking not work.  Am I correct in that?

The only way I can think of is to try to do it in a stored procedure and I would rather not. Does anyone have any other ideas?

Thanks in advance.

PS I am using cf6.1

This topic has been closed for replies.

1 reply

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
January 21, 2014

I would also go for a database lock. I see no problem with sequential queries. After all, you will be locking tables, not database sessions. Also, lock will be the first instruction you send to the database, and unlocking the last. That is, something like

Lock

Query 1

Query 2

Unlock

Here follows an example I have just whipped up in MySQL. The table names are animals and birds. The lock types are read for animals and write for birds.

<cfquery name="q1" datasource="cfmx_db">

LOCK TABLES animals READ, birds WRITE

</cfquery>

<cfquery name="q2" datasource="cfmx_db">

SELECT * FROM animals

LIMIT 5

</cfquery>

<cfquery name="q3" datasource="cfmx_db">

SELECT * FROM birds

LIMIT 3

</cfquery>

<cfquery name="q4" datasource="cfmx_db">

UNLOCK TABLES

</cfquery>

<cfdump var="#q2#">

<cfdump var="#q3#">

M
MovakAuthor
Participating Frequently
January 21, 2014

> I would also go for a database lock. I see no problem with sequential queries. After all, you will be locking tables, not database sessions.

The problem is that table locks are associated with sessions. When the session goes so does the lock.

From the manual

> If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional).

The problem is that there seems to be no way to ensure that all sequential queries will go through the same connection as the driver controls the connection and not the calling program.

For example, the program could do the "LOCK TABLES .." query and the driver may immediately drop the connection and therefore the session is terminated and therefore the lock will come off. Dropping a lock when the session disconnects is a safety feature to ensure locks do not hang around if the program dies and the connection is lost.

There seems to be no gaurantee that the driver maintains the same connection, and therefore session, between cfqueries.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
January 21, 2014

Movak wrote:

The problem is that there seems to be no way to ensure that all sequential queries will go through the same connection as the driver controls the connection and not the calling program.

... 

There seems to be no gaurantee that the driver maintains the same connection, and therefore session, between cfqueries.

There may be no guarantees, however there is a way for ColdFusion to instruct the driver to maintain a connection. You can configure this in the ColdFusion Administrator.

To do so, click to select the particular datasource from the Data Sources page.  Then click on 'Show Advanced Settings'. Ensure that the setting 'Maintain Connections' is checked. There you are.

There are some caveats, however. For example, if your query tags each has a username and password attribute, then a new connection will be created for each request to the database. See the article How are database connections handled in ColdFusion?.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices