Transaction Control Always Has Deadlock By Spid=-2
Nov 1, 2007
When I do a data flow task like following, always blocking
1.get data from data source
2.conditional spit by column='Y' or 'N'
3.when column='Y' insert to table a
4 when column='N', ole db command, update table a
but when I run this package, I see there are some rows are 'Y', some rows are 'N',but they can't run together.
Why? I've took long time for the issues.
When I change this data flow to a data flow plus t-sql task, and change ole db command( update table a) to execute tsql task.
The issue can be solved. But I want to know why i can't put these in one data flow?
View 6 Replies
ADVERTISEMENT
Jun 4, 2007
I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.
rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.
it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while
the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607
If some body could please help me it would be really helpful for me.
Thanks in advance!!
View 5 Replies
View Related
Jun 10, 2002
Hi guys;
i use a transaction that takes a long time to execute.(Different updates on different tables)
I want to use in my transaction a TABLOCKX after each update in order to Lock the table within the Transaction (Until the transaction will finish).
This will normaly force another transaction using the same tables within the first transaction to wait until the lock will release.
1- Because there is no time out set . Does the second transaction wait until the first transaction be commited even if it takes more long time.
2- Does such situation hold a deadlock.
i will apreciate your help. Thinks
View 1 Replies
View Related
Jun 22, 2007
I have a customer using our program with SQL server and isoccasionally getting a "Transaction (process ID xxxxx) was deadlockedon lock resources with another process and has been chosen as thedeadlock victim." From what they are telling me, there shouldn't beany deadlock happening as they say this happens when they invoicing ina different program that is accessing a different database. Also theerror is happening on an SQL Select from a view and this select isthen showing data in an HTML table for the user. I don't think thisview should need to lock anything, I just want to read the data. Isthere anything I can do to fix this?
View 5 Replies
View Related
Mar 3, 2007
HI,
I have a framewrok that runs tests and keeps updating the status of the tests to the DB. They are approx 20 tests whose status will be updated simultaneously. Recently i have seen the follwoing error
{"Transaction (Process ID 84) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."}
I am using SQL server 2005. Any suggestions?
Thank you
View 1 Replies
View Related
Jun 7, 2007
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.
r,
JM
View 3 Replies
View Related
Sep 20, 2004
I have a DB that has a DB filesize of 6 gb and a transaction log filesize of 66gb!!!! This DB has a potential for alot of growth because it holds scanned documents for retention and they are in the process of scanning in several years worth of documents from what I understand.
I have my maintenance plan setup to backup the DB full overnight and transaction log backups hourly during the day. usually my transaction log backups are small - but on friday, the transction log backup was 66gb and it filled up the disk. after doing some cleanup, I ran a full and then a transaction log backup and everything seems OK. But the transaction log file itself remains at 66gb!
I'm just wondering how I can optimize my Database AND especially my maintenance plans so that the transaction log gets back down to a manageable size?? Can I or should shrink the transaction log?? Should my DB be set to 'autoshrink'???????
View 1 Replies
View Related
Nov 29, 2006
Hi,
can we control how transaction send from principal to mirror ?
If application inserting 10000000 rows in one transaction to principal database
how infomation will be transfered once it is commited and where it will be stored before it is replayed on mirror database?
1. Is it going to be 1 big data packet ?
2.is it going to be split on many packets (of what size ?)
Thanks
Alex
View 4 Replies
View Related
Apr 20, 2004
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @@error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @@error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Help
View 5 Replies
View Related
Nov 14, 2007
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
View 4 Replies
View Related
Nov 10, 2015
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
View 5 Replies
View Related
Jan 27, 2008
Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
With Thanks
M.Mahendra
View 5 Replies
View Related
Oct 25, 2007
does any one have and example of how to embedd a flash swf file onto a report.??? Is it possable? any examples would be helpful.
View 1 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
Dec 14, 2000
Could someome please tell me what is going on with the following stored procedure:
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
View 1 Replies
View Related
Dec 13, 2005
HI!
if i have SPID. then can i get the storedprocedure name ?
Regards,
Shabber Abbas Rizvi.
View 1 Replies
View Related
Mar 28, 2006
All,
Is there any way to see the SQL for any given spid in SQL server?
I can see "properties" under enterprise mgr - which shows you a snippet of the SQL - or "sp_execute" depending on what you use to get to the DBMS.
Is there a way to take a deeper look?
Thanks,
Isaac
View 2 Replies
View Related
Apr 15, 2004
Hi,
Can I have more than one value for variable @@SPID during a connection?
PS: sorry my english.
Thanks.
View 3 Replies
View Related
May 18, 2004
Hi All,
Sql Server 7.
I have a table cust_detail when ever there is an insert/update/delete in this table it is getting blocked and in turn its blocking other table inv_credit which is doing select statement.
Please suggest me what might be the reason.
Waiting for Reply
TIA
Adil
View 1 Replies
View Related
Jun 19, 2008
Hi to all.
I have a small doubt.
In my server some processs are running slow.
With out the use of profiler hw we can find the spid's that are running slow. is there any procedure for that.
View 5 Replies
View Related
Dec 29, 2006
I am seeing intermittent blocking problems. The strange part of it is,that the SPID listed in the Blk By column is the same as the SPID thatis being blocked.Has anyone seen this before, or have any idea what this may suggest?
View 1 Replies
View Related
Feb 6, 2007
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View 1 Replies
View Related
Dec 22, 2006
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
View 8 Replies
View Related
Jan 8, 2008
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
View 2 Replies
View Related
May 12, 2000
Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'''
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u
kavira
View 2 Replies
View Related
Nov 15, 2000
I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?
Thanks
View 2 Replies
View Related
Dec 22, 1999
I'm going to try to explain my situation.
I have a spid that I'm not able to kill, this were a select statement from with in access 97 application using a DNS connection.
So even that I reboot the client pc and kill the spid it still shows as active with status RollBack.
We have similar problem before and the only way that it disappear were re - starting Sql.
(system Sql 7.0 with sp1 and Access 97)
Thanks.
View 2 Replies
View Related
Feb 20, 2003
How do COM+ transactions relate to SQL Server SPID?
I am trying to solve a deadlock issue. Using SQL trace, I find SPID 65 and 71 are trying to acquire resources that are locked by each other. But what I am confuse with is tasks performed by 65 are triggered by different transactions (same for 71). Should different COM+ transaction uses different SPID?
The entire COM+ application uses only one db login.
Please advice.
View 2 Replies
View Related
Feb 19, 1999
I can not stop a process using 'KILL spid'.What else should I try?
process has begun a transaction and does not commit.
View 2 Replies
View Related
Apr 15, 2002
I have a problem. I am trying to kill a spid that is blocking updates to a table.
The spid number is '-2'. I am using KILL with UOW and I am getting this error:
Server: Msg 6112, Level 16, State 1, Line 1
Distributed transaction with UOW {FCF8D536-27ED-11D6-9CF2-0002A56BDA54} is in prepared state.
Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.
Users are connecting through MTS server. I am runnnig SQL2000 sp2 +hotfix, on NT4.0.
Has anyone encountered this problem before, and has a solution for it (besides rebooting the MTS and SQL Server)?
Thanks,
Peter
View 1 Replies
View Related
Oct 6, 1998
What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
View 3 Replies
View Related
May 8, 2008
can we just kill spid and drop it without doing any rollback ...?
Thanks,
ServerTeam
View 9 Replies
View Related
