Consistent Deadlock, need to identify the rogue PAGE lock

I'm having trouble tracking down a deadlock in a large multi-threaded application. I have been able to gather some information, but not quite enough to identify the two queries at fault.

I collected the following by turning on the -T1204 and -T3605 parameters:

Deadlock encountered .... Printing deadlock information
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Wait-for graph
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Node:1
2006-04-13 09:13:48.70 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.70 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5b80 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:0 Ec:(0x1B4E54F0) Value:0x1a0d5ae0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:2
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14898 CleanCnt:2 Mode: U Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 0::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6bc0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:58 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: IU SPID:61 ECID:0 Ec:(0x1C2B54F0) Value:0x1a0d5440 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:3
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14899 CleanCnt:2 Mode: IX Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6e80 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:55 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:4
2006-04-13 09:13:48.71 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.71 spid3 Wait List:
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5ae0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:53 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:0 Ec:(0x1BFB94F0) Value:0x1a0c6fc0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3 Victim Resource Owner:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)

I then ran the following queries: -

> select name from sysobjects where id=1993058136;
result: ACCOUNTARRANGEMENTSCHEDULES

> select name from sysindexes where indid=1 and id=1993058136;
result: PK_ACCOUNTARRANGEMENTSCHEDULES

> sp_help PK_ACCOUNTARRANGEMENTSCHEDULES
result: PK_ACCOUNTARRANGEMENTSCHEDULES , dbo , primary key cns , 2005-11-01 13:51:01.650

It looks like there is sql server selected a row lock for the ACCOUNTARRANGEMENTSCHEDULES table (sounds good), but for the UPDATE being done by nodes 2 and 3 a page lock is being used. Is there any way I can get more info on what the statement is or what table it is updating?
I found some more useful info, using the following command: -

dbcc traceon (3604)
dbcc page(10, 4, 14898,2)
dbcc traceoff (3604)

I received the following (amongst other things): -
m_objId = 1993058136

Which would indicate the page lock is on the same table (ACCOUNTARRANGEMENTSCHEDULES).|||

The page that the deadlock occurs on has little to do with what led to the deadlock.

This is the deadlock scenario:

Spid 61 -> owns KEY: 10:1993058136:1 (aa0069f30857)
-> requesting 10:4:14898
Spid 58 -> owns 10:4:14898
-> Requesting 10:4:14899
Spid 55 -> Owns 10:4:14899
-> requesting 10:1993058136:1 (aa0069f30857)

So spid 61 used an index to find a row it was interested in and the needed additional data off page 10:4:14898.

Spid 55 appears to have found a row it intends to update on page 10:4:14899, and now needs a key lock to perform the necessary index maintenance.

Spid 58 appears to be scanning as it is requesting the page with the next ID, which likely just happens to be the next page in the list.

All 3 of these spids are exectuting some kind of prepared statement that does an update, as evidenced by the "sp_execute" statement in the input buffer.

To get to the bottom of this you're going to need to look at the exectution plan of these statements. Use profiler and gather the showplan all, sp:stmtstarting, sp:stmtcompleted, rpc starting and rpc completed events. If the plan shows (as I suspect it will) something like CLUSTERED INDEX SCAN, then you need to think about proper indexes to support these statements.

|||Unfortunately, whenever I use the profiler (even if I only turn on Deadlock and Deadlock Chain events) it seems to change the timings enough that the deadlock is not produced.

I don't know how I can change the indexes. The table has a primary key, which is a composite of two columns. Since a primary key exists, why is a page lock being used? The only thing that I can think that could be causing it, is the fact that an updateable jdbc ResultSet is being used by one of the queries. So it's not strictly an UPDATE, it's a SELECT (that selects only 1 row) then an updateRow.

This deadlock only appeared when introducing sp4. With sp3, no deadlocks occur. Has the lock selection algorithm changed?
|||The lock selection algorithm has not changed. It's possible that you have a different plan now. Even if you don't hit the deadlock the plans themselves can be instructive. If you see Scan's in the plan you want to understand why. Of course a Scan is going to cause each page in an index to be read and locked. The fewer pages that are locked the fewer chances there are of encountering a blocking, or deadlocking scenario.

ConnectionString Property

I am having trouble initializing my connection. This is the code:

DimDBConnPhone As NewSqlConnection(ConfigurationManager.AppSettings("DBConnPhone"))

Dim DBConnClient As NewSqlConnection(ConfigurationManager.AppSettings("DBConnClient"))

Dim Sqlcomm1 As New SqlCommand

Dim Sqlcomm2 As New SqlCommand

DBConnPhone.Open()

DBConnClient.Open()

Once I start debugging, it stops and give me the error "The ConnectionString Property was not initialized" Any suggestions?

If you want to retrieve connection strings, you should use ConfigurationManager.ConnectionStrings property, not ConfigurationManager.AppSettings. So the code changes to:

Dim DBConnPhone As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnPhone"))

Dim DBConnClient As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnClient"))

...

|||I did that and it said that Value of type 'System.Configuration.ConnectionStringSettings' cannot be converted to 'String'|||Dim DBConnClient As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnClient").ConnectionString)|||I didn't get any errors with that, but I also did not see where the newrecord was inserted into the table. I got to check my code some more.Thanks

Connection with Sql 2000

Hi,
My client is using window's 95 but we have SQL 2000, we are getting trouble to connect with SQL 2000 from Window's '95 operating system.If you dont mind can you elaborate on what error exactly you are getting ? and what exactly you are trying to do ?

Thanks.|||Originally posted by Decastod
If you dont mind can you elaborate on what error exactly you are getting ? and what exactly you are trying to do ?

Thanks.

Actually I have Widows'95 operating system and with that ay application tried to connect with SQL 2000, but it is giving me error and connection failed.|||Like Decastod stated earlier, the question is a little vague. A couple of things:

1) Can you ping the Server by name and/or ip address
2) Is the client utilities for SQL Server installed on the client PC
3) If the client utilities are installed verify the Enabled Protocols using the 'Client Network Utility'

Connection Using Gobal ASP

I am having trouble connecting to my SQL Server. I am using InterDev and mod
ifing the gobal.asp.
This is the code I am using
application("DBConn")="Provider=SQLOLEDB;User ID=sa;Initial Catalog=northwin
d;Data Source=192.168.20.17;PASSWORD=;"
Is this correct? If not what should I modify?What errors do you receive? That might help us provide an answer.
First off, I would not use the 'sa' account for a web app.
Create a separate SQL Server login (with limited permissions) and use =
that.
Secondly, you should probably have a password on the account that you =
are using. I am guessing that you may have left that piece out of your =
post because you did not want to tell us your sa password.
Thirdly, do you want the initial database to be northwind?
--=20
Keith
"Jack Temple" <anonymous@.discussions.microsoft.com> wrote in message =
news:9A428A01-1348-4F81-B46B-511CC98ACF0B@.microsoft.com...
> I am having trouble connecting to my SQL Server. I am using InterDev =
and modifing the gobal.asp.
>=20
> This is the code I am using=20
>=20
> application("DBConn")=3D"Provider=3DSQLOLEDB;User ID=3Dsa;Initial =
Catalog=3Dnorthwind;Data Source=3D192.168.20.17;PASSWORD=3D;"
>=20
> Is this correct? If not what should I modify?
>=20
>

connection string using sql authentication

i'm having trouble getting my project to work on other machines using windows authentication, so as this is urgent I want to change it to sql authentication. I've enabled sql authentication and enabled the sa login, could someone please tell me how to connect to my db

my connection string is currently as follows using windows authentication:

<

connectionStrings>

<

addname="GuitarShackConnection"connectionString="Server=(local)\SqlExpress;Integrated Security=True;Database=GuitarShack;"providerName="System.Data.SqlClient"/>

<

addname="GuitarShackConnectionString"connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=GuitarShack;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="CustomerNameDS"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

</

connectionStrings><add name="ConnectionString" connectionString="Server=.\SQLEXPRESS;Database=xxxxxxxxxx;User ID=xxxxxxx;Password=xxxxxxx;Trusted_Connection=False"
providerName="System.Data.SqlClient" />|||

i've enabled sa and restarted the sql service but when i go to modify the connection in the database explorer and enter the credentials it returns an error when i test the connection:

the user sa is not associated with a trusted sql connection

any ideas what would cause this?

|||The full ins and outs of Sql Server permissions are beyond me, because I find it difficult to remember what user can do what. So I administer my servers using Windows Authentication on the local machine, set the Server up to operate in Mixed Mode, then create application users. I give these users the absolute minimum permissions possible, which is only actuallyExecute on the stored procedures I create for the app. They have no SELECT, DELETE, UPDATE or INSERT permissions. I know that you must NEVER use the sa account to connect to SQL Server from your ASP.NET app. That user has every privilege going.|||

Hi, the follow steps may be helpful to you.

To change security authentication mode
1. In SQL Server Management Studio Object Explorer, right-click your server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK, to acknowledge the need to restart SQL Server.

To restart SQL Server from SQL Server Management Studio
1. In Object Explorer, right-click your server, and then click Restart. If running, SQL Server Agent must also be restarted.

To enable the sa login
1. Execute the following statements to enable the sa password and assign a password.
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<password>' ;
GO

Thanks.

Connection string and external web host.

Hey guys, I'm havnig a bit of trouble setting up my sql reporting tools. My webhost's technical support either does not know what they're doing or might just not care. They 've been unelpful so I'm resorting to asking a stupid noob question again. I hope you guys can help me out.

On my home pc, all i woul have to input is

Data Source=mycomputername\sqlexpress;AttachDbFilename=c:\document and settings\administrator\mydocuments\test8_data1.mdf;Initial Catalog=test

and it would work.

However, when I upload thist stuff to my host and in their admin pages I entered various strings and none of them work.
For the data source I've tried :

Data Source = ./sqlexpress
data source = server ip
data source = server ip/sqlexpress
data source = sqlexpress

as for attachdbfilename I've tried
|datadirectory|\test8_data1.mdf
full path to test8_data1.mdf
and just test8_data1.mdf

In addition to that, in order for this to work, do I need to both input the connection string on the server as well as the .RDL file I upload? I assume both settngs need to be identical for it to work.

Unfortruantely after trying all this,

"An error has occurred during report processing.
Cannot create a connection to data source 'test'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors "

1) Chane hosting company :) Try CrystalTech, Webhost4life - both have great support and will always help you solve problems like that

2) You will find all info about connection strings here

http://www.connectionstrings.com/

3) What you have is database file (from sql express) - most hosting companies DO NOT support it, but they should offer some tools to copy it/attach it to full SQL server. Or you can create backup and ask them to restore it for you - after that go to website i gave you and it will work :)

|||

the irony, I use webhost4life.

I sent over 5 helpdesk tickets about what would go into datasource, and I got answers such as, you can input that in the report manager. which wasn't the question.

On my computer I have the computer name so I can easily just do servername\sqlexpress, but i've tried everythign and it won't work on their server.

As for item 3, I did use sqlexpress for my .mdf file , created a backup and restored it into webhost4life's sql server. So that isn't an issue.

|||

With Webhost4life

- login to your acc.

- go to "database" top menu

- click on "MSSQL Admin" left menu

- and you should see all your SQL databases there wit the IP and login ingo you should use to connect to it

Your connections string will look like that

connectionString="Data Source='sql332.mysite4now.com';User ID='USER_HERE';Password=PASSWORD_HERE;database='DATABASENAME_HERE'"

change sql332.mysite4now.com to your sql server

and that is it

|||

hey tom thanks very much. Still having some issues, if you don't mind I'd like to request some more advice.

1) My current status is, went into sqlreport01.mysite4now.com/reports... and created a data source and used that as a shared data source and also inputed the info for my .rdl file
i've also, tried just deleting the shared data source and used a custom datasource and my .rdl report file.

currently its listed as.

"Data Sources="Data Source='sql380.mysite4now.com';User ID=' my username ';Password=' my password ';database='bkt_data.mdf'""

on the site,

and

<DataSource Name="test">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Sources="Data Source='sql380.mysite4now.com';User ID=' my username ';Password=' my password ';database='bkt_data.mdf'";</ConnectString> <DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>df397291-cd99-460b-9468-1324b2143105</rd:DataSourceID>
</DataSource>
</DataSources>

in the .rdl file

I've tried both ways and they both don't seem to work.

bkt_data is a sql2005 database that i created from their site.

So now after trying your advice, I realized that the message

"

An error has occurred during report processing.

Cannot create a connection to data source 'test'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

can you access your database from EM ?

|||

I actually don't have EM i beleive. I'm assuming that comes with sql server. I only have express edition. However like i stated, the database itself was loaded into a sqlserver database from the control panel. Do i need eM?

|||scratchthat, microsoft has an web version of smoethign similair to em and I'm using it. It works, I can connect to the database. so then I wonder what the problem is.|||ok and what is the database name there ?|||

now i'm not even sure what you are using database file in app_data folder ? or you uploaded db from backup ?

if file in app_data folder read this :

http://www.webhost4life.com/kb/question.php?qstId=153

|||

my db name is bkt and its the same from the tool as well as the database tab in the control panel.

also, I restored db from backup.

|||

it is late :)

anyway if your database name is "bkt" why are you using "'bkt_data.mdf'" as a database name in connection ???

"Data Sources="Data Source='sql380.mysite4now.com';User ID=' my username ';Password=' my password ';database='bkt_data.mdf'""

|||my apologies. My database is named bkt, but the file name that webhost4life gave it is bkt_data.mdf
I'm gonna try it again later today and see if it works. thanks for all your help. I really appreciate it.|||

I misread what you had wrote up there so here is the revised script in my rdl file. I noticed it automatically makes a custom data source on the report manager side.

<ConnectString>connectionString="Data Source='sql380.mysite4now.com';User ID='user';Password='password';database='bkt'";</ConnectString>

still does not seem to work. Is there a simple way to test if the report manager can even access sql380.mysite4now.com?

|||Hey tom, got it to work. Thanks for all your help. I usually print out my reports with .16 margins all around, but there no setting for that here. Is that something I can input into the .rdl files?