Locking during ADO .net transaction

  • Thread starter Thread starter Darren
  • Start date Start date
D

Darren

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();
 
Darren said:
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?
Click to expand...

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.
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)?
Click to expand...

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.
Will there be a performance hit for other processes hitting the table
during this transaction? (either select/insert/delete/update)
Click to expand...

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
 
Back
Top