Re: Connection.IsolationLevel = adXactSerializable and timeout

From: Jiho Han (jiho.han_at_infinityinfo.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 10:20:42 -0400

Thanks Bill, you're a champ! Before I go out and get your book ;) - I have
your ADO book btw -, can I ask you a few questions regarding the resource
contention issues?
I may be lacking some basic knowledge regarding the topic and so if you can
think of any references including your books, please feel free to let me
know.

This a really basic scenario but one that is common in almost all
data-driven app.
Basically, let's say we have a table named Account:

Account
-----------
AccountID
AccountName
AccountType
AccountStatus

And I want to insert a row into this table with an AccountName "ABC Corp."
but first I want to check that there isn't one already.
So what I do is run two queries, a SELECT to see if the record exists, and
INSERT to insert if not exists.
But there is a chance of somebody inserting the same record into the Account
table right after the SELECT query but before the INSERT, resulting in a
duplicate record being created.
So I create a transaction and wrap the SELECT and INSERT inside it. This
way, another process cannot "interject" until the whole thing is done.
My SELECT query then is something like:

SELECT COUNT(*) FROM ACCOUNT WHERE ACCOUNTNAME = 'ABC Corp.'

And my INSERT is the standard INSERT...VALUES type.

So then my questions are:
1. Does the select query use the table lock(or page lock), or a row lock(or
locks)?
2. Doesn't INSERT automatically create a table lock?
3. I am currently using:

Connection.IsolationLevel = adXactSerializable

in my code. Is this correct for this simple scenario?

Thanks

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:eFQplxKVEHA.2520@TK2MSFTNGP12.phx.gbl...
> You are mostly correct as to the ConnectionTimeout--it's the time taken to
> get the server to answer that a connection is available (essentially).
Once
> the connection is pooled, this value is used to determine how long to wait
> for a valid pooled connection to become available. However, connection
> pooling is implemented by the DataAccess provider so each behaves
(somewhat)
> differently.
>
> CommandTimeout only applies to the execution of the query itself--this
> assumes a connection is already open.
>
> The behavior you're seeing (if I understand you correctly) is normal. When
> you execute any transaction that updates rows it's normal (and desirable)
to
> lock rows, pages (or even tables) while the transaction is working.
> Depending on the approach you're using, those locks can be held for quite
> some time--this is not a characteristic of scalable design. One problem
you
> might be seeing is "deadly embrace" where one transaction is holding
> resources and waiting for other resources being held by another competing
> transaction. These resources might be rows/pages from the same table or
> simply index pages.
> Scalable designs are built around small, focused operations that affect
very
> few rows and are executed very quickly as a result. These result in far
> fewer lock contentions. Usually, the server does not escalate to a table
> lock but you can query SP_lock to see what's going on. If you are getting
> table locks, you're clearly trying to impact too many rows at once. You
> might have chosen the wrong cursor type as well.
>
> I discuss these issues in far more depth in my book on ADO.NET.
>
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> 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.
> __________________________________
>
> "Jiho Han" <jiho.han@infinityinfo.com> wrote in message
> news:u5LleZKVEHA.1036@TK2MSFTNGP12.phx.gbl...
> > Ok, this seems to have fallen out of the thread but I am still
> experiencing
> > this but I have new information.
> >
> > Bill (Vaughn),
> >
> > To answer your question, it is SalesLogix(CRM software). They
implemented
> > their own OLE DB Provider.
> >
> > I need to be more specific here. I recently found out(or rather
noticed)
> > that ConnectionTimeout and/or even CommandTimeout may not help me since:
> >
> > 1. ConnectionTimeout only specifies the timeout setting for the initial
> > connection.
> > 2. CommandTimeout only specifies the timeout setting for .Execute
> method(of
> > connection or command).
> >
> > Please correct me if I'm wrong with the above assumptions.
> >
> > The problem that I see happening is whenever the sql statements -
whether
> > they are SELECT, INSERT, UPDATE or DELETE - are inside a transaction. I
> > have two processes competing for the same resource. They both use
> > transaction to:
> >
> > - retrieve a value and depending on the value, insert/update - it's most
> > likely using table lock - a particular row.
> >
> > So while one row is modifying the table, the other process is held up.
> Now
> > if the first process were experiencing some trouble and were *stuck*
while
> > in transaction or somehow, the table were not freed for some reason, I
> want
> > the second process to quit trying with an error after x number of
seconds.
> >
> > If not the 2 timeout properties, how would I achieve this?
> >
> > Thanks
> >
> > "Jiho Han" <jiho.han@infinityinfo.com> wrote in message
> > news:OKKiA9IKEHA.2660@TK2MSFTNGP09.phx.gbl...
> > > I have two processes that must check on a single table row for access
> > rights
> > > to other table records.
> > > I am using Connection.IsolationLevel set to adXactSerializable to
> achieve
> > > this since I found no other way to do this (SQL server lock hints are
> not
> > > supported by my provider).
> > > So whichever opens a transaction will have the lock and update the
mutex
> > row
> > > and proceed with its transactions. When it completes, the other
process
> > > then locks it using the same procedure.
> > >
> > > My problem is that the waiting process seems to wait indefinitely no
> > matter
> > > what setting I use for ConnectionTimeout or the CommandTimeout. I am
> > using
> > > Connection.Execute to retrieve this mutex row at the beginning of the
> > > transaction and hold it until the end of the transaction. The ADO doc
> > > states that the timout settings control the when the error occurs due
to
> > > "delays from network traffic or heavy server use". Does this not hold
> > true
> > > for SQL server holding the lock as well?
> > >
> > > Can someone tell me how to proceed?
> > > Thanks much.
> > >
> > > Jiho
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Setting Locks on Rows in MSSQL Database (Currenlty Unsolvable Problem and Trying)
    ... I am beginning a transaction from PHP and setting a lock on a record ... I then commit the transaction I started when the user clicked ... There is no way in PHP where I can start a persistent ms sql connection ...
    (alt.php)
  • Re: Connection.IsolationLevel = adXactSerializable and timeout
    ... Yup, I expect a Counton a non PK column can result in a table lock, but ... let's say we have a table named Account: ... > So I create a transaction and wrap the SELECT and INSERT inside it. ... >> get the server to answer that a connection is available. ...
    (microsoft.public.data.ado)
  • Re: Connection not timing out
    ... Are you waiting for the connection to close ... > I am using Connection.IsolationLevel set to adXactSerializable to achieve> this since I found no other way to do this (SQL server lock hints are not> supported by my provider). ... > My problem is that the waiting process seems to wait indefinitely no matter> what setting I use for ConnectionTimeout or the CommandTimeout. ... I am using> Connection.Execute to retrieve this mutex row at the beginning of the> transaction and hold it until the end of the transaction. ...
    (microsoft.public.data.ado)
  • Re: Problem: No Network Connections under Guest Account
    ... The Guest Account on my other computer seems to ... Sounds like you might have more of an issue with your network than with the ... network connection settings. ...
    (microsoft.public.windowsxp.security_admin)