mysql - 试图防止类(class)注册申请中的过度注册

Question

我们有 child 夏令营的类(class)登记表。我被要求确保注册仅限于可用的座位。

提交表单后，我有代码检查所有提交的类(class)是否都有座位。如果有空位，则将注册输入到注册表中。如果不是，则用户将返回到类列表以进行新选择。

我在 CFLOCK 中附上了检查可用性的代码。我的理解是 CFLOCK 一次只允许一次提交以访问锁内的代码。我以前没有使用过 CFLOCK，所以我可能使用不当。

我的问题是同时请求最后一个可用座位。我已经用最后一个座位的 5 个同时请求测试了代码，每次 5 个请求中有 3 个成功。

我正在使用 ColdFusion 9 和 MySQL 5(MyISAM 表)

任何想法或建议都会有所帮助。

这是检查座位可用性的代码:

<!--- 3E Check Class availability and Submit or Re-Select --->
<!--- Class 1 availability --->

<cfif IsDefined('Class1') and Class1 neq ''>
    <cflock name="ClassAvail" timeout="10" throwontimeout="no" type="exclusive">
        <!--- Check number of registrations for Class 1 --->
        <cfquery datasource="#datasource#" name="Registrations1">
            Select Count(*) as Count From Registrations
            Where Class_ID = #Class1#
        </cfquery>
        <!--- Check number of seats for class 1 --->
        <cfquery datasource="#datasource#" name="ClassAvail1">
            Select Class, Class_Size from Classes
            Where ID = #Class1#
        </cfquery>
        <!--- Check for seat availability --->
        <cfif Registrations1.count GTE ClassAvail1.Class_size>
            <cfset Class1_Avail = 'False'>
        <cfelse>
            <cfset Class1_Avail = 'True'>
        </cfif>

        <!--- Class 2 availability --->
        <cfif IsDefined('Class2') and Class2 neq ''>
            <!--- Check number of registrations for Class 2 --->
            <cfquery datasource="#datasource#" name="Registrations2">
                Select Count(*) as Count From Registrations 
                Where Class_ID = #Class2#
            </cfquery>
            <!--- Check number of seats for class 2 --->
            <cfquery datasource="#datasource#" name="ClassAvail2">
                Select Class, Class_Size from Classes 
                Where ID = #Class2#
            </cfquery>
            <!--- Check for seat availability --->
            <cfif Registrations2.count GTE ClassAvail2.Class_size>
                <cfset Class2_Avail = 'False'>
            <cfelse>
                <cfset Class2_Avail = 'True'>
            </cfif>
        </cfif>

        <!--- If Classes Available, Insert in Registrations --->
        <cfif (IsDefined('Class1_Avail') and Class1_Avail eq 'True') and (IsDefined('Class2_Avail') and Class2_Avail eq 'True')>
            <cfloop from="1" to="2" index="LoopCount">
                <cfif ISDEFINED("class#loopcount#")>
                    <cfset Class_ID = #ListGetAt(ClassChoice, #loopcount#)#>
                    <cfquery datasource="#datasource#" name="AddRegistration">
                        Insert into Registrations (Child_ID, Class_ID) Values(#Session.Child_ID#, #Class_ID#)
                    </cfquery>
                </cfif>
            </cfloop>
            <cflocation addtoken="no" url="Thanks.cfm">
        </cfif>
    </cflock>

    <!--- Classes not available --->
    <cfif (IsDefined('Class1_Avail') and Class1_Avail eq 'False') or (IsDefined('Class2_Avail') and Class2_Avail eq 'False')>
        <cfset ClassChoice = "">
        <cfset Class1 = "">
        <cfset Class2 = "">
        <div style="display: block; z-index: 1000; border: #F00 thick solid; background-color: #FFF; position: absolute; top: 220px; box-shadow: 10px 10px 3px 5px #999999; padding:20px">
            We're sorry, but one or more of your choices are no longer available.<br />
  Please make your selections again.<br />
        </div>
        <cfif Class1_Avail eq 'False'>
            <h3><cfoutput>#classavail1.class# is no longer available.</cfoutput></h3>
            <br/>
        </cfif>
        <cfif Class2_Avail eq 'False'>
            <h3><cfoutput>#classavail2.class# is no longer available.</cfoutput></h3>
            <br/>
        </cfif>
    </cfif>
</cfif>

Answer 1

cfquery 的 result 属性可以使这一切变得更简单。在插入查询中，recordcount 属性会告诉您插入了多少行。所以你只需要做这样的事情:

<cfquery datasource="something" result="insertResult">
Insert into Registrations
 (Child_ID, Class_ID) 
 select distinct #Session.Child_ID#, #Class_ID#
 from registrations
 where (select count(*)
 from registrations
 where class_id = #Class_id#) <= #variable for max allowed goes here#
 </cfquery

<cfif insertResult.recordcount is 1>
code for successful registration
<cfelse>
code for full class
<cfif>

您必须至少在子查询中使用 cfqueryparam。如果mysql允许，也可以在select子句中使用。