Data wrong retrieved when using parallel loop

Question

0.00/5 (No votes)

See more:

Before this, I encouter a problem when my for loop takes a very long time to execute and cause the time out. I have successfully solved the problem by using parallel loop which has been introduced by our friend here[^]

The problem now is, some data retrieved are wrong when compared to original data where the data has been taken. Is that anything to do with the loop?
Here is the code.

VB

Parallel.For(0, dtMc.Rows.Count, Sub(i)
                                                                                       Dim mcno As String = dtMc.Rows(i)("MCNO_N")

                                                                                       Dim sqlPC As String = "Select PARTCODE_T FROM MC_PARTCODE_MASTER WHERE MCNO_N = '" & mcno & "'"
                                            Dim pc As String = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlPC, connEPJSetting)

    Dim sqlMcType As String = "SELECT MCTYPE_T FROM MC_MASTER WHERE MCNO_N = '" & mcno & "' AND DEPTID_T = 'PD1'  "
                                            Dim mctype As String = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlMcType, connEPJSetting)

                                            If Not mctype Is Nothing Or Not IsDBNull(mctype) Then
                                                type = "'" & mctype & "'"
                                            Else
                                                type = "NULL"
                                            End If
                                            If Not pc Is Nothing Then
                                                partcode = "'" & pc & "'"


                                                Dim sqlMpInspect As String = "SELECT HEADER.GROUPCODE_T, HEADER.MONTH_T,HEADER.SHOPGROUP_T, HEADER.MCNO_N, HEADER.PARTC" & _
                                                                                "ODE_T, HEADER.ACTCT_T, dbo.MCINFO_DETAILS.ROLEID_N, EPJ_Employee.dbo.E" & _
                                                                                "MPLOYEE_DATA.NAME_T FROM (SELECT GROUPCODE_T, MONTH_T, MCNO_N, PARTCOD" & _
                                                                                "E_T, ACTCT_T,  SHOPGROUP_T FROM dbo.MCINFO_HEADER WHERE (MONTH_T = (SELECT MAX(MONTH" & _
                                                                                "_T) AS MAXMTH FROM dbo.MCINFO_HEADER AS MCINFO_HEADER_1 WHERE (MCNO_N " & _
                                        "= '" & mcno & "'))) AND (MCNO_N = '" & mcno & "') AND (PARTCODE_T = '" & pc & "')) AS" & _
                                        " HEADER INNER JOIN dbo.MCINFO_DETAILS ON HEADER.MONTH_T = dbo.MCINFO_D" & _
                                        "ETAILS.MONTH_T AND HEADER.MCNO_N = dbo.MCINFO_DETAILS.MCNO_N AND HEADE" & _
                                        "R.PARTCODE_T = dbo.MCINFO_DETAILS.PARTCODE_T INNER JOIN EPJ_Employee.d" & _
                                        "bo.EMPLOYEE_DATA ON dbo.MCINFO_DETAILS.EMPID_T = EPJ_Employee.dbo.EMPL" & _
                                        "OYEE_DATA.EMPID_T WHERE (dbo.MCINFO_DETAILS.ROLEID_N = 2) "
                                                Dim dtInspect As DataRow = DBLayer.Util.Data.MsSql.DBFunction.executeRowScalar(sqlMpInspect, connEPJSetting)

                                                If Not dtInspect Is Nothing Then
                                                    act = "'" & dtInspect("ACTCT_T") & "'"
                                                    groupcode = "'" & dtInspect("GROUPCODE_T") & "'"
                                                    leader = "'" & dtInspect("NAME_T") & "'"
                                                    shopgroup = "'" & dtInspect("SHOPGROUP_T") & "'"

                                                    Dim sqlMpPlanning As String = "SELECT * FROM Q_MPPLANNING_MCLOC WHERE ITEM_CODE_T = '" & pc & "' "
                                                    Dim dtPlanning As DataRow = DBLayer.Util.Data.MsSql.DBFunction.executeRowScalar(sqlMpPlanning, connEPJSetting)


                                                    If Not dtPlanning Is Nothing Then
                                                        Sct = "'" & dtPlanning("CT_N") & "'"
                                                        matcode = "'" & dtPlanning("DIMENSION_T") & "'"
                                                        calibration = "'" & dtPlanning("CALIBRATION_T") & "'"

                                                    Else
                                                        Sct = "NULL"
                                                        matcode = "NULL"
                                                        calibration = "NULL"
                                                    End If

                                                Else
                                                    act = "NULL"
                                                    groupcode = "NULL"
                                                    leader = "NULL"
                                                    shopgroup = "NULL"
                                                    Sct = "NULL"
                                                    matcode = "NULL"
                                                    calibration = "NULL"
                                                End If

                                                Dim sqlMCStat As String = "SELECT MCSTATID_N FROM MCPLAN_DATA PLANDATA " & _
                                                                                             " WHERE PLANDATA.MCNO_N = '" & mcno & "' AND CONVERT(DATE, DATE_D, 102) = CONVERT(DATE, GETDATE(), 102) "

                                                Dim drMcStatus As DataRow = DBLayer.Util.Data.MsSql.DBFunction.executeRowScalar(sqlMCStat, connEPJSetting)


                                                If drMcStatus Is Nothing Then
                                                    stat = "NULL"
                                                Else
                                                    stat = "'" & drMcStatus("MCSTATID_N") & "'"
                                                End If


                                            Else
                                                Sct = "NULL"
                                                matcode = "NULL"
                                                calibration = "NULL"
                                                act = "NULL"
                                                groupcode = "NULL"
                                                leader = "NULL"
                                                stat = "NULL"
                                                partcode = "NULL"
                                                shopgroup = "NULL"
                                                'type = "NULL"

                                            End If

                                           Dim str As String = "UPDATE MC_INFO_TRACKING SET GROUP_T = " & groupcode & ", LEADER_T = " & leader & ", PARTCODE_T= " & partcode & " , ACTCT_T = " & act & ", MATCODE_T = " & matcode & ", CALIBER_T = " & calibration & ", MCSTAT_N = " & stat & ", DATEMODIFY_D = GETDATE(), STD_CT_T = " & Sct & " WHERE MCNO_N = '" & mcno & "'"
                                            Dim a As Object = DBLayer.Util.Data.MsSql.DBFunction.executeNonQuery(str, connEPJSetting)

                                            Dim strB As String = "UPDATE MC_INFO_TRACKING_HEADER SET MC_TYPE_CODE_T = " & type & " WHERE MCNO_N = '" & mcno & "'"
                                            Dim b As Object = DBLayer.Util.Data.MsSql.DBFunction.executeNonQuery(strB, connEPJSetting)

                                        End Sub)

Updated:

After doing some study on my code, I tried to retrieve just one data and debug it one by one. Look example below.

No	Item
701	00273991L-T7N
702	00361541L-T7N
703	00273991L-T7N
704	00361372L-T7S
705	0027268KL-T7N

see no 701 and 703. For no 703, the loop reverse back and filter Item for no 701. the item no for 703 should be 02725100L-T7N. Why is that happening?

Posted 17-Dec-12 23:21pm

snamyna

Updated 18-Dec-12 0:34am

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 18-Dec-12 20:10pm

So, "successfully solved" was a big exaggeration... This is called "race condition", check it out.
—SA

3 solutions

Solution 1

Was the question really "why is that happening"? If so, my respect. You don't just want to kick the problem until the results satisfy you, but want to understand the problem.

Very basically, here is why: http://en.wikipedia.org/wiki/Race_condition[^].

The solution? Well, this is not so easy. Perhaps you would need to go to sequential execution again. Basically, you need to identify dependencies and ensure proper sequence of operations according to them. Easier to say then to solve it. By the way, if and when you synchronize depending parts of elaboration, you gain in throughput may go down and even become negative.

—SA

Posted 18-Dec-12 14:15pm

Sergey Alexandrovich Kryukov

Solution 2

Hi Again,

It is impossible to see each line of code but it seems you are using any variable in the loop which is changing the by another thread of loop, while it is in use by previous one.

Here one thing I need to say, while using “Multithreading” you will have to very careful about variables. It can bring disaster to you program. Parallel Loop is new thing using multi threading inside. So you need to be careful.

Solution is: Intelligent use of variable.

RKS

Posted 18-Dec-12 18:37pm

Rahul K Singh

Comments

snamyna 19-Dec-12 4:23am

This is my first time using parallel loop. I need some time to study more about it. I am now studying this article about parallel loop. http://msdn.microsoft.com/en-us/library/ff963552.aspx

Hope it can increase my understandings. In a meanwhie, hope somebody can really explain about this.

snamyna 19-Dec-12 22:39pm

Hye Rahul.. can u take a look at my code and see which variables actually cause the value to mix up? Just take one example in my code.Tq

snamyna 19-Dec-12 22:48pm

I have looked at Interlocked function when using Parallel Loop but i have no idea how to use it. So, i googled around and found a method which Interlocked is not necessary.

Private Sub b()

Dim sqlMc As String = "SELECT MCNO_N FROM MC_LAYOUTTEST"
Dim dtMc As DataTable = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(sqlMc, connEPJSetting)
Dim mc = (From row In dtMc Select colB = row(0).ToString).ToArray

Parallel.For(0, mc.Length, Sub(x) mc(x) = getItem(mc))

End Sub

Function getItem(ByVal mc As String()) As String

Dim pc As String = ""

For Each d As Integer In mc

Dim sqlPC As String = "Select PARTCODE_T FROM MC_PARTCODE_MASTER WHERE MCNO_N = '" & d & "'"
pc = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlPC, connEPJSetting)

Dim str As String = "UPDATE MC_LAYOUTTEST SET PARTCODE_T= '" & pc & "' WHERE MCNO_N = '" & d & "'"
Dim a As Object = DBLayer.Util.Data.MsSql.DBFunction.executeNonQuery(str, connEPJSetting)

Next

Return pc

End Function

mc is declared as INT in database design. After executing the code, I successfully get the right item for each no. After finish executing all no, the loop passes items values to d variable to get pc value at this statement -> Dim sqlPC As String = "Select PARTCODE_T FROM MC_PARTCODE_MASTER WHERE MCNO_N = '" & d & "'".
Why is the loop didnt stop although all array items has been processed?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

snamyna · Accepted Answer · 2012-12-23T13:55:00

Solution 3

I moved all variable declaration inside the Parallel Loop function and it goes well. I dont know whether that's the best solution but I think that solves race condition occured in the function. Thanks all for the feedback. :)

Posted 23-Dec-12 13:55pm

snamyna

Updated 23-Dec-12 22:19pm

v2

Comments

Rahul K Singh 23-Dec-12 23:52pm

Test it as much as possible. Good Luck :)