Ok, the way I understand SQL Server transactions is that SQL Server
dynamically figures out what kind of lock to place on the table or row for
each transaction. Since the 'SELECT max(id) as myVar' is passed to SQL
Server in the same connection as my insert query, it becomes part of the
transaction.
So the question is, does SQL Server see the max() function and decide to
place a table level lock or only a row level lock. It would seem logical to
me, that since the max function is an operation on the entire table that SQL
Server should do a table lock for the transaction...
jon
----- Original Message -----
From: "Dave Watts" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, March 20, 2001 4:02 PM
Subject: RE: @@identity
> > I am curious, I replaced the
> > SELECT newID = @@identity
> > with
> > SELECT max(id) as myVar
> >
> > and max(id) is faster than your example in my application. Is
> > there a reason that @@identity is "better" than max(id), other
> > than the stated SQL Server issues?
>
> Using @@IDENTITY in this case makes it less likely that you'll have a
> concurrency issue. If you simply insert the new record, then query for the
> highest identity value, it's possible that someone else will have inserted
a
> record between your insert and your SELECT MAX query. If that happens,
then
> you'll get the wrong identity value. Using @@IDENTITY ensures that you get
> the identity value from your insert.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
> voice: (202) 797-5496
> fax: (202) 797-5444
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
