This is all happening inside one explicit transaction: Begin Tran, Insert into Table1 (on connection1), Select max() from table1 (on connection2) then insert into table2 (on connection1). And yes, there are triggers involved. Triggers on Table2 could actually need to go back and update Table1. The developers tell me that they are not explicitly setting the Transaction Isolation level so my belief is that it is the default (Read Committed). I have asked the developers to run the DBCC Useroptions command from inside the app to confirm that however. But, as far as I know, Read Committed was also the default in SQL 7 so it is still unclear why this is failing on SQL 2K. I did run the sp_updatestats on this database and the select max() is using the primary key for the table (but it is still a table scan). For code change options, I think the best would be to change the select max() to select @@Identity, but this would take to most work and testing. Would it be bad to explicitly set the transaction isolation level to Read Uncommitted? My understanding is this only affects select statements. But I am also not sure we would want to do that.

Mark Hottinger