Locking the database while a transfer is in progress?

[creole]

Hey guys...

I'm working on a contest which will use a database. The user will enter their information and the answers (multiple choice) then this information will be written to a database.

I need to know about cflock, which I assume will allow me to lock a transaction to prevent information becoming corrupted. Here's the code that I'm using to write to the database. First I select from the database where email EQ the email the user entered to verify that they have not yet submitted answers. Then I write the information or don't based on that information:

<cfquery name="verifyEmail" datasource="theKingContest">
SELECT email
FROM contest
WHERE email = '#email#'
</cfquery>

<cfif verifyEmail.recordcount GT 0>
<cfoutput>
We're sorry but it appears that you have already taken the quiz.
</cfoutput>
<cfelse>
<cfinsert datasource="theKingContest" tablename="contest">
<cfoutput>
Thanks for entering our contest
</cfoutput>
</cfif>

Can you guys do two things for me?
-= Look over this code and make sure it's good quality.
-= Tell me how to lock it so that only one person can submit at a time?

[nagrom]

ok creole, you caught nagrom in a typey-typey mood....

you don't need those cfoutputs (they're just surrounding text), and that cfinsert is missing the attributes it needs (but you probably knew that)

INSERT queries seem to work better for me most of the time, most people have trouble with cfinsert at some point, and give it up (but it works fine for some things, probably ok here). the syntax for those are:

INSERT INTO tablename
(field1,field2)
VALUES
(value1,value2)
WHERE email = '#verifyEmail.email#'

don't forget single quotes around your values if they are character fields

and lastly, you might want to change the if logic to 'if recordcount eq 1', since if it equals anything else, its wrong (including 2). you may want to get really anal and throw an error for 0 and a different one for 2 or higher.

as far as locking...you probably dont need to bother. unless you expect HUGE amounts of traffic and are only using access, but then the locks may crash the server anyway (for that type of exclusive lock). usually you only lock a text file write, not a database one.

the database should be fine, it gets dangerous when two people access the same record at the same time (but even then it can be ok, just someone probably will get screwed) but you won't have that happening here. each user should only be accessing their personal record, right?

even access can handle 20-30 concurrent writes simultaneously, the odds of that happening are pretty slim. many would argue to only ever use enterprise level DBs on the web (sql server, oracle, sybase, etc).

hope that helps, may the force be with you, freak-child.

[-TheDarkEye-]

database locking is done via the cftransaction tag.

[creole]

Just to point out a few things:

-= I will be using Access.
-= I'm not sure how much traffic we expect. This is an Elvis-based contest with the prize being a 2 (or 3) nights stay (with airfare) in Nashville for Elvis month. Celebrating the 25th anniversary of his "disappearance". So, we could get lots of traffic to the site.
-= As for the database? This particular app will ony write to the database. There will be no reading except for verify if the user has already submitted the answers using a particular email address.
-= I'll ditch the output tags then.
-= I didn't realize the cfinsert was missing anything. It works so what's wrong with it? Not being a smartass, but serious.
-= The main reason why I used it is because I'm inserting about 30 things into one big long row. I know that this will not make the normalization people happy but here's the db structure:

firstname
lastname
address
city, state, zip
phone
email
day, month, year (for birthdate)
add me (to mailing list)
iagree (to the rules)
1-10 (Each being a field containing one character which corresponds to their answer in the trivia game.)

I figured that since each person can only enter once (based on email) why not put it all in one row. What do you think about the logic? Is it sound or does it suck?

[ownersite]

database locking is done via the cftransaction tag

Not really, unless I am misunderstanding what you mean. <CFTRANSACTION> allows for commit/rollback capabilities for a group of queries that may be dependent upon one another. For instance, if one of the queries in the transaction fails, they all fail if encapsulated in a <CFTRANSACTION>, preventing potential database corruption. <CFLOCK> prevents potential corruption by locking reads/writes of ColdFusion data, not the database itself either. The lock on the actual database table reads/writes is handled by the database itself.

[nagrom]

creole,

i guess cfinsert default to using all available form fields, which is fine for what you're doing.

your DB table is ok too, if they can only enter once, then there isn't anything to normalize (no redundant data that i see, except maybe the trivia answers, but if the questions and answers dont change there's not much point)

it should all be fine. unless you think more than 20 people at a time will be hitting submit at the exact same time (probably happens on amazon all day, but you should be cool)

your serious smartass, nagrom.

[creole]

Great...nice to get independent confirmation that I made the right choices here. I'm sure we'll get lots of entries but I doubt there will be that many people on at once.

So you don't think that I need to do anything different?

[creole]

Originally posted by nagrom
creole,

i guess cfinsert default to using all available form fields, which is fine for what you're doing.

Yep...it does. Inserts every field with a "name" into the table you specify.

[nagrom]

Originally posted by creole
Great...nice to get independent confirmation that I made the right choices here.

uh-oh, i hear creole's pointy-haired boss coming down the hall....i hereby retract all posts to this thread

[-TheDarkEye-]

Originally posted by ownersite
Not really, unless I am misunderstanding what you mean. <CFTRANSACTION> allows for commit/rollback capabilities for a group of queries that may be dependent upon one another. For instance, if one of the queries in the transaction fails, they all fail if encapsulated in a <CFTRANSACTION>, preventing potential database corruption. <CFLOCK> prevents potential corruption by locking reads/writes of ColdFusion data, not the database itself either. The lock on the actual database table reads/writes is handled by the database itself.

actualy it does controll how the db does locking. from the cf docs...

Within a transaction block, you can write queries to more than one database, but you must commit or rollback a transaction to one database before writing a query to another. Using CFML error handling, you control whether each transaction is committed, based on the success or failure of the database query. To control how the database engine performs locking during the transaction, use the isolation attribute.

i dont think isolation works with ms access, though.