Re: Removing deadlock
From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 07/02/04
- Next message: Bonjour: "Re: Checking existing data"
- Previous message: Wayne Snyder: "Re: Applications sharing a common table"
- In reply to: Viatcheslav V. Vassiliev: "Removing deadlock"
- Next in thread: Wayne Snyder: "Re: Removing deadlock"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 2 Jul 2004 08:22:03 -0400
There are several things you might do.
1. Select * from table(nolock)
This allows the selector to see data which has been changed in the other
transaction (even though that trans might be rolled back..) Make sure this
makes business sense.
2. Select * from table(readpast).
this simply excludes the locked rows from your rowset... You just do
not see any rows which are locked for insert/udpate.
3. You can bind the 2 connections together so SQL sees them as the same lock
space ( Search for bindtoken in books on line.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Viatcheslav V. Vassiliev" <msnewsgroup@www-sharp.com> wrote in message
news:OYg0dPCYEHA.4008@TK2MSFTNGP09.phx.gbl...
> Could MS SQL Server be setup to have no deadlock in this code (insert in
> transaction from one connection and select from another without closing
> transaction):
>
> //-------------------------------------------------------------
> var connStr = "Provider=SQLOLEDB;Integrated Security=SSPI;Initial
> catalog=XXX";
> var conn1 = new ActiveXObject("ADODB.Connection");
> var conn2 = new ActiveXObject("ADODB.Connection");
> conn1.Open(connStr);
> conn2.Open(connStr);
>
> var rs = new ActiveXObject("ADODB.Recordset");
> var cmd = new ActiveXObject("ADODB.Command");
>
> conn1.BeginTrans();
> cmd.ActiveConnection = conn1;
> WScript.Echo("Inserting");
> cmd.CommandText = "INSERT INTO Table1 VALUES('Example')";
> cmd.Execute();
> cmd = null;
>
> WScript.Echo("Selecting");
> //Deadlock is here
> rs.Open("SELECT * FROM Table1", conn2);
>
> rs.Close();
> rs = null;
>
> conn1.RollbackTrans();
>
> conn1.Close();
> conn1 = null;
>
> conn2.Close();
> conn2 = null;
>
> WScript.Echo("Done");
> //---------------------------------------------------------------
>
> Best regards,
> Viatcheslav V. Vassiliev
>
>
- Next message: Bonjour: "Re: Checking existing data"
- Previous message: Wayne Snyder: "Re: Applications sharing a common table"
- In reply to: Viatcheslav V. Vassiliev: "Removing deadlock"
- Next in thread: Wayne Snyder: "Re: Removing deadlock"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
