ADO.NET :: Transaction (Process ID 51) Deadlocked On Lock
Oct 21, 2010
I have a deadlock problem that occurs every 5 minutes on SQL update. I get the following error randomly - means qurey failed one time every 200 calls. I'm using transaction scope to manage transaction.
code:
using(TransactionScope scope =
new
TransactionScope())
{
SELECT ... FROM TABLE1
UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
scope.Complete();
}
error:
System.Data.SqlClient.SqlException: Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
stack trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DAL.RunSQL(String query, SqlParameter[] parameters, Boolean openTransact) in C:prjDAL.cs:line 215
I took trace in SQL about deadlock and same SQL query is mentioned bu deadlock. I do not understand how to resolve this deadlock.
SQL log:
deadlock-list
deadlock victim=process5462718
process-list
process taskpriority=0 logused=0 waitresource=KEY: 20:72057597472604160 (ce004d0b917a) waittime=30 ownerId=5937428 transactionname=user_transaction lasttranstarted=2010-10-20T16:29:04.050 XDES=0x5d00280 lockMode=X schedulerid=2 kpid=2168 status=suspended
spid=65 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-10-20T16:29:04.110 lastbatchcompleted=2010-10-20T16:29:04.110 clientapp=.Net SqlClient Data Provider hostname=IPTBEWKS017 hostpid=3112 loginname=iptuserjde isolationlevel=serializable (4) xactid=5937428
currentdb=20 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=222 sqlhandle=0x0200000086adfa0c959ccbdd7ef0e31e854e3a987706ae38
UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@UId nvarchar(9),@F1 nvarchar(9),@F2 nvarchar(9),@F3 int,@F4 int,@F5 datetime)UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
process taskpriority=0 logused=0 waitresource=KEY: 20:72057597472604160 (cc0078aef293) waittime=88 ownerId=5937412 transactionname=user_transaction lasttranstarted=2010-10-20T16:29:04 XDES=0x2b028280 lockMode=X schedulerid=1 kpid=6788 status=suspended
spid=74 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-10-20T16:29:04.050 lastbatchcompleted=2010-10-20T16:29:04.050 clientapp=.Net SqlClient Data Provider hostname=IPTBEWKS017 hostpid=3112 loginname=iptuserjde isolationlevel=serializable (4) xactid=5937412
currentdb=20 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=222 sqlhandle=0x0200000086adfa0c959ccbdd7ef0e31e854e3a987706ae38
UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@UId nvarchar(9),@F1 nvarchar(9),@F2 nvarchar(9),@F3 int,@F4 int,@F5 datetime)
UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
resource-list
keylock hobtid=72057597472604160 dbid=20 objectname=dbipt_rc41_02.dbo.TABLE1 indexname=PK_TABLE1 mode=RangeS-U associatedObjectId=72057597472604160
owner-list
owner id=processdbbe40 mode=RangeS-S
waiter-list
waiter mode=X requestType=convert
keylock hobtid=72057597472604160 dbid=20 objectname=dbipt_rc41_02.dbo.TABLE1 indexname=PK_TABLE10 mode=RangeS-U associatedObjectId=72057597472604160
owner-list
owner mode=RangeS-S
waiter-list
waiter id=processdbbe40 mode=X requestType=convert
I have tried to analyse the trace with this post but unsuccessfully: [URL] UId is my primary key - I do select on F3 column just before in same transaction but i have also an index on F3 column.
->
Spid 65 is running this query (line 2 of proc [p1]):
UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
Spid 74 is running this query (line 2 of proc [p2]):
UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
I have an invoke function that I call. It On the client side it reads a file and then sends the line to the server side function here and waits for a return. Here is the server side code:
[Code]....
What I find is that I will keep getting these error message as posted in the subject... It will work for some but others I get this message. Thoughts on how to avoid that, or is that just something I'll need to work around with timing?
I have a stored procedure which fetches data after joining 8-9 tables and inserts that into a temp table. It was running fine till now, but now when the amount of data fetched have exceeded 20000, the SP is breaking. I have debugged the sp and found that this main query is failing after returning arround 15000-16000 records.
The error message says Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I know what is deadlock, but when i checked with SP_LOCK stored proc i only found that my process is running on database only. So how is this getting deadlocked when there are no other processes running simultaneously on server.
I develop an application using LINQ to SQL. Recently I deploy the application on server and starts performance testing using WAPT for 100 users. Unfortunately, 90 requests are failed while placing an order and only 10 are passed. I analyse and found that for every failed request I got an error "System Exception: Transaction (Process Id 68) was Deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
The architecture of the application is straight forward. There is a web application which maintain account holder data. This data is processed and the status of account holders is updated based on number of business rules. This process is initiated using a button on the page and is a long running process (say 15 mins). A component is developed to do this data processing which internally calls stored procedures. Most of the business rules are kept in stored procedure.
i have a method that do bunch of inserts in a table
these inserts done through a transaction (with default isolation level)
meanwhile i need to read some committed records from this table by using another method, i create another transaction for 2th method with a ReadCommitted isolation level but it just fails and return a timeout error and also, the DoTransaction cause the entire table inaccessible, how could i apply a row-level lock ?
While updating table Un handled exception was raised (asp.net with c# and SQLServer 2005)
Error Message: "Transaction (Process ID 91) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Here is the scenario...An authenticated user can earn points and later spend it on some items.Each item has fix value for each entry, where user can spend for that.For example user has 100 points and an item needs 40 points for each entry. User allows to spend as much as points he wants for an item, which in this case he can spend max 80 points and get 2 entries.
The problem is, there is a chance that a user try to spend more than what he is allow by logging in from different browsers or computers and add these entries at the same time. Although there is server side validation but there is still a chance that he passes the validation.The work around was to restrict user to login only once which is not feasible solution.
Put a lock on static object and make sure it will not tampered by other threads.But what if 1000 users or even more want to add an entry at the fairly same time? I don't want make user wait so long...What is the best solution to deal with these type of scenarios.. I have read so much but really din get any good answer..
I've got all of my ASP.NET requests wrapped in a Session and a Transaction that gets commited only at the very end of the request. At some point during execution of the request, I would like to insert an object and make it visible to other potential threads - i.e. split the insertion into a new transaction, commit that transaction, and move on. The reason is that the request in question hits an API that then chain hits another one of my pages (near-synchronously) to let me know that it processed, and thus double submits a transaction record, because the original request had not yet finished, and thus not committed the transaction record.
So I've tried wrapping the insertion code with a new SessionScope, TransactionScope(TransactionMode.New), combination of both, flushing everything manually, etc. However, when I call Refresh on the object I'm still getting the old object state. Here's some code sample for what I'm seeing:
Post outsidePost = Post.Find(id); // status of this post is Status.Old using (TransactionScope transaction = new TransactionScope(TransactionMode.New)) { Post p = Post.Find(id); p.Status = Status.New; // new status set here p.Update(); SessionScope.Current.Flush(); transaction.Flush(); transaction.VoteCommit(); } outsidePost.Refresh(); // refresh doesn't get the new status, status is still Status.Old
If I watch the processes tab in Task Manager on the web server I see "cmd.exe" under the context of 'administrator' but it just hangs. For test purposes c:SendV80s.bat: copy c: oot.ini c:zzz.txt
If I logon onto the webserver's console and execute SendV80s.bat it works and exits without issue. But when I execute the same batch file via the Submit button it gets stuck executing in Task Manager/Process. I believe this has something to do with the fact that cmd is not running in a full environment/desktop context. I just noticed this on the actual console of the webserver (not in my RDP console but console 0 instead)
A pop-up box stating: CMD.exe Application error The application failed to initialize properly (0xc0000142). Click OK to terminate the application. And when I click on the OK button my ASPX page's WaitForExit is satisfied and the continues processing normally.
I know that similar questions have been asked all over the place, but I'm having trouble finding one that relates directly to what I'm after.
I have a website where a user uploads a data file, then that file is transformed and imported into SQL. The file could be up to 50mb in size, and some times this process can take 30 minutes or sometimes even longer.
I realise I need to palm off the actual work to another process, and poll that process on the web page. I'm wondering what the best approach would be though? Being a web developer by trade, I'm finding all this new Windows Service stuff a bit confusing, and I just wanted somewhere to start.
So:
Can I do / should I being doing this with a windows service? if so, how?
Should I use WCF? If this runs under IIS, will I have problems with aspnet_wp.exe recycling and timing out my process?
clarifications
The data is imported into sql, there's no file distribution taking place.
If there is a failure, it absolutely MUST be reported to the user. The web page will poll every, lets say, 5 seconds, from the time the async task begins, to get the 'status' of the import. Once it's finished another response will tell the page to stop polling for status updates.
queries on final decision
ok, so as I thought, it seems that a windows service is the best idea. So as to HOW to get it to work, it seems the 'put the file there and wait for the service to pick it up' idea is the generally accepted way, is there a way I can start a process run by the service, without it having to constantly be checking a database table / folder? As I said earlier, I don't have any experience with Windows Services - I wondered if I put a public method in the service, can I call it somehow?
I keep receiving an exception with the message "Logon failure: unknown user name or bad password". Im absolutelly sure that i'm submiting my correct username/pwd
I've developed a popup email .aspx used on our intranet based web app that is auto generated with .pdf's attached. I'm developing with VS 2008 ASP.Net 3.5 C# and System.Net.Mail.MailMessage. I can create and send the email with no issues. The problem is with any attempt to open or delete the attachments I get the above error. The .pdf's a copied with the following code:
FileStream fsr = new FileStream(inFilename, FileMode.Open, FileAccess.Read, FileShare.Read); BinaryReader reader = new BinaryReader(fsr); byte[] bytes = new byte[fsr.Length]; reader.Read(bytes, 0, bytes.Length); FileStream fsw = new FileStream(outFileName, FileMode.Create, FileAccess.Write, FileShare.Write); BinaryWriter writer = new BinaryWriter(fsw); writer.Write(bytes, 0, bytes.Length); // clean up writer.Flush(); writer.Close(); writer = null; fsw.Close(); fsw.Dispose(); fsw = null; reader.Close(); reader = null; fsr.Close(); fsr.Dispose(); fsr = null; Later after sending the email I: mailMessage.Dispose(); mailMessage = null; foreach (string fileName in attachments) { if (File.Exists(fileName)) File.Delete(fileName); }
The error occurs at: the File.Delete(fileName);
how I can delete or reopen these files after sending the email?
I have a .NET infra code running both within the IIS worker process and within a desktop client app. How can the .NET code determine whether it is running within an IIS worker process?
I know that I could check the name of the process (w3wp.exe, for instance), but I would like a more robust approach. I wish to make a side note. This is not a production need. I need this information to enable certain scenarios useful during the development and testing phase. Specifically to ease the testing of secure vs non secure configurations.
When I hit the run button (in my Default.aspx), a process starts (this process contacts a webservice to get some files, etc). How do I: Ensure that only a single process is running at a time (i.e. if I refresh the browser, I don't want to start the process a second time)Track progress - there are 4 points of the process (at 25%, 50%, 75%, 100%) that I want to track, and when each part completes, I want to update the progress bar. I have a status object for the running process, but the question is how to update the progress bar automatically? Do I need to use threads to achieve the above two?
My code is that I want to create a log file and log it upon a new user browsing the site. However, what i did was I put in a 6 second delay and then used another browser to access the page. And it threw an Exception saying it is being used by another process which is true. So how come I set it so that, if IT is being used by another process, WAIT and retry every 500 milliseconds until it becomes free/available?
here's the code:
protected void Page_Load(object sender, EventArgs e) {if (!IsPostBack) // if this is the first time page loads, set k to 1 { lognewuser(); }}
I am having the text file which is used to track all the ip address which is available in the network and replace the content from"Reply from 172.29.116.3: bytes=32 time=1ms TTL=255" to 172.29.116.3.
For this task i am having 2 functions 1.runCMD() Function is used to create a file which ping all the ip address between 3 to 254 ("Reply from 172.29.116.3: bytes=32 time=1ms TTL=255").
2.Another function textFileReplace() which is used to replace the text from "Reply from 172.29.116.3: bytes=32 time=1ms TTL=255" to 172.29.116.3.
This process will continue every 30 minutes..
But i am having the error while accessing the function textFileReplace() as The process cannot access the file 'C:inetpub' because it is being used by another process.
When I delete all files from the directory path. then it will give me this error The process cannot access the file because it is being used by another process. How to solve this?
I am getting binary data from a SQL Server database field and am creating a document locally in a directory my application has permissions. However I am still getting the error specified in the title. I have tried numerous suggestions posted on the web including those suggested in previous posts on Stackoverflow. I have also used ProcessExplorer > Find Handle to locate the lock and it returns nothing as if the file is not locked.
I am using the code below to save the file to the file system and I then try to copy this file to a new location later in the application process within another method. It is this copy method that takes the path of the newly created file that throws the exception.
The file itself is created with its content and i can open it through Windows Explorer without any problems.
Am I missing something completely obvious? Am I creating the file correctly from the database?
// Get file from DB FileStream fs = new FileStream( "C:myTempDirectorymyFile.doc", FileMode.OpenOrCreate, FileAccess.Write); BinaryWriter br = new BinaryWriter(fs); br.Write("BinaryDataFromDB"); fs.Flush(); fs.Close(); fs.Dispose(); // Copy file File.Copy(sourceFileName, destinationFilename, true);
I am using a AsyncUpload(telerik), i am in a point where i am supposed to save the File to a certain Folder. First I check if the directory exist
Code: Dim baseDire As String = Server.MapPath("~/MyFILES/") If IO.Directory.Exists(baseDire & Request("Req_ID").ToString()) = False Then IO.Directory.CreateDirectory(baseDire & Request("Req_ID").ToString()) End If
Because i am doing multiple Upload , i will have to loop through the items of the Upload control and save to a folder.
Code: For Each file As UploadedFile In UploadPropDoc.UploadedFiles Dim bytes(file.ContentLength - 1) As Byte file.InputStream.Read(bytes, 0, file.ContentLength) 'Save the File to the disk file.SaveAs(baseDire + file.GetName()) Next
Now when ever i debug this , when it comes to this line
Code: file.SaveAs(baseDire + file.GetName())
I get an Error
The process cannot access the file because it is being used by another process.
I have removed the Readonly in the Folder that it supposed to write to and gave everyone permission , but still the error persist.
A few months ago I was interviewing for a job inside the company I am currently in, I dont have a strong web development background, but one of the questions he posed to me was how could you improve this block of code.I dont remember the code block perfectly but to sum it up it was a web hit counter, and he used lock on the hitcounter.
lock(HitCounter) { // Bla... } However after some discussion he said, lock is good but never use it in web applications!What is the basis behind his statement? Why shouldnt I use lock in web applications?
Is their a wayin sql server 2005 to change some type of setting on a data table so that it can not be updated or inserted into?
My problem is that I have a data table that is somehow being populated by an application. I have looked through all of the stored procs, views, functions and the C# code itself. I can't find out where or how it is populating a certain data table. So I figure if I can somehow make the table non updateable, then I can find out where the insert/update is occuring.
This would all occur in a test environment of course.
