Re: tricky ado.net question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Angel Saenz-Badillos[MS] (angelsa_at_online.microsoft.com)
Date: 02/16/04 

Date: Mon, 16 Feb 2004 10:45:44 -0800

Just my opinion:
In most normal circumstances I would disagree with this.

Currently our commands will wait for thirty seconds to timeout, so the
transaction will have to be active for at least that long. This is an
outrageous amount of time to have a transaction active during production and
you should take a hard look at what you are doing that is taking too long.
Make your update logic as tight as possible, open the connection as late as
possible and close it as soon as you can and make your transactions as
atomic as feasible.

If your scenario is not a "normal" production scenario, that is if you are
doing admin work or bulk copying large amounts of data in pre-production
take a look at the NOLOCK Tsql Statement. You can also use the
ReadUncommited isolation level, but not the way you tried it. ReadUncommited
iso level places the same locks as ReadCommited, what makes it special is
that it ignores locks placed by other transactions. So if lock a table and
update values while in one transaction I can, in a different connection,
start a readuncommited iso level transaction and look at the values updated
by the first transaction. This is not something I would use in production.

Happy programming. 

-- 
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:uRXU4cJ9DHA.1632@TK2MSFTNGP12.phx.gbl...
>
> "wrxguru" <wrxguru@iprimus.com-dot-au.no-spam.invalid> wrote in message
> news:402ff4b3$1_2@Usenet.com...
> > Yes, my update spans several tables and the problem is I have inserted
> > records into the product table but when i use another connection (via
> > a sql reader as i have general functions to lookup for the latest
> > item id for example) to see the product table i get locked out
> > because the transaction has got the whole table locked no matter what
> > isolation i use.  i thought another connection would be able to see
> > the table at least to read if the isolation level was read
> > uncomitted?
> >
>
> Looks like your real problem is that you are using multiple connections.
> You need to restructure your DAL methods so they can all share a
connection
> for the duration of your transaction.  You can read through the locks with
> certian settings on your reading transaction, but you really don't want
to.
> What if another connection had made uncommited changes to the data you are
> querying?
>
> David
>
>

Relevant Pages

  • Lock confusion
    ... I created a test case to exemplify an issue I'm having in a production ... ExecuteNonQuery and before Commit for the update transaction so that I ... In the 2005 studio manager the "Locks by Object" for the Color table ... All of the locks are of type KEY. ...
    (microsoft.public.dotnet.framework.adonet)
  • 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)
  • TransactionScope Transactions not commiting
    ... be) using the same SQL Connection. ... and dtermines the Connection String to use to create the connection to ... MyClass obj = new MyClass; ... not involved in a Transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, I need to install the path sqljdbc_xa.dll and xa_install.sql before using the XA driver. ... I created a TX aware datasource using com.microsoft.sqlserver.jdbc.SQLServerDriver and tried to get the connection from the TX aware data source. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)