> -----Original Message-----
> From: Adam Churvis [mailto:[EMAIL PROTECTED]
> Sent: Saturday, July 12, 2003 1:11 AM
> To: CF-Talk
> Subject: Re: datasource connections
>
> Jim Davis said:
> > Run the fist and before it finishes, run the second - the second will
> return
> > while the first is still working showing that two connections are in
> use.
> >
> > This is exactly the reason that DB Transactions (and by extension the
> > CFTRANSACTION tag) exist.
>
> Huh????????
I'm not sure what you're confused about so I'll assume.
Databases and ColdFusion are multi-threaded - this means that multiple
actions can happen at one time.
For example one thread (generally a connection in a DB and a Request in CF)
can be updating a table at the same time another is reading from it. This
is, I hope it's obvious a bad thing.
Database transactions (using the SQL statement TRANSACTION and the CF tag
CFTRANSACTION) force the database to allow only one thread to "touch" any
particular object at a time (it's more complicated than that... but for now
let's be simple). This is also known as "Serializing" statements ("running
statements in serial"). In ColdFusion (and most other multi-threaded
languages) this is known as "Locking" (you'll also here the term in relation
to databases) and is what the CFLOCK tag was created for.
If the DB allowed only one connection at a time (which would make databases
nearly unusable) then no transactions would be needed - every single thing
that happened in the DB would follow one after the other in a nice, logical
progression. You'd never get any real work done, but you wouldn't have to
worry about locking.
Let's do an example. Take the following pseudo-code which represents a
common problem: you want to insert a new record and then get the ID of that
record:
<cfquery>
INSERT NEW RECORD
</cfquery>
<cfquery>
SELECT ID OF MOST RECENTLY INSERTED RECORD
</cfquery>
(There are MUCH better way's to do this, but this is the way nearly
everybody does it their first time out.)
If that code is run by you alone, fine - no problem. However when multiple
people are running that code AT THE SAME TIME (which can happed because both
CF and the DB are multi-threaded) then you're not guaranteed that the second
statement will get you the record that the previous statement inserted. It
may pull the record from another request.
To solve this you can place the two statement into a TRANSACTION. This will
instruct the database that you'll be doing SEVERAL things at to protect the
integrity of the date while you do them. The code would then look like
this:
<cftransaction>
<cfquery>
INSERT NEW RECORD
</cfquery>
<cfquery>
SELECT ID OF MOST RECENTLY INSERTED RECORD
</cfquery>
</cftransaction>
In the database world this has a HUGE benefit not generally found in
programming languages: the ability to "roll-back" the transaction. In other
words you can do a bunch of statements, but if the last one fails you can
reverse ALL the changes made in the transaction. CFTRANSACTION can expose
this to CF like so:
<cftransaction action="BEGIN" isolation="SERIALIZABLE">
<cftry>
<cfquery>
INSERT NEW RECORD
</cfquery>
<cfquery>
SELECT ID OF MOST RECENTLY INSERTED RECORD
</cfquery>
<cfcatch type="Any">
<cftransaction action="ROLLBACK" />
<cfrethrow />
</cfcatch>
</cftry>
<cftransaction action="COMMIT" />
</cftransaction>
Using the above code ANY error in the <CFTRY> will nullify all actions taken
in the database. If the second CFQUERY fails then he first is ROLLED-BACK
and nothing is inserted.
For a larger discussion on multi-threading in CF you might want to read
through the first few pages of my Guide to Locking in CFML:
http://www.depressedpress.com/DepressedPress/Content/ColdFusion/Guides/Locki
ng/Index.cfm
DB Transactions and Locking are one of the most ignored things, especially
by newbies: but they're ESSENTIAL to building solid applications. One of
the biggest issues is that you'll NEVER have locking problems sitting in
front of a dev server programming - you're the only one on the box.
Everything works fine and you give the application the customer. Then it
takes a little load and things go crazy: bad data, customers seeing other
customers data, etc. This is a VERY common scenario: much of the money I've
made consulting with CF comes from making applications thread-safe.
So maybe on second thought you shouldn't pay any attention to what I've
said: I could use the money! ;^)
Jim Davis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
