Migrating to ADO.NET and locks

  • Thread starter Thread starter Tom Pester
  • Start date Start date
T

Tom Pester

I just started to work with ADO.NET and I had some problem with locks that
it caused on our sql server.
In classic ADO I didnt close the connection (sloppy of me I know) and it
didnt cause many problems.

After executing the following code in a ASP.NEt :

Dim conn As New SqlConnection("Data Source=(local);Initial
Catalog=xxx;user=xxx;pwd=xxx")
Dim sql As New SqlCommand("SELECT * FROM Page", conn)
conn.Open()
sql.ExecuteReader()

The following locks are itroduced :

============================================================================
======
Object Lock Type Mode Status Owner Index Resource
Spider DB S GRANT Sess
Spider.dbo.Page TAB IS GRANT Xact
Spider.dbo.Page KEY S GRANT Xact PK_Page (c8006e1f5864)
Spider.dbo.Page PAG IS GRANT Xact PK_Page 1:169
Spider.dbo.Page PAG IS GRANT Xact tPage 1:110
Spider.dbo.Page RID S GRANT Xact tPage 1:110:4
============================================================================
======

When I want to empty the Page table (delete from page) the command doesnt
success bacause of the locks
(I understand that Xact is a transaction

When I close the connection (conn.Close) I get a nicer picture regarding
locks :

============================================================================
======
Object Lock Type Mode Status Owner Index Resource
Spider DB S GRANT Sess
============================================================================
======

And everything works fine just like in the good old classic ado days.

so my question is : Why is it that with ADO.NET its much more important to
close the connection ?

It seems that a datareader starts a transaction automaticaly. Do I realy
need this?
 
so my question is : Why is it that with ADO.NET its much more important to
close the connection ?
Click to expand...

It was *always* a good idea in classic ADO and VB6 programming in
general to close any objects that you explicitly opened to prevent
memory leaks. In .NET, if an object has an Open method, it probably
also has a Close and possibly a Dispose method as well, which means
you should use one or the other when you're finished with the object
so that it can be cleaned up. One other reason it's important to close
a Connection object is that it never gets returned to the pool if you
don't, and therefore can't be reused.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
ADO.NET has a whole set of objects whose main purpose is to operate in a
disconnected mode and leaving connections open is totally antithetical to
everything ADO.NET. One of the first things I was told when I was learning
ADO.NET is to forget ADO, it'll only be a hindrance. If you leave your
connections open, and your userbase grows substantially, what benefit have
you created for youself? Other than saving a line of code here and there,
nothing. On the other hand, what do you lose? Well, you could easily limit
what users can do b/c people have open connections that aren't being used
for a set period of time. From a DB point of view, it's all downside.

IN addition, on a client server database like Oracle or SQL Server, they can
handle a large number of users, but if you have an Access database, you can
cause yourself all sorts of nightmares. Whereas Access can easily handle 20
users if only one of them or a few of them is hitting it simultanesouly,
you'll probably be in for a rough time if you were to leave all of those
connections open.

HTH,

Bill
 
As others have mentioned, ADO.NET is NOT ADO classic. Another big difference
is how the runtime handles objects that have fallen from scope. In .NET, the
garbage collector works more like the Chicago garbage haulers (when they're
on strike). You're used to having the VB runtime clean up after you when you
don't close Connections. With the .NET CRL, objects remain unclosed
indefinitely (until the GC decides it needs more memory) which can hold
connections open indefinitely. This leads to the connection pool filling up
and your application being unable to open more connections.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Many thx. Your 3 answers helped a lot.
I was indeed used to the runtime cleaning up after my asp.net pages
terminated.

I hava a few more question now though.

1) transaction for a simple select?

I found it surprising that a SQL select command ("select * from table" in my
case) used a transaction and effectivly locks a table. Since I was used to
the runtime cleaning up maybe this has slipped my attention! Was this with
classic ADO also the case (turns red)?

Is there maybe an option to disable an implicit transaction or must I look
at the isoltation levels to avoid this.

2) "With the .NET CRL, objects remain unclosed indefinitely (until the GC
decides it needs more memory) "

Indeed. I called the gc.collect() method (this forces the garbace collection
to kick in manualy) and what do you know? The locks disapeared.

I think this has been discussed a lot but why does the .NET framework not
prevent such mistakes. Forgetting to close a connection can happen and the
consequences are huge.

Maybe the answer is that its much more efficient to clean up once and a
while instead of every time an asp.net page ends. So the programmer has to
pay good attention at closing connections for the sake of garbage collection
efficiency. Is this the correct anwser.

Thx again Mary,William and Bill.
 
1) Sure. When you SELECT all the rows, as the SQL engine fetches the rows
they are locked temporarily until rowset population is complete using a
SELECT Lock. It's always worked this way. I rarely (virtually never) select
all the rows from a table unless it's a "lookup" table with only a few dozen
rows at most.

2) The CLR GC works as it does for good reason--the OO people complain that
it's the developers' responsibility to clean up and dispose of the objects
the instantiate--not the GC. What we got away with in VB in the old days was
sloppy. It's like living at home too long--you depend on your mom to pick up
and launder your clothes so you never really learn to do it yourself. It's
important to release the resources of any co-dependant object (like the
physical connection associated with a Connection object) yourself.

I discuss these issues in more detail in my book...

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thx for the reply Bill. I am beginning to understand how ADO.NET works :)

I think the book you are refering to is called "ADO.NET and ADO Examples and
Best Practices for VB Programmers, 2nd Edition".
Ive added it to my amazon wishlist :)
 
I have another question related to my last one:

what if something like this happened :

conn.open
Execute Select query that locks a whole table

some code where a CRASH occurs !

conn.close

The close method never gets called which leaves the locks on the table until
the garbage collecter kicks in (can be very long time)

Must I a try and catch error around every code that could prevent the
connection to be closed ??
 
I blinked, and Tom Pester said ...
I have another question related to my last one:

what if something like this happened :

conn.open
Execute Select query that locks a whole table

some code where a CRASH occurs !

conn.close

The close method never gets called which leaves the locks on the table until
the garbage collecter kicks in (can be very long time)
Click to expand...

If possible, move the close call to right after the execute. Normally
you shouldn't need to leave it open. However, you still need to account
for any potential error generated by the execute statement, so ...
Must I a try and catch error around every code that could prevent the
connection to be closed ??
Click to expand...

Yes, if you want your code to work properly under all conditions. Put a
close call in the finally block. Check the state before you call it.
 
Back
Top