Exclusive lock in SELECT

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

William
Mon Aug 18 16:58:22 CDT 2003

Try using a query hint NO LOCK after the select and see=20
if it still locks.

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

Hopefully this helps.

Good Luck,

Bill

W.G. Ryan
dotnetguru@comcast.nospam.net
www.knowdotnet.com

>-----Original Message-----
>Hi.
>When I execute a SELECT using the Fill method of=20
SqlDataAdapter class, there
>is a exclusive lock on all the tables that apperas in=20
the SELECT clause. I
>ain't using transactions. Therefore, while the SELECT is=20
executing, I can't
>have interaction with the tables.
>
>Hope someone has an answer for this.
>
>Thanks very mucho.
>--=20
>Ing. Rodrigo Meneses
>Arquitecto de Sistemas
>Corporaci=F3n L'
>Tel: +58(212)267.4611
>Fax: +58(212)267.5395
>
>
>.
>

Neil
Tue Aug 19 14:49:17 CDT 2003

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



"Rodrigo Meneses" <rmeneses@NOSPAMcorpl.com.ve> wrote in message
news:O40esAdZDHA.2548@TK2MSFTNGP09.phx.gbl...
> 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
>
>