Database locking

[Movak]

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

[BKBK]

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#">

[Movak]

> 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]

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?.

[Carl Von Stetten]

BKBK,

I think the OP stated that his application is "run on multiple servers updating the same database", so the Maintain Connections setting won't help him

-Carl V.

[BKBK]

Carl Von Stetten wrote:

BKBK,

I think the OP stated that his application is "run on multiple servers updating the same database", so the Maintain Connections setting won't help him

Imagine I am connected to a database, as are 3 separate servers. My 'Maintain Connections' setting pertains to me alone. I make no assumptions about the 3 servers contending for the same database. They might in fact not even be ColdFusion servers.

My post was answering just one specific question. The original poster said "there seems to be no way to ensure that all sequential queries will go through the same connection" and again that "There seems to be no gaurantee that the driver maintains the same connection". I was just letting him know there is a way to maintain a connection.

[Movak]

Maintain connections might work, along with table locking, if there was a gaurantee that sequential calls from the same user session go to the same database connection and queries from other user sessions would not be interspersed. All the documentation says is that the connection is not dropped after a query is completed and the connection will be used again for a new user session.

Gaurantees are very important because not knowing how it works means that it may not work the way we think and therefore the "solution" may not be valid.

The problem sems to be that the driver puts the database at arms length and it may treat each query as a separate connection to the driver.

[BKBK]

Movak,

I see your point about the possibility of sessions dropping off like fruitflies, once used. The problem is that such fine-grained interaction between application servers and databases is unpredictable, let alone documented.

You could solve the problem by resorting to the big guns: cftransaction. Rather than keep the database at arm's length, the tag grabs it by the scruff of the neck.