Hey Tim,
> <cfquery name="qTestIns" datasource="mydb">
> insert into mytable (columnname) values ('a')
> insert into mytable (columnname) values ('b')
> </cfquery>
The code above will connect to the db with the SQL server default
isolation level. MS SQL 2000 on a default install will operate at
isolation level of READ COMMITTED. This is a server setting on the db
server, you cannot set this via the ODBC driver. To change the above code
to the same as using a <cftransaction> tag I would use something like the code below.
As
no isolation was specified on the <cftransaction> tag it will use SERIALIZABLE
<cfquery name="qTestIns" datasource="mydb">
set transaction isolation level SERIALIZABLE
go
begin transaction
insert into mytable (columnname) values ('a')
insert into mytable (columnname) values ('b')
commit transaction
</cfquery>
SQL Online books
"If transactions are run at an isolation level of serializable, any
concurrent overlapping transactions are guaranteed to be serializable."
> <cftransaction>
> <cfquery name="qTestIns" datasource="mydb">
> insert into mytable (columnname) values ('a')
> </cfquery>
> <cfquery name="qTestIns" datasource="mydb">
> insert into mytable (columnname) values ('b')
> </cfquery>
> </cftransaction>
>
> I've noticed that the short version rolls back the first insert statement
> if there's a problem with the second statement.
This cannot be guaranteed. It depends on the db server settings you are
connecting to. The default isolation level on the db server may be set to
read uncommitted to improve performance, this can lead to dirty read's,
etc. If this is the case the first insert will be written and not rolled
back.
> Does it also perform the inserts as an atomic transaction? ie. If 2 users
> hit this code concurrently, will it ensure that one user's inserts finish
> before starting on the other's?
Again it depends on the db locking. Setting the isolation level to
serializable on each db connection is the only way to guarantee each
transaction will run as you wish.
hth
Rod
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
