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?
