March 8, 2012 at 3:41 am
Hello Friends,
I am facing a challenge to develop an algorithm in T-SQL.
Algorithm Logic:
A depends of B and B depends of A, or indirectly, for example A depends of B, B depends of C and C depends of A. In this case, it will create a dead lock. My aim is to identify this and insert them in a table.
For example: The data is shown in the table like this.
Source_idDependingSources
187 188
188 189
189 187
In this case -- A(187) is dependent on B(188), B(188) is dependent C(189), C(189) is dependent on A(187). This create a dead lock here.
So I have to insert a line like this "The dependent sources are 187,188,189". So how to detect the dependencies like this with T-SQL code.
I need the code / ideas / concepts or help in implementing this.
Please help. Thanks in advance.
March 8, 2012 at 4:07 am
Cannot see where exactly you're expecting a dead-lock.
May be if you follow: http://www.sqlservercentral.com/articles/Best+Practices/61537/
we may be more helpfull...
March 8, 2012 at 4:15 am
shyamhr (3/8/2012)
In this case -- A(187) is dependent on B(188), B(188) is dependent C(189), C(189) is dependent on A(187). This create a dead lock here.
That's not a deadlock, that's a dependency loop (deadlock is a circular chain of locks)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2012 at 5:29 am
MS SQL Server already have a mechanism to find dead locks.
What's you goal?
Its a home work?
March 8, 2012 at 6:34 am
Yes, this is a kind of challenge in T-SQL coding assigned to me.
March 8, 2012 at 6:36 am
That is true Gila. How can I achieve find these combinations that there is a loop in place. Thanks.
March 8, 2012 at 7:06 am
I would probably use a recursive CTE with checks to see it I ever get dependant = starting value.
Give it a try (Books Online has some good resources on recursive CTEs), if you get stuck post your code and we'll help further. Since it's an exercise I'm not doing it for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2012 at 7:18 am
Solution withdrawn as per Gail's note:
Give it a try (Books Online has some good resources on recursive CTEs), if you get stuck post your code and we'll help further. Since it's an exercise I'm not doing it for you.
Just one more thing: Depends on exact requirements, if you have predefined maximum possible number of items in the dependency loop (let say in your case it is 3), you may not need to use recursive CTE, JOINS will do just fine.
March 8, 2012 at 7:23 am
Thanks Gila and SSC. I will keep you posted.
March 8, 2012 at 7:28 am
Looks like OP managed to see my withdrawn solution, I wasn't quick enough to hit a button... :hehe:
March 9, 2012 at 7:00 am
Hello Friends,
My task is almost over. But I am struck @ final step. I was able to get the desired output with a Recursive CTE function.
But the challenge now here is :
Sample data
Dependency List
47,48,49,92,93,94,95,96,108,107
47,48,49,92,93,94,95,96,107,108
27,37
37,27
If you see there are two rows for the same combination of numbers. How can I order them to get only one row for a single combination. This is the last step.
Note: This is a varchar column.
This is the last step. If I am done with this the SP will be ready in couple of hours. So Please help me.......
March 9, 2012 at 7:02 am
Post the code you have so far. Just want to see how you've done it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2012 at 7:53 am
Sure... Here you go ....
;with tmp(Source_ID, DependsOn, DependingSources) as
( select CAST(Source_ID AS INT),
CAST(LEFT(DependingSources, CHARINDEX(',',DependingSources+',')-1) AS NVARCHAR(50)),
CAST(STUFF(DependingSources, 1, CHARINDEX(',',DependingSources+','), '')AS NVARCHAR(50))
from TEC.moris_source_id_India WHERE Active=1 AND DependingSources<>''
union all
select CAST(Source_ID AS INT),
CAST(LEFT(DependingSources, CHARINDEX(',',DependingSources+',')-1)AS NVARCHAR(50)),
CAST(STUFF(DependingSources, 1, CHARINDEX(',',DependingSources+','), '') AS NVARCHAR(50))
from tmp where DependingSources > '' )
select Source_ID, DependsOn INTO #Temp from tmp order by Source_ID
SELECT * FROM #Temp
GO
--
;WITH Final_CTE AS (
SELECT Source_ID, DependsWithCommas= CAST(DependsOn AS NVARCHAR(100)) ,LEN(DependsOn) As DepLen
FROM #Temp
--WHERE ManagerID IS NULL
UNION ALL
SELECT T.Source_ID, CAST(DependsWithCommas +','+T.DependsOn AS NVARCHAR(100)),LEN(DependsWithCommas) AS DepLen
FROM #Temp T
INNER JOIN Final_CTE Fcte ON Fcte.Source_ID = T.DependsOn
WHERE DependsWithCommas NOT LIKE '%'+CAST(T.DependsOn AS NVARCHAR(100))+'%'--LEN(DependsWithCommas)<50
)
SELECT * INTO #TempNew
FROM Final_CTE
SELECT * FROM #TempNew
SELECT TT.* INTO #TempFinal FROM
(SELECT TN.Source_ID,TN.DependsWithCommas,TN.DepLen FROM #TempNew TN
WHERE LTRIM(RTRIM(TN.DependsWithCommas)) LIKE '%'+CAST(TN.Source_ID AS NVARCHAR(100))+'%') TT
--AND DepLen = (SELECT MAX(TN.DepLen) FROM #TempNew TN GROUP BY TN.Source_ID )
INNER JOIN
(SELECT Source_ID, MAX(DepLen) AS MaxLen FROM #TempNew GROUP BY Source_ID) GM
ON GM.Source_ID = TT.Source_ID AND GM.MaxLen=TT.DepLen
SELECT * FROM #TempFinal
SELECT Distinct DependsWithCommas from #TempFinal
--DROP TABLE #Temp
--DROP TABLE #TempNew
-- DROP TABLE #TempFinal
March 9, 2012 at 8:10 am
There are lots better ways to concatenate with commas than you have there. Look around for the XML Path method. When you find that, consider keeping 2 copies of the concatenated string, one in the dependency order and one just in numeric order. The second will allow you to identify duplicates (probably with Row_Number)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply