Migrating to ADO.NET and locks

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?
 
N

Natty Gur

Just a try ...

If your DB component register as COM+ supporting transaction, COM+
starts transaction for you with serializable isolation level. you should
change it. refer to Q-215520.

Natty Gur[MVP]
Phone Numbers:
Office: +972-(0)9-7740261
Fax: +972-(0)9-7740261
Mobile: +972-(0)58-888377
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top