I have a deadlock that I can't figure out. Both processes are doing a select from the same table. The owner mode on both is IX and the request mode is S. The lock is a Page Lock.
How do I resolve this?
I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"
From progress:
Error: The Script returned a failure result.
Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
I have a small database and a smalll table ( Table ID=565577053,with two
indexes on this table). when more than one user connected, I got the deadlock on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I got this TAB lock situation instead as following:
2006-01-18 09:51:37.87 spid4 ----------------------------------
2006-01-18 09:51:37.87 spid4 Starting deadlock search 15
Deadlock encountered .... Printing deadlock information
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Wait-for graph
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:1
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:77 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:2
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:64 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
found.
2006-01-18 09:51:37.87 spid4 ----------------------------------
How can I get rid of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?
Any kind of help will be appreciate.
Hanson
Hello,
I have scheduled a job using Job Agent in SQL2K (Management). Every once in a while i get an error when i view job history:
Executed as user: xxx. Transaction (Process ID 53) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
The process ID changes for every error. Sometimes its 51, 56, or 54 as well.
I have checked several times, but there is no other process that runs at that time. I even rescheduled it for a different time, but even then it fails about once every week or two.
How can i check what is the other procedure it is deadlocking with, if it is? OR what exactly is the problem, if it is not with any other process?
Any help and/or guidance is much appreciated.
Thanks,
J
Hello, I'm gettting a deadlock. One of the resources is a metadata lock in service broker.
this is from the deadlock graph.
<process id="process1fc2325c8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 5 CONVERSATION_GROUP($hash = 0xa927b1ef:0x691c259b:0xd5edc9)" waittime="13250" ownerId="3702420299" transactionname="GetDialogByHandle" lasttranstarted="2008-04-22T10:46:31.457" XDES="0x927baaf8" lockMode="X" schedulerid="2" kpid="7316" status="suspended" spid="173" sbid="2" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-04-22T10:46:31.457" lastbatchcompleted="2008-04-22T10:45:51.567" clientapp="SQL Queue Activation Service" hostname="82994-APP1" hostpid="2396" loginname="VIAGOGORSQueue.Activation" isolationlevel="serializable (4)" xactid="3702420292" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
would anyone shed some light??
thanks!
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 .
Hi Friends,
I have a problem to read varibale inside script.
the error discription is following-
Error: A deadlock was detected while trying to lock variables "User::FilePath" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
this is my code. i my code i am using two variables, but i can access only one .
i have to retrive file directory and file path is generated by other system.
Public Sub Main()
'
' Add your code here
'
Dim vars As Variables
If Not vars Is Nothing Then
vars.Unlock()
End If
Dim lFileName As String = String.Empty
Dim lFilePath As String = String.Empty
Dts.VariableDispenser.LockOneForWrite("FilePath", vars)
Dts.VariableDispenser.LockOneForRead("FileName", vars)
Dts.VariableDispenser.GetVariables(vars)
lFileName = vars.Item("FileName").Value.ToString
lFilePath = GetDirectoryName(lFileName)
If Not lFilePath.EndsWith("") Then
lFilePath = String.Format("{0}", lFilePath)
End If
vars("UnzippedKeyStatFilePath").Value = lFilePath
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
Thanks
Manish Jain
I have two questions regarding locking in SQL 2000.
1) If I set a lock whithin a transaction and the transaction fails/rollsback.
Does the lock get released as well?
2) If a connection is lost while in a transaction and a lock was set during
the transaction. Does the lock get released?
Thanks
Hi,Version Info: SQLSERVER 2000 SP3I am trying to understand how SQL Server works with SERIALIZABLE read.I am fairly new to SQL Server, having mainly worked with Informix.We are getting lock timeout error in our application. Our lock timeoutis set to 20 seconds. I am trying to investigate why locks are held forsuch a long time in the first place.I believe SQLServer tries to put keylocks as much as possible forensuring serializibility. For e.g.SELECT column_bfrom table_awhere column_c between 1000 and 1100what if column_c has no index. Does it switch over the data row.And what if a table has no index. Will it go for table lock, as ituse to do in earlier versions.TIA.--email id is bogus
View 3 Replies View RelatedFirst post, so greetings!I've been using ASP.NET 2 authentication from an MS SQL 2k5 database now in the web application for my company's customer accounts (file serving, custom webreports, etc). There is currently no support for employees through this web app, but in our plan to go paperless it's become of interest to let the employees access much of their information through the same web portal. The catch is, I'd rather not make them have to use two accounts (one for their domain authentication and one for their web authentication). It's already taken a lot of conditioning to get them to memorize a single username and password.So what I really need is the single Login.aspx page to accept both ASP.NET logins and Domain digest logins, and the Domain digest login needs to be HTTP based (not named pipes). Is this possible at all, and if so, is it possible without making an isapi filter? After all, Microsoft Office Outlook Web Access is served by the exchange server and accepts active directory passwords. So how could I do authentication in this way, and if so, is it possible to have the Outlook Web Access form be accessible from the same session as that of my web server? Thanks, I surely hope someone can help. . . -Brandon
View 1 Replies View Related
Hi,
I was trying to extract data from the source server using OLEDB Source and SQL Server Destination when i encountered this error:
"Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
What must be done so that even if the table being queried is locked, i wouldn't experience any deadlock?
cherriesh
One annoying problem has occurred....I want to put database in offline mode....but it is giving me some error...
"ALTER DATABASE failed because a lock could not be placed on database [database]. Try again later. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)" ...
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 RelatedHi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Please advice. Thanks a bunch.
Pankaj
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
View 1 Replies View RelatedWe have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
Hello,everyone!!
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)
Rgds,
Adie
how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.
  i am using SQL server 2008 R2
Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.'
Can someone say anything about fibe mode?I am appreciated for it.:)
Hi,
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
Thanks very much.
Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
Hi,
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
Thank you for any help
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
Sorry I am a newbie at this SQL thing.
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 RelatedIn 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 RelatedSQL 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
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
How can I reproduce a deadlock in 7.0?
View 1 Replies View RelatedHi,
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
Help !!
How would you handle the deadlocks that happen often? Since the victim users complained about the disconnection.
Thank you ahead of time
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