>
> <CFTRANSACTION>
> <CFQUERY NAME = "InsertMain">
> INSERT INTO tbl_Main (adadadadadadadada)
> VALUES (dadadadadadad)
> </CFQUERY>
> <CFQUERY NAME = "GetID">
> SELECT MAX ID from tbl_Main
> </CFQUERY>
> </CFTRANSACTION>
>
> The question that I have is do I also need to include
> <CFLOCK> with this. I have been told that I should by some
> people and not by others. Does CFTRANACTION automatically do
> a lock or do you need to specify it.
The short answer is, no, you probably don't need to use CFLOCK with this,
and you don't need to use CFLOCK with database transactions in general. In
general, the only time you need to use CFLOCK is when you have something in
your CF code that you don't want multiple people to be able to interact with
in specific ways at the same time.
The longer answer is that to address these issues, you really have to have a
thorough understanding of how databases handle transactions and locking.
The whole purpose of CFTRANSACTION is to let you tell the database that a
series of queries should be treated as a single transaction. Whenever you
talk to the database from CF using CFQUERY or CFSTOREDPROC, you will be
conducting one or more transactions with the database. The database will
lock records as appropriate during any transaction - locking is central to
multi-user functionality within most DBMSs.
In the above case, where you're inserting and then fetching the
database-generated primary key, you may need to tell the database what kind
of transaction you want, however. The default isolation level for most
databases is something called "read committed", which will create a
transaction that places exclusive locks for the duration of changes, that
places shared locks for the duration of reads, and that will honor any
existing locks it encounters. If you're using Access, this would be
sufficient to ensure that no one else could insert a record until your
transaction is complete, since Access would lock the entire table for the
duration of the transaction. If you're using SQL Server 2000, on the other
hand, this may not be sufficient, since SQL Server 2000 can lock individual
records. So, in that case, you'd want to specify a stricter isolation level,
such as "serializable". You can do this using the ISOLATION attribute of
CFTRANSACTION:
<cftransaction isolation="serializable"> ... </cftransaction>
However, if you're using something like SQL Server, you can avoid this
problem by using database-specific functionality, like @@IDENTITY or
SCOPE_IDENTITY, which will let you return the identity of the inserted
record directly.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
