Just to follow up on this thread, is the final verdict that you must use
cflock to prevent these kinds of concurrency issues in the database, UNLESS
you use CFTRANSACTION with the serializable isolation level?
On 9/12/07, Dave Watts [EMAIL PROTECTED] wrote:
They recently changed it to the
I don't want to cloud this conversation but I have to say that in all my
years of development, I've never had a database collision using
ColdFusion. I put my multiple database calls into cftransaction and I
use cflock when I'm trying to access a client's session or cookie. I've
never put a
Just to follow up on this thread, is the final verdict that
you must use cflock to prevent these kinds of concurrency
issues in the database, UNLESS you use CFTRANSACTION with the
serializable isolation level?
That's not my final verdict, for what that's worth; depending on the
specific
Brian Kotek wrote:
Just to follow up on this thread, is the final verdict that you must use
cflock to prevent these kinds of concurrency issues in the database, UNLESS
you use CFTRANSACTION with the serializable isolation level?
There may be specific cases where the above holds true, but there
Dave Watts wrote:
According to livedocs cftransaction by default uses the
default isolation level of the database - which in most
installations will be either read committed or repeatable
read.
This has come up before; I believe that this may be a documentation error.
They recently
Brian Kotek wrote:
I can't test it myself to simulate the race condition, but I definitely know
that people have been talking about this issue for a very long time. I can
remember reading posts about it going back for years, and in those cases,
people had tried CFTRANSACTION alone and it
Jake Pilgrim wrote:
Yes, you could use CFtransaction for this type of isolation, but this can
also cause problems that you wouldn't see with cflock. Cftransaction
isoloation='serializabile' for example will give you an exclusive lock to all
tables within the transaction (most restrictive
They recently changed it to the current wording because I
filed a bug against the previous wording that it used
serializable.
Ah! So I remembered a minor detail, but was completely wrong about the
important part. Thanks for setting me straight!
Dave Watts, CTO, Fig Leaf Software
Sorry for my ignorance on this,
I would have suggested using cftransaction. However, now, I'm thinking
that cftransaction may just ensure that that particular thread of
commits are done in order but it doesn't prevent other threads' from
colliding with the database which is why cflock is
Yes. CFTRANSACTION only creates a transaction for the current thread
(actually the current database connection from the connection pool). If
multiple threads could be running the same process at the same time,
resulting in a race condition, then you need to lock that set of queries as
well. Use a
Thanks Brian!
Brian Kotek wrote:
Yes. CFTRANSACTION only creates a transaction for the current thread
(actually the current database connection from the connection pool). If
multiple threads could be running the same process at the same time,
resulting in a race condition, then you need to
Yes. CFTRANSACTION only creates a transaction for the current
thread (actually the current database connection from the
connection pool). If multiple threads could be running the
same process at the same time, resulting in a race condition,
then you need to lock that set of queries as
I can't test it myself to simulate the race condition, but I definitely know
that people have been talking about this issue for a very long time. I can
remember reading posts about it going back for years, and in those cases,
people had tried CFTRANSACTION alone and it didn't make a difference.
people had tried CFTRANSACTION alone and it didn't make a difference.
CFTRANSACTION won't help for race condition problems, it only handles
rollback
if a problem happens during execution.
But two updates can still compete one against another without causing
any execution error.
CFLOCK will
Well that's just it, from what Dave (and Simon's article) are saying, this
is not the case. CFTRANSACTION (with the appropriate level of isolation)
should handle concurrency across threads as well as handling rollbacks. I'd
like to confirm that this is true, because if it is, my whole
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 11, 2007 1:32 PM
To: CF-Talk
Subject: RE: Odd database behavior: duplicate key error
Yes. CFTRANSACTION only creates a transaction for the current
thread (actually the current database connection from
Original poster here...for the record I'm using MySQL 5.
Update: I added the named lock and that squashed the problem completely.
In my case there is some additional code involved rather than just
back-to-back database updates, PLUS the possibility of some shared scope
issues, AND we are on a
Yes, you could use CFtransaction for this type of isolation, but this can also
cause problems that you wouldn't see with cflock. Cftransaction
isoloation='serializabile' for example will give you an exclusive lock to all
tables within the transaction (most restrictive level of isloation).
for stop the world
database maintenance tasks.
Jaime Metcher
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 12 September 2007 4:32 AM
To: CF-Talk
Subject: RE: Odd database behavior: duplicate key error
This is not correct at all, according to my
According to livedocs cftransaction by default uses the
default isolation level of the database - which in most
installations will be either read committed or repeatable
read.
This has come up before; I believe that this may be a documentation error. I
recall receiving an email about this
Yes, you could use CFtransaction for this type of isolation,
but this can also cause problems that you wouldn't see with
cflock. Cftransaction isoloation='serializabile' for example
will give you an exclusive lock to all tables within the
transaction (most restrictive level of isloation).
If he is using MS SQL server wouldn't with(tablock holdlock)
also need to be used on the delete AS WELL as a transaction
to actually prevent another process from inserting into the
table until the transaction was complete.
Not if you specify a sufficiently restrictive isolation level.
No, it's definitely set by the DB. For our Oracle servers, it's
read-committed. I've checked this with code to make sure (I can do so
again if no-one believes me :-)
Regardless, Dave's points still hold - cftransaction works across all
DB sessions, across all connections. What it you have two CF
I can't test it myself to simulate the race condition, but I
definitely know that people have been talking about this
issue for a very long time. I can remember reading posts
about it going back for years, and in those cases, people had
tried CFTRANSACTION alone and it didn't make a
Hey all,
In an inherited application, we have the following pseudo-code - two
queries, one right after the other:
cfquery
DELETE FROM table
WHERE ID = '#myidstring#'
/cfquery
cfquery
INSERT INTO table
(ID)
VALUES
('#myidstring#')
/cfquery
Given this scenario, it would seem that you could
You probably have a race condition, where two people execute the code
at the same time, and the order goes like this:
A - delete
B - delete
A - insert
B - insert
In this case, you'd expect the fourth statement (the second insert) to
throw the dupe key exception.
cheers,
barneyb
On 9/10/07,
If this is true - wrap the code in a named lock:
cflock name=aUniqueLockName type=exclusive
your SQL here
/cflock
A named lock ensures that only one thread at a time will be allowed to run the
code within the cflock tag. CFTransaction will not give you this same level of
isolation.
Jake
Thanks Jake and Barney, I'll lock it and see if that helps.
-- Josh
- Original Message -
From: Jake Pilgrim [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Monday, September 10, 2007 2:50 PM
Subject: Re: Odd database behavior: duplicate key error
If this is true - wrap
28 matches
Mail list logo
