Joshua Miller wrote:
>
> --- "For instance, the SELECT MAX(ID) + 1 -> INSERT trick to get the
> next
> value for a primary key technically requires a serializable isolation
> level."
>
> That seems like a bad idea anyway, wouldn't it be possible for someone
> to write to the database between you grabbing that value and actually
> using it?
Exactly. That's why it is usually wrapped in a transaction. But if you
don't make that transaction serializable, you can still get another
query that writes underneath it.
> <cfquery name="qryIns" datasource="#request.dsn#">
> BEGIN TRANSACTION
> SET NOCOUNT ON
>
> INSERT INTO tblMyTable
> (MyValue)
> VALUES
> ('A Value');
>
> SET NOCOUNT OFF
> SELECT @@Identity AS 'Identity'
> COMMIT TRANSACTION
> </cfquery>
>
> Then just use: #qryIns.Identity# in your supporting data instead of the
> method above?
That is the recommended method for MS SQL Server. Other RDBMS's have
comparable methods with different syntax. Although often that is based
on the use of sequences instead of an identity field.
Does this actually work in CF MX? I thought there was a problem with
this, or maybe that was without the explicit transaction.
> Or does the method of incrementing the ID work reliably? I would assume
> that's inside of a transaction so that no one can grab that ID before
> you can use it?
In a serializable transaction it is safe.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
