Last week one of our processes starting issuing or suffering deadlock
detected errors every 15 minutes or so.
I have read several articles at MS on the subject. I set a couple of
startup parameters related to producing deadlock detection information
and ran SQL Profiler. I found the SQL statements being issued by the
deadlocked statements. In every deadlock the same UPDATE statement
appears however the data values being searched on are different. The
best I can tell from trying to query the actual data each update hits
only one or very few rows. No indexed column is updated so the indexes
should not be the source of conflict.
Looking at the query I noticed that the query does not have an
available index and Query Analyzer shows that the full table scan is
being done in parallel.
My question: Does SQL Server change or modify its locking rules when
queries are converted to be ran using parallel processing? If so, do
you have a reference?
Here is the deadlock entries posted to the error log:
SPID=167
ResType:LockOwner Stype:'OR' Mode: IX SPID:63 ECID:0 Ec:(0x65971510)
Value:0x3c577e60 Cost:(0/0)
Input Buf: Language Event: UPDATE Station_Upload set
Station_Accept_Status = 'ACC',HeadStatus =
'ACC',LastProcessedSta='110',HeadPartType='1' WHERE Part_Serial_No =
'SCH1119323' AND Station = 'H110'
SPID=63
ResType:LockOwner Stype:'OR' Mode: IX SPID:167 ECID:0 Ec:(0x65801510)
Value:0x3c27d060 Cost:(0/0)
Input Buf: Language Event: UPDATE Station_Upload set
Station_Accept_Status = 'ACC',HeadStatus =
'ACC',LastProcessedSta='70',HeadPartType='1' WHERE Part_Serial_No =
'SCH1119060' AND Station = 'H070'
I have suggested adding an index to support the query.
I am facing some problem's while using the FOR loop container to execute 7-10 packages in parallel.
The main package has 7 FOR loop containers say F1-F7.
Each FOR loop container has 2 task's
T1==> exec child package C1
T2==> exec delay task Delay1.
The idea is to run child packages c1-c7 in parallel ...delay for some time and then run again since there are in the FOR loop container.
I am facing someproblems.
1. The execution of tasks T1-T7 is not guranteed. This means SSIS picks up any 6 tasks of T1-T7 randomly to start with. 6 is the max it processes whereas i have more than that. Can i change this setting???
2. Its not guranteed that if say Task t1 of FOR loop F1 is executed the subsequent task for Delay within tat For loop would be executed next. Typically wat happens is it starts with T1-T6 (T7 onhold) and then exec the delay for T1-T5 and passes control to T7 without going into the delay for T6.This is not the intended execution.
What i want is exec T1-t7 ..delay for the next exec and start again.
I create a main program which will launch two jobs at a time, each job does some processes and at the end I'm trying to delete those jobs after storing the job details in one of the custom table I created (cleanup sub-program).
Out of two jobs I am able to store one job details (like job_name,job_id,start_time and end_time of the job) in the custom table and able to delete that job, but the job that's getting completed at the end is not getting captured nor getting deleted from sysjobs and sysjobhistory tables.
I had included this step (which will call the cleanup sub-program to store the job details and delete it) at the end. I can see that this cleanup procedure getting called from debug message but it is neither storing details nor deleting the job.
When I execute this cleanup program separately, it does store the job details and delete it.
I've got an SSRS report that is set up using a data-driven subscription to supply input parameters to the stored procedure that is called to generate the report results.
I was wondering if there is any way to specify the execution processing method (running the reports in parallel or serially). The subscription that we have set up appears to be running all of the reports in parallel which is causing massive load on our servers.
I am creating an SSIS Script Task that will be used to process SSAS dimensions and partitions and ideally log the details of each in a table. Any info on the benefits or drawbacks of using the built-in SSAS parallel processing as opposed to doing it manually in a multi-threaded "Parallel.Foreach" loop using the .NET AMO library.
In my testing, when I use a Parallel.foreach loop, I am able to obtain and log information about the object such as end time and time to process immediately after each object is processed. Â This allows me to keep a history of processing time for each object:
public void processDimensions(Server Server, Database Database, ProcessType processType) { Parallel.ForEach(Database.Dimensions.OfType<Microsoft.AnalysisServices.Dimension>(), d => { DateTime beginTime = DateTime.Now; try { d.Process(processType);
[code]....
If circumventing the built-in SSAS parallel processing is not best practice I'd like to know in advance before I go too far down that path.
I'm having some deadlocking issues in my ASP.Net 2.0 web application with SQL Server 2000. One solution I've come across involves catching the the deadlock error (at the application level) and then retrying the query again, in the hopes that whatever was causing the deadlock is no longer locked.
I've tried to implement such a solution, where I catch the deadlock exception, then try to execute the same query again. Only now I'm getting a "The transaction is in doubt" exception.
So am I going about this wrong? Am I supposed to retry the entire transaction, or is valid to just try to redo the one query that it failed on? Is there some way around the transaction being in doubt?
I have another post here regarding SQL 2005 running a query 50% slower than on 2000. It was discovered that 2005 runs the query in series whereas 2000 runs it in parallel.
Even with "Cost Threshold For Parallelism" set to a default value – 0, 2005 still executes my query in series. Does anyone know how to force a query to run in parallel in SQL 2005. I specifically want to set it at the database level.
I have a SQL 7 db with a union query (view), and I'm getting the error, "Thequery processor could not start the necessary thread resources for parallelquery execution." This union query has been in place for about two years nowwith no problems until just now, though I haven't changed anything. Also, Ihave a local copy of the database on my machine, and the query runs fine.As noted, I haven't changed anything in the query, nor in the SQL settings.There is a network administrator, so it's possible that he may have changeda setting, but I don't know what. The query is reproduced below. Any ideasas to what's going on would be appreciated.NeilMain query:SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,INVTRY.HoldInitFROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,SDPAID, SDPRICE, SDCOPIES, 'P' AS LocationFROM vwInvoiceDetUNION ALLSELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,SDPAID, SDPRICE, SDCOPIES, 'N' AS LocationFROM vwInvoiceDetNUNION ALLSELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,SDPAID, SDPRICE, SDCOPIES, 'M' AS LocationFROM vwInvoiceDetM) Tmp INNER JOINdbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]vwInvoiceDet:SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDIDFROM dbo.tabInvoice INNER JOINdbo.SALEDET ONdbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR(vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)
I am running a pivot query on approx 13000 records. When I run it on the whole dataset it timesout after 30 seconds but when I run it on 6500 (half at a time) it completes in about 2 seconds.
I am running a pivot query on approx 13000 records. When I run it on the whole dataset it timesout after 30 seconds but when I run it on 6500 (half at a time) it completes in about 2 seconds.
(I am running SQL Server 2005 through the SQL Server Management Studio on Windows 2003 R2)
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
I'm facing a strange problem.. I've developed few reports. they are working fine in develop environment. after successfull testing they were published on web. in web version, all reports are executing for first time.. if I change any of parameters values or without chaning also.. if I press "View Report" following error occurs..
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'dsMLGDB2Odbc'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
please suggest any alternative ways to overcome this issue thanks in adv.
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).
We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.
One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.
we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".
This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.
These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.
We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.
In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.
Is there any way to totally avoid deadlocks. In some critical applications we have removed transactions entirely, counting on other means to maintain database consistency. We still get deadlocks in this area. These are mainly inserts, and the only thing I can think is that updates to the indexes are causing multiple page locks which result in deadlocks. Is this true?
Will deadlocks be eliminated in 7.0 with row level locking for this situation? Or will index page splits still cause a possibility of deadlock contention?
I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.
The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.
I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks. During past year, the number of users has grow (from 100 before to 500 or 700 now)
*** Deadlock Detected *** - Requested by: SPID 360 ECID 0 Mode "S" - Held by: SPID 113 ECID 0 Mode "S" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 113 ECID 0 Mode "S" - Held by: SPID 374 ECID 0 Mode "X" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 374 ECID 0 Mode "IX" - Held by: SPID 360 ECID 0 Mode "S" Table: TABLE_2 Database: MYDB == Lock: PAG: 22:1:2428 == Deadlock Lock participant information: Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1 Input Buf: s p _ e x e c u t e 8 Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1 == Session participant information: == Deadlock Detected at: ==> Process 360 chosen as deadlock victim
I have done : - rebuild indexes on all tables (fillfactor 90) - analysed memory activity
Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?
Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.
Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700
I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.
I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock. Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:
SELECT spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 10000 AND spid > 50
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)] FROM master..sysprocesses p WHERE open_tran > 0 AND spid > 50 AND datediff (s, last_batch, getdate ()) > 30 ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)
I get:
55860978LCK_M_XPAG: 13:1:2573
54AWAITING COMMANDsleeping sa 11499 55UPDATE sleeping sa 21499
We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems.
The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.
There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?
Hi EverybodyI am new to sqlserver 2000.I know basics of locks.but i dont know how toresolve deadlock issues.I am cofusing by reading articles with 90%information and remaining 10% missing.Can any one help me which is the goodsite to learn and resolve deadlocks.Note: I create deadlock. when i try to trace deadlock using dbcc traceon(1205,3604,-1).In error log showing nothing about the deadlock.showing created traceon.........Any help would be appreciated.--Message posted via http://www.sqlmonster.com
We have a problem with a table giving us deadlock issues and we can'tfigure out why.It's a table we write to fairly often perhaps 50 times a minute. Andalso do a select of 200 rows at a time from 4 servers every 5 minutes or so.We are only keeping 48 hours worth of rows in the table which averagesat 30000 a day on a busy day.This table has 1 PK and 2 FKs plus one TEXT column which does notparticipate in the WHERE clause.We are using binded variables.We have applied the latest patch to SQL2003 server running onWindows2003. The patch is supposed to resolve deadlock issues.Anyone have any advice on how to alleviate this problem.Thanks
Morning All, Am getting the following error from a number of users and am sort of wondering where to start in terms of diagnosing the problem. If anyone could give me any pointers on where to start in diagnosing the issue I would be grateful. "System.Data.SqlClient.SqlException: Transaction (Process ID 282) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I know blocks and Deadlocks are different but how related are they? Seems like when I get reports of deadlocks I always have blocks and the blocks grow as time passes.
My database has been in production for 10 months with no deadlock problems. Three weeks ago I had to restore the database and I am now experiencing several deadlocks. SQL server is suppose to handle this and kill a process to resolve the deadlock but it is not. The deadlock occures on a stored procedure which is a simple select on a table with 187 records. DBCC ran with no problems. Any ideas what might be causing this? Any why is SQL not handling? Your help is greatly appreciated!
I need some help in reducing deadlocks in 6.5 I have tested with `Insert Row Locking` turned off and it reduced the number of deadlocks. What i need to know is if removing the foreign key relationships on tables reduces/eliminates Deadlocks. If any of you have any info on this please let me know.
i have an issue regarding the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
this is caused on a couple of ASP pages, which are using:
This is happening fairly frequently, which can't be a good thing, so would it be worth changing the lock or cursor types to a different one, in particular, adLockPessismistic?
Any pointers or comments would be very welcome.
This is happening on a windows server os, sql server 2000 using classic asp (.asp) pages, connecting to sql server using ado within the asp pages.
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data:
Transaction (Process ID 499) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?
