I had a deadlock situation on SQLServer 2000. When I look at the
Locks / process ID screen on Enterprise Manager, for one of the
session, I see the object being one table and the index is on another
totally unrelated table's primary key. This is really odd since there
are no foreign keys tying these two tables together. They don't even
reference the same parent tables. These two tables are quite
dis-joint. Does anyone know why I will get this kind of lock?
We have a client-server architecture in development in which clients synchronize their data tables with the server. If we try to sync two clients at the same time we often get a deadlock - which would make sense except for the resource that gets deadlocked: stored procedure xp_regdeletevalue, which is used by SQL Server to delete keys from the Windows registry.
According to the trace (capturing all events listed in Profiler + the 1204 flag) right before the deadlock takes place xp_regdeletevalue gets locked and released about 500(!) times in a row (nothing happens between the Lock:Acquires and Lock:Releases). We're not invoking this procedure directly although one of the frameworks we're using (Spring or Hibernate) might be, but I found no reference to it in their documentations.
Spid4 is a system thread (as far as I know it's the read-ahead manager) and 1950629992 corresponds to xp_regdeletevalue according to dbo.sysobjects. The system is written in Java, based on Hibernate and Spring (using Spring's transaction model) and runs on a Windows XP test server with SQL Server 2000.
I have a SSIS package that is run from one job, nowhere else. The package has TransactionOption NotSupported.
In the SSIS package I first have a sequence container that has TransactionOption Required (Serializable). The sequence container contains several Execute SQL tasks that access the same SQL Server 2005 database. The Execute SQL tasks have TransactionOption supported / Serializable. The first SQL statement is:
select p.column_name
from table_name p with (XLOCK, ROWLOCK)
where p.second_column_name = 'COLUMN_VALUE'
After this there are a couple of SQL tasks, and finally a task containing the following SQL:
select p.third_column_name
from table_name p
where p.second_colomn_name = 'COLUNM_NAME'
The idea is that the first query exclusively locks the row in the table_name-table for mutual exclusion purposes, i.e. so that if for some reason the SSIS package would be executed simultaneously two or more times, only one package execution could lock the row and proceed and the other executions will have to wait. There's an index on column second_column_name in the table to avoid the select locking other rows in addition to the required row.
Some questions:
1) Is it so in my setup that when SSIS runtime executes the sequence container it creates a transaction in the beginning of the sequence container and commits the transaction in the end of the sequence container? And in my setup the Execute SQL tasks in the sequence containar are executed under the same transaction?
2) I have a problem that the second query sometimes gives this error:"ErrorCode: -1073548784. ErrorDescription: Executing the query "XXXXX" failed with the following error: "Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
The 1st query has locked the row for the transaction. How can the second query be deadlocked, shouldn't the transaction have a lock on the row? I'd understand if the 1st query failed sometimes, but don't understand how the 2nd quey can fail.
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.
I built an ASP.NET application that works with SQL Server Express 2005. I am using Typed Datasets to interact with the database, and everything works well on my staging server (Windows 2003). However, when I deployed the application to the clients server, no updates actually work! I try adding new items through the interface to no avail. I try updating data through the interface and nothing works. The strange thing is, I get no errors! Has anyone ever encountered this before? Karls
MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.
Hello,At work we have various servers, therefore we have various DTS Interfaceswhich transfer data between them.However when trying to save a new View to one server which gets its datafrom another server nothing happens, I get an hourglass and nothing else.Then I have to exit out of SQL Enterprise Manager and then no-one can accessthe database on that server, so we had to restore from backup!The query to create the view resides on server AThe query is getting its data from server BBut the query does need to reside on server A for other reasons.What does this sound like to you?I am suspecting connection problems between server A and B but the queryruns, its only when I save the view that this happens.I am puzzled.Thanks,Jayne
I'm trying to copy a database from my web host to local Sql Server 2005 instance using the Copy Database wizard. I get this error:An exception occurred while executing a Transact-SQL statement or batch. Server user 'celestine' is not a valid user id database 'db2'.However, 'db2' isn't the database I was trying to copy! I am trying to copy db1, but this error is complaining about db2, which is in fact somebody else's database that I don't have a login for. Why am I getting an error about db2 when I'm not trying to do anything with db2???I can however export the tables from db1 just fine using Export. But then I have to go back and generate script to re-create the stored procs, views, etc., so it is rather tedious to do it that way.
Hello, I'm tyring a simple piece of code to conenct to my database: SqlConnection con = new SqlConnection("Data Source=localhost; Initial Catalog=OrderingSystem; Integrated Security=SSPI"); try { // Try to open the connection. con.Open(); label1.Text = "<b>Server Version:</b> " + con.ServerVersion; label1.Text += "<br /><b>Connection Is:</b> " + con.State.ToString(); } catch (Exception err) { // Handle an error by displaying the information. label1.Text = "Error reading the database. " + err.Message; } finally { // Either way, make sure the connection is properly closed. // Even if the connection wasn't opened successfully, // calling Close() won't cause an error. con.Close(); label1.Text += "<br /><b>Now Connection Is:</b> " + con.State.ToString(); } When I try this, I get an error:"Error reading the database. Cannot open database "OrderingSystem" requested by the login. The login failed. Login failed for user 'NT AUTHORITYNETWORK SERVICE'." The strange thing is, this code works perfectly If I use it in a Windows Forms application. Is it a problem with SQL server, IIS or my code?I'm using SQL Server 2005 (SP2) 32bit developer edition + IIS 6.0 + Visual Studio 2008 Pro all runnign on my Windows XP x64 PC. Thankyou
Hey u guys.I have a strange problem and non of myfriends can solve it.The story is like this:I want to connect to my database at my hosting from SQL Query Analyser.With the same SQL server, username and password.But there is only me who can not connect by that way.This is the error of that problem, i made a screenshot for it.Please click here: http://www.xhnguyen.info/sql.JPGAnyone had this problem before ?Could anyone please help me solve it?Thank you very much.Joesy
I was just restoring one database and creating another concurrently on the same server. The restore took longer than the create due to the respective sizes. After the create had finished (done in Query Analyzer) I went back to the restore to see how it was getting on (running in Enterprise Manager). There was an error (which I didn't record exactly - DOH!) along the lines of "could not obtain a lock on the model database, restore terminating abnormally." I guess the create had model locked while it was creating the default objects, but what was a *restore* doing needing the model database anyway?
I met a very strange problem recently. I set up a database integrity check maintenance plan. But this job failed every time. I looked into the logs, the error message was that Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. I used the sp_helpdb to check the version of the databases included in my maintenance plan. The sp result shows that all the databases are above version 80....
Even more strange, i can successfully run the dbcc check query on each database.
Any comment and suggestion will be very appreciated.
Hi,We have 2 servers. Server1 is a 2000 box, with SP3 (yes I know it isnot up to date). Server2 is a 2005 box, SP2.I set up Server1 (2000) to have a linked server to Server2 (2005). Thereason I did this is because we are using a stored procedure onServer2 to send mail, as we have found that using mail on 2000 doesn'talways work as advertised.When I set up the linked server on the 2000 box, for security I justset it up to use a SQL Server user on the 2005 box. The SQL Serveruser on the 2005 box has permissions to run the stored procedure forsending mail.Here's the weird thing though. When calling the stored procedure onthe 2005 box from the 2000 box, sometimes we get an error that "Thedefault database cannot be opened", and the query does not run on the2005 box. However, it only happens *sometimes*. Other times, the queryruns fine.Since the problem seeemed to be with the default database, I changedthe SQL Server user on 2005 default database to the SAME database thatcontains the stored procedure.However, I just don't understand why it's even TRYING to open thedefault database, since when we called the linked server we are doingso as, and it's referencing the default database in the name:EXEC Server2.DefaultDatabase.dbo.StoredProcedureNameHowever, after changing the user's default database to"DefaultDatabase" as shown above, the query runs fine.Why are we having this problem? That is, if I change the defaultdatabase to something other than "DefaultDatabase", then the querydoesn't run, even though the database name is referenced in the abovequery??Obviously, this is not desireable, because that means we can only runqueries that are in "DefaultDatabase", which may not always be thecase.Thanks much
I€™m suffering a queer behaviour when I use BIDS. Concretely, when I open a dtsx from my project (it has 10 packages) many times Sequence Container and Data Flow tasks are invisible. I mean, its lines are not visible at all whereas its titles are. I mean, what you see is just a white box€¦
Then, I€™m gonna Data Flow layer and I have to do double-clik over the tasks and are visible but on Control Flow I don€™t see how to solve.
Curiously in our development and production server such behaviour doesn€™t happen (we are accessing by mean Terminal Server from our workstations)
How odd!. Everything is fine except this.
I want to remark you that such project has been copied from the server, this is, these packages are been built on the server
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.
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?
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.
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
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) ?
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)
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.
Using SQL Server 2000 SP3a, I run the following in 2 query analizerwindows on the Northwind database, the second one always gets thedeadlock Msg 1205:Window 1:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1update orders set employeeid = 1 where employeeid = 1waitfor delay '00:00:03'commitselect @cnt = @cnt -1endWindow 2:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1 and customerid ='ERNSH'waitfor delay '00:00:02'commitselect @cnt = @cnt -1endThe query in the first window gets 123 rows and places update locks onthem, then updates them and commits. The query in the second windowgets a subset (about 5) of the results that window 1 gets also tryingto place update locks on the same rows. Shouldn't the query in window 2just wait for the transaction in window 1 to finish? why would itdeadlock?you can also get rid of the delay in the second window and it willdeadlock faster.thanks in advance.Eugene
Hi.create table joe(c1 integer not null, c2 integer not null)Two sessions:Session 1:BEGIN TRANinsert into joe (c1,c2) values (1,2)Session 2:BEGIN TRANinsert into joe (c1,c2) values (3,4)Session 1:select * from joeSession 2:select * from joeOne of the sessions gets a deadlock victim message.thanks,Joe
I have a table that every 30 minutes needs to be repopulated fromanother table that is recreated from scratch just before.What I did was this:CREATE PROCEDURE BatchUpdProducts ASbegin transactiondelete productsinsert into productsselect * from productsTempcommit transactionGOThis takes about 30 seconds to run. I tried it doing it with a cursor,row by row, but it took like 30 minutes to run instead. The problemis with the fast approach is, once in a while I get a deadlock errorin different areas trying to access the products table. Using SQLServer 2000 by the way.Any ideas?
Here is the exception I am getting, any idea why this might be happening:
Exception: ThisMethod is done: let this go: Exception: Transaction (Process ID 74) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Stack Trace: at
Hi, I need to trace deadlock, one of article was mentioning “QL Server Profiler's Create Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
