Re: Connection.IsolationLevel = adXactSerializable and timeout

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 06/18/04

  • Next message: William \(Bill\) Vaughn: "Re: Connection suddenly closed" 
    Date: Fri, 18 Jun 2004 13:25:48 -0700

    Duplicate checking is often accomplished by being prepared for a failure
    when you add a new row--a Unique Primary Key index prevents duplicate rows
    from being added.
    Yup, I expect a Count(*) on a non PK column can result in a table lock, but
    it's easy to tell--run the Query Analyzer and look at the query plan.
    It sounds like you need a tutorial on Indexes. Once you have a Unique
    Primary Key index created, you can't add a duplicate row--simply do the
    Insert and be prepared for it to fail if there is a collision. An INSERT
    does not lock the table, but it will lock the row (possibly the page) and
    the index page being added for a brief instant.

    hth 

    -- 
____________________________________
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:%235KOx9TVEHA.3516@TK2MSFTNGP10.phx.gbl...
> 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>
  • Next message: William \(Bill\) Vaughn: "Re: Connection suddenly closed"

    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
      ... let's say we have a table named Account: ... So I create a transaction and wrap the SELECT and INSERT inside it. ... Doesn't INSERT automatically create a table lock? ... > 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: controlling lock order in transactions
      ... Andrew J. Kelly SQL MVP ... > ROLLBACK TRANSACTION; ... > find in the clients sp that could be causing the dead lock is the join on ...
      (microsoft.public.sqlserver.programming)