> 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...
There are two points here.
First, a connection does not a (safe) transaction make. You can run multiple
SQL commands within a single CFQUERY tag, and that's an SQL batch. Each
individual SQL statement within the batch is itself a transaction - it will
either completely succeed or it will completely fail. Within a batch
containing multiple statements and no transaction logic, the only way that
all statements will fail is if the batch fails to compile.
Second, the locking required by the use of the MAX function may vary. For
example, if it's used on an identity column as in our example, that column
will probably have an index, and in SQL Server you'd be able to take
advantage of something called "key-range locking" which is significantly
less severe than a table lock.
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
