This is an ado .net transaction in C# with a SQL Server 2000 database.

Have 1000 or more inserts/updates/deletes to do in a transaction.  All
or none must be committed.  Each stored proc operates on the same
table and performs only one insert, update or delete.

Will locking occur at the page or row level of the table for the
duration of the transaction?

  If specify ROWLOCK on each update/insert/delete statement would this
guarantee that only the rows affected will be locked and not the table
(or page)?

Will there be a performance hit for other processes hitting the table
during this transaction? (either select/insert/delete/update)

Thanks


Simplified Example:

myCommand.Transaction = myTrans;
for I = 1 to 1000
{
   if...
   {
      myCommand.ExecuteNonQuery( "stored_proc_insert");
   }

   if...
   {
      myCommand.ExecuteNonQuery( "stored_proc_update");
   }

   if...
   {
      myCommand.ExecuteNonQuery( "stored_proc_delete");
   }
}
myTrans.Commit();

No way to tell.  Generally single row DML will cause row and key locks.
However if you ask SQL to manage thousands and thousands of individual
row/key locks for a statement, it will sometimes decide that it would be
more efficient to escalate to a single table lock.  And sometimes a
single-row update will require locks on related tables, for instance if yyou
update the primary key side of a foreign key relationship, and the foreign
key table is not properly indexed.

ROWLOCK will prevent row locks from escalating to table locks, no matter how
many row locks you generate.  But the key locks required for the query may
affect other rows.  Imagine a non-unique index on the table.  When you
change a single row, you affect index keys which point to other rows as
well.

Yes.  Depending on the details of the table structure and indexes, other DML
may be blocked for the duration of the transaction.  However, don't abandon
your transaction.  SQL Server runs more efficiently when you do this sort of
thing in a transaction, and handling errors without a transaction is a giant
pain.

David

DbDataReadet.GetOrdinal() - duplicate column name
DataView on two DataTables
DataSet Clear slow performance in C# 2.0
Subquery in AccessDataSource Query Builder Failing
business objects vs. datasets