Exclusive lock in SELECT

  • Thread starter Thread starter Rodrigo Meneses
  • Start date Start date
R

Rodrigo Meneses

Hi.
When I execute a SELECT using the Fill method of SqlDataAdapter class, there
is a exclusive lock on all the tables that apperas in the SELECT clause. I
ain't using transactions. Therefore, while the SELECT is executing, I can't
have interaction with the tables.

Hope someone has an answer for this.

Thanks very mucho.
--
Ing. Rodrigo Meneses
Arquitecto de Sistemas
Corporación L'
Tel: +58(212)267.4611
Fax: +58(212)267.5395
 
Try using a query hint NO LOCK after the select and see
if it still locks.

Similarly, you may want to execute queries against one
table at a time and then use a DataRelation to glue them
together. I can't find the article, but I read (I think
in VS.NET magazine) that if at all possible, avoid
multitable queries in ADO.NET. Query each one
individually and then use DataRelations (multiple ones if
necessary) to handle everything.

Hopefully this helps.

Good Luck,

Bill

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com
 
Another solution would be to use transactions with a non-default isolation
level. This would allow you to do your reads without locking the table.

For example:

SqlTransaction tx = cn.BeginTransaction(IsolationLevel.ReadUncommitted);
cmd.Transaction = tx;
SqlDataReader rdr = cmd.ExecuteReader();
....
rdr.Close();
tx.Commit();

Hope this helps.

Neil McKechnie
 
Back
Top