Deadlock On A Select Query, Possible?
Jan 31, 2005
I have a simple select query that selects data from a view. I consistently get a deadlock exception when running this query:
Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID #) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The view is a simple select statement that has WITH (NOLOCK) as a hint on all tables. I thought I understand how deadlocks worked, two threads are holding a lock and request the other's item. How does a read uncommitted select statement participate in this?
If I look at the other processes in the current activity and run sql profiler, there is no other activity and no existing locks at the time the statement is run.
Can anyone explain this? Or should I bounce my server and hope it never happens again?
Thanks,
Dave
View 1 Replies
ADVERTISEMENT
Feb 8, 2006
My SQL Server has kicked out a deadlocked process, which should only berunning a select statement, though there is another select on one ofthe tables in the WHERE clause (see code below). Can anyone tell mewhether this is possible, or is it that my system, which is re-usingconnections, is trying to complete an earlier statement? I've lookedthrough the system and think I'm committing all transactions.The query I'm running (simplified, I don't use daft names like 'table1'or 'date_col', honest :) is:SELECT table1.*, table2.*, table3.*FROM table1, table2, table3WHERE table2.col1 = table1.col1 AND table3.col1 = table1.col2AND table1.col3 = 'xyz'AND (table1.date_col >= '2006-02-01' OR table1.date_col IN(SELECT date_col FROM table1 WHERE (col4 = 'A' OR col4 = 'B') AND col3= 'xyz'))ORDER BY table1.date_colThanksJ
View 2 Replies
View Related
May 12, 2008
Hi ,
I have some issues with deadlock.I am getting deadlock becuase of two
select on same table. The sql server log is like this ,
Select statements should have always shared lock.I am not getting why
its doing exclusive lock and creating deadlock.
5/6/2008 12:38 spid4s Unknown Deadlock encountered €¦. Printing
deadlock information
5/6/2008 12:38 spid4s Unknown Wait-for graph
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:1
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594050117632 (8d036f07c58f)
CleanCnt:3 Mode Flags: 0×0
5/6/2008 12:38 spid4s Unknown Grant List 3:
5/6/2008 12:38 spid4s Unknown Owner:0×12E9F160 Mode: S Flg:0×0 Ref:1
Life:00000001 SPID:68 ECID:0 XactLockInfo: 0×353D1C54
5/6/2008 12:38 spid4s Unknown SPID: 68 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0×2FBB67F0 Mode: X SPID:112 BatchID:0 ECID:0 TaskProxy0×0792E378)
Value:0×38baa20 Cost0/11888)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:2
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594049986560 (6f02e1cd37c3)
CleanCnt:3 Mode:X Flags: 0×0
5/6/2008 12:38 spid4s Unknown Wait List:
5/6/2008 12:38 spid4s Unknown Owner:0×12396EE0 Mode: S Flg:0×2 Ref:1
Life:00000000 SPID:90 ECID:0 XactLockInfo: 0×0AA8178C
5/6/2008 12:38 spid4s Unknown SPID: 90 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0×353D1C30 Mode: S SPID:68 BatchID:0 ECID:0 TaskProxy0×13B3E378)
Value:0×12e9e780 Cost0/6164)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
If you have any idea regarding this please let me know ASAP.
Thanks in advance .
View 4 Replies
View Related
Jul 23, 2005
I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null
View 1 Replies
View Related
Oct 14, 2007
There are a series of traces of transactions for essentially the same units of work. Each includes a retrieving the same table "Select" from the database. The first 4 transactions ran concurrently. The last one ran about 7 minutes later with no other concurrent transactions.
The elapsed times for the database accesses for each of the first 4 is significantly higher than the last one.
Can you think of any database optimazation that might improve this?
Are there any utilities available than can be used for stand-alone testing of SQL Server database performance?
E.G. they would measure response time for reading/writing large/small amounts of canned data once/multiple times with concurrent/nonconcurrent access.
SQL Server Database is on 2000.I am not sure about the ISOLATION level in the Java code.
Basically I am new to SQL Sever....Please ans the above questions and provide me the direction to proceed on these questions
View 5 Replies
View Related
Nov 10, 2015
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
View 5 Replies
View Related
Jan 2, 2008
I am tring to execute the simple update query in query analyiser but after some seconds process got block by the same process(DEADLOCK).
query:
update tablename
set col1 = 0
Other info may help you:
Database had 3 different primary data files. In these files, first two files restricted file growth option is selected and there size is full. Only third file has space and option to grow.
Table containts total 50 columns and 15 milion rows.
Please help me to solve this ASAP.
Thanks in advance.
View 12 Replies
View Related
Sep 6, 2015
How to see text of the query involved in deadlock ?
View 2 Replies
View Related
Oct 22, 2015
I have a deadlock trace running on a production server that is filling up with the following:
Lock: Deadlock Chain
Parallel query worker thread was involved in a deadlock SPID 8
105 - Resource Type Exchange
Spid 8 is a SQL process LOCK MONITOR
There are no graphs as seen with typical deadlock, so it is not two queries that are deadlocking, and I've never seen a deadlock for "LOCK MONITOR"
View 0 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
Sep 6, 2006
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
Jul 9, 2002
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
View 1 Replies
View Related
Aug 22, 2006
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
View 6 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB Â Â Â Â 123
colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Oct 29, 1998
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..
select * from authors
OR
create procedure authors as
select * from authors
lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input
Ali
View 1 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Jan 28, 2008
Hi,
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005.
I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC
Whose result set is as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
On modifying the query to:-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC
I get the result set as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
I want the result set as follows:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above.
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
View 7 Replies
View Related
Apr 26, 2008
Hello everyone,Can you please help me on the following problem that i have.I am using the microsoft web stress tools to simulate 100 concurrent users for one min, and the database will go into a deadlock for large part of the 100 concurrent users.Please help...i've been stuck for many days already. Thanks...Error 5: Transaction (Process ID 497) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..Here is my code.My connection string:<connectionStrings><add name="Connection_String" connectionString="Data Source=123.123.123.123;Initial Catalog=DBName;MultipleActiveResultSets=true;User ID=uname;Password=upass;connection timeout=60" providerName="System.Data.SqlClient"/> </connectionStrings>My web apps will call the below function. And the deadlock always occurs at the update statement (error 5). I believe it's probably the select statement above locked up the row. I've put in (UPDLOCK) for the select statement, and it still didn't work.private void UpdateLogTables(string Station_ID, string ContentFileName, string FileSize, string IP){ContentFileName = Regex.Replace(ContentFileName, "'", "''");string AdFileName = "";string AdFileNameClause = "";string DownloadCount = "";string TotalFileSize = "";string DownloadDate = "";string currentDate = "";string tempDate = "";string MyString = "";SqlConnection MyConnection = new SqlConnection(System.Configuration.ConfigurationMa nager.ConnectionStrings["Connection_String"].ConnectionString);SqlCommand myCommand = null;SqlDataReader myReader = null;try{MyConnection.Open();MyString = "SELECT AdFileName FROM tblContentRef WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "'";myCommand = new SqlCommand(MyString, MyConnection);myCommand.CommandTimeout = 60;myReader = myCommand.ExecuteReader();if (myReader.Read()) {AdFileName = myReader["AdFileName"].ToString();} }catch (Exception e){Response.Write("Error 2: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");} finally{if (myReader != null){myReader.Close();}}if (AdFileName != ""){AdFileName = "'" + AdFileName.Replace("'", "''") + "'";AdFileNameClause = "AdFileName = " + AdFileName;}else{AdFileName = "NULL";AdFileNameClause = "AdFileName IS NULL";}InsertDetailsLogTable(Station_ID, ContentFileName, AdFileName, FileSize, IP, MyConnection);SqlDataReader myReader2 = null;SqlCommand myCommand2 = null;bool Readable = false;try{MyString = "SELECT DownloadCount, TotalFileSize, DownloadDate FROM tblSummaryLog (UPDLOCK) WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "' AND " + AdFileNameClause + " ORDER BY DownloadDate DESC";myCommand2 = new SqlCommand(MyString, MyConnection);myCommand2.CommandTimeout = 60;myReader2 = myCommand2.ExecuteReader();if (myReader2.Read()){DownloadCount = myReader2["DownloadCount"].ToString();TotalFileSize = myReader2["TotalFileSize"].ToString();DownloadDate = myReader2["DownloadDate"].ToString();currentDate = DateTime.Now.ToString("M/d/yyyy");string[] inDate = DownloadDate.Split(new char[] { ' ' });tempDate = inDate[0];Readable = true;}if (myReader2 != null){myReader2.Close();} if (Readable){if (tempDate == currentDate){DownloadCount = Convert.ToString(Convert.ToInt32(DownloadCount) + 1);TotalFileSize = Convert.ToString(Convert.ToInt32(TotalFileSize) + Convert.ToInt32(FileSize));UpdateSummaryLogTable(DownloadCount, TotalFileSize, Station_ID, ContentFileName, AdFileNameClause, DownloadDate, MyConnection);}else{InsertSummaryLogTable(Station_ID, ContentFileName, AdFileName, FileSize, MyConnection);}}else{InsertSummaryLogTable(Station_ID, ContentFileName, AdFileName, FileSize, MyConnection);} }catch (Exception e){Response.Write("Error 3: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{ if (MyConnection != null){ MyConnection.Close();} }}private void InsertDetailsLogTable(string Station_ID, string ContentFileName, string AdFileName, string FileSize, string IP, SqlConnection MyConnection){string MyString = @"INSERT INTO tblDetailsLog ( Station_ID, ContentFileName, AdFileName, FileSize, IP, DateTime ) VALUES ( '" + Station_ID + "', '" + ContentFileName + "', " + AdFileName + ", '" + FileSize + "', '" + IP + "', getdate() )";try{ SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);MyCmd.CommandTimeout = 60;MyCmd.ExecuteNonQuery(); }catch (Exception e){Response.Write("Error 4: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{} }private void UpdateSummaryLogTable(string DownloadCount, string TotalFileSize , string Station_ID , string ContentFileName , string AdFileNameClause, string DownloadDate, SqlConnection MyConnection){string MyString = @"UPDATE tblSummaryLog SET DownloadCount = '" + DownloadCount + "', TotalFileSize = '" + TotalFileSize + "', DownloadDate = getdate() WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "' AND " + AdFileNameClause + " AND (CONVERT(CHAR(19), DownloadDate) = CONVERT(CHAR(19), CAST('" + DownloadDate + "' AS datetime)))";try{ SqlCommand MyCmd = new SqlCommand(MyString, MyConnection); MyCmd.CommandTimeout = 60;MyCmd.ExecuteNonQuery(); }catch (Exception e){Response.Write("Error 5: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{} }private void InsertSummaryLogTable(string Station_ID , string ContentFileName , string AdFileName , string TotalFileSize, SqlConnection MyConnection){string MyString = @"INSERT INTO tblSummaryLog ( Station_ID, ContentFileName, AdFileName, DownloadCount, TotalFileSize, DownloadDate ) VALUES ( '" + Station_ID + "', '" + ContentFileName + "', " + AdFileName + ", '1', '" + TotalFileSize + "', getdate() )";try{ SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);MyCmd.CommandTimeout = 60;MyCmd.ExecuteNonQuery();}catch (Exception e){Response.Write("Error 6: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{} }
View 5 Replies
View Related
Jul 9, 2005
In a high traffic environment, deadlocks eventually occur as number of data processes increase. How can deadlocks be avoided, minimized and resolved. Please kindly provide scenario examples and samples of T-SQL code. Thanks much.
View 3 Replies
View Related
May 7, 2001
SQL 7.0
Hi All
Iam trying to bcp a table(residing on my prod server to my local machine from command prompt) .Actually the table iam trying to bcp has heavy updates and selects, from users (70 users).
The users complain that system becomes slow.Is it got anything to do with my trying to bcp the mentioned table(table has 170,000 records).Also whenever i try to bcp this table, only after being chosen as the deadlock victim by Sql server,for 3 or 4 times that iam able to bcp the table.
Any help regarding this will be very much appreciated
TIA
kinnu
View 2 Replies
View Related
Jul 27, 2000
I am unable to control the granularity of locks in our queries.
We are running queries through MTS and are getting deadlocks.
The batch includes two inserts and one select query - all are hitting on
only one table. This table has a unique clustered and a unique nonclustered index as well
as a primary key.
Within the batch, I have given a table hint to set transaction isolation level
to READCOMMITTED, ROWLOCK for the insert statements, like this
INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........
and the same for the Select statement.
SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)
When I run sp_lock on the spid, I get output indicating that
SS7 is placing a IX lock on the table. I'm pretty sure this is
a big contributor to the deadlock.
I get the deadlock when I try to run more than one client with similar insert parameters.
How can I control the granularity to just rowlocks?
All help is appreciated.
Thanks
Brett
View 4 Replies
View Related
May 12, 2000
How can I reproduce a deadlock in 7.0?
View 1 Replies
View Related
Nov 4, 2002
Hi,
I am getting deadlock running a stored procedure from two machines. Looking at the error log (generated using trace flag 1204 and 3605), it seems the deadlock is on a key. But what I fail to understand is how come sql server granted exclusive lock on the key to both connections. The grant list shows that lock with Mode X is granted to both connection.
Deadlock encountered .... Printing deadlock information
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Wait-for graph
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Node:1
2002-11-04 14:34:26.33 spid4 KEY: 11:1205683443:11 (da00d1f77328) CleanCnt:1 Mode: X Flags: 0x0
2002-11-04 14:34:26.33 spid4 Grant List::
2002-11-04 14:34:26.33 spid4 Owner:0x2ffa89c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:179 ECID:0
2002-11-04 14:34:26.33 spid4 SPID: 179 ECID: 0 Statement Type: SELECT INTO Line #: 44
2002-11-04 14:34:26.33 spid4 Input Buf: Language Event: EXEC dbo.PROC_RVM_CALCULATE 22567, 'centreAVashista'
2002-11-04 14:34:26.33 spid4 Requested By:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:166 ECID:0 Ec:(0x6f02568) Value:0x85146480 Cost:(0/14E0)
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Node:2
2002-11-04 14:34:26.33 spid4 KEY: 11:1205683443:11 (df001773794a) CleanCnt:1 Mode: X Flags: 0x0
2002-11-04 14:34:26.33 spid4 Grant List::
2002-11-04 14:34:26.33 spid4 Owner:0x85147ee0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:166 ECID:0
2002-11-04 14:34:26.33 spid4 SPID: 166 ECID: 0 Statement Type: SELECT INTO Line #: 44
2002-11-04 14:34:26.33 spid4 Input Buf: Language Event: EXEC dbo.PROC_RVM_CALCULATE 22562, 'centreJTtest4'
2002-11-04 14:34:26.33 spid4 Requested By:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:179 ECID:0 Ec:(0x7dd0568) Value:0x3a24f920 Cost:(0/39BC)
2002-11-04 14:34:26.33 spid4 Victim Resource Owner:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:166 ECID:0 Ec:(0x6f02568) Value:0x85146480 Cost:(0/14E0)
Any pointer is much appreciated.
Thanks
Shailesh
View 2 Replies
View Related
Dec 10, 1999
Help !!
How would you handle the deadlocks that happen often? Since the victim users complained about the disconnection.
Thank you ahead of time
View 2 Replies
View Related
Apr 27, 2004
Can anyone help me resolve deadlock with following text
Parallel Query worker thread Involved in deadlock.
I am particularly interested in resolving details of above mentioned line,as I started getting dead lock
more frequently now and when I look
into query involved blocking and victim
I see nothing that can cause deadlock
they are update insert and select statement which were fine for long and all of sudden started giving problem.
Thanks in advance,
for any knowledge share
my mail id scraval@hotmail.com
View 5 Replies
View Related
Aug 4, 1999
Hi,
When many users run some stored procedures I 've got some deadlocks. How to avoid that?
We run large stored procedures code which are using sometime the same table.
What is the best way for using the transaction isolation level, fillfactor indexes, procedure cache configuration ...etc to avoid that.
In addition, I am using MTS and sometimes the Tempdb is also locked, is it a Microsoft bug (again) ?
Herve Meftah
View 1 Replies
View Related
Mar 30, 2001
We had a dead lock every night 9:00pm. I found out Server/Current Activity --Object Locks :
The error log showing error 17824, severity:10, state 0 DNCC TRACEON 208, SPID 28 DBCCTRACEOFF 208, SPID 28 In current activity --object locks and reapetedly showing "tempdb.dbo.sysobjects/sysindexes/syscolumns" 28:sa.master.dbo /INSERT /SQL_servername (MS SQLEW)
Any help will be appreciated.
View 2 Replies
View Related
Apr 1, 2004
Hi
Sorry for bombading the forum with all these questions, but i am relatively new to sql 2000.
I am getting dead lock on the following procedure.
important background information
1. this is a multi user web-based call centre application
2. this procedure loads up a new contact based on priority
I see no reason how a dead lock could occur.
does any one have any idea. could it be something else that is locking up resource used by this procedure?
CREATE PROCEDURE topcat.getNewContactInfo
(
@contact_id int
)
AS
BEGIN
begin transaction
declare @id int
set @id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
UPDATE class_contact SET checked_in = 1 WHERE _id = @id
SELECT TOP 1 * FROM class_contact
WHERE _id = @id
commit
END
GO
wat i dont' get is that, this procedure only has one update statement, this is the only statement that could possibly hold a lock on another resource (i think) , i can't see how a dead lock can happen in this case since this procedure doesn't hold up 2 resources at a time.
James :(
View 6 Replies
View Related
Jun 19, 2007
Can anyone help me to solve this deadlock?
2007-06-17 22:42:34.18 spid4 Wait-for graph
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:1
2007-06-17 22:42:34.18 spid4 PAG: 66:1:26187 CleanCnt:1 Mode: IX Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Owner:0x69f19520 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:349 ECID:0
2007-06-17 22:42:34.18 spid4 SPID: 349 ECID: 0 Statement Type: UPDATE Line #: 1
2007-06-17 22:42:34.18 spid4 Input Buf: RPC Event: sp_prepexec;1
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:2
2007-06-17 22:42:34.18 spid4 PAG: 66:1:3051 CleanCnt:1 Mode: SIU Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 1::
2007-06-17 22:42:34.18 spid4 Owner:0x65c57560 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:348 ECID:33
2007-06-17 22:42:34.18 spid4 SPID: 348 ECID: 33 Statement Type: UPDATE Line #: 22
2007-06-17 22:42:34.18 spid4 Input Buf: Language Event: exec [Extracts].dbo.asp_SPCUpdateDRPFieldsIR @Name = 'SPCExecStep B05-ASP'
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:349 ECID:0 Ec:(0x5425B500) Value:0x674a2160 Cost:(0/5FD8)
2007-06-17 22:42:34.18 spid4 Victim Resource Owner:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)
=============================
http://www.sqlserverstudy.com
View 12 Replies
View Related
