locked
Parallel calls to EntityMembersCreate causing deadlocks RRS feed

  • Question

  • Greetings,

    Has anyone observed dead locks when calling multiple EntityMemberCreate in parallel?

    We're using TIBCO ESB to insert data into MDS (via Web Services) from upstream systems. TIBCO allows for data on the bus to be consumed by multiple threads which in turn make a call to EntityMembersCreate and ValidateProcess for a single record.

    With 10 or more records on the bus most of the records are not created due to deadlocks in the MDS repo.

    The following is an example deadlock graph from SQLProfile trace.

    Has anyone come across this before?

    Any suggestions would be greatly appreciated (other than configuring TIBCO to use single thread).

    Cheers

    <deadlock-list>
     <deadlock victim="processd9b3a508">
      <process-list>
       <process id="processd9b3a508" taskpriority="0" logused="1148" waitresource="KEY: 42:72057594069450752 (0cb5f7c380d7)" waittime="4640" ownerId="205544227" transactionname="user_transaction" lasttranstarted="2011-10-12T09:32:37.603" XDES="0xc4ca13c0" lockMode="U" schedulerid="1" kpid="788" status="suspended" spid="120" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-10-12T09:32:37.600" lastbatchcompleted="2011-10-12T09:32:37.600" clientapp=".Net SqlClient Data Provider" hostname="UDVBIERW001" hostpid="3636" loginname="S-1-9-3-4262486836-1146880358-3282037932-1022498585" isolationlevel="read committed (2)" xactid="205544227" currentdb="42" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="2" stmtstart="16" sqlhandle="0x02000000c1e710020321c51bb3a9d7f983225a7e88b81c7d">
                update mdm . [tbl_2_27_EN] set VersionMember_ID = ID where ID = @0     </frame>
         <frame procname="adhoc" line="2" stmtstart="10" sqlhandle="0x02000000061e902b2ae93a98cc672cd5a7d85a37b9f1b771">
                UPDATE mdm.[tbl_2_27_EN] SET VersionMember_ID = ID
                WHERE ID = 1322;     </frame>
         <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">
                sp_executesql     </frame>
         <frame procname="MDS.mdm.udpMemberCreate" line="144" stmtstart="10130" stmtend="10436" sqlhandle="0x03002a00c682cb324df0a5004a9f00000100000000000000">
                EXEC sp_executesql @SQL;
                --Create the hierarchy relationship(s) and set the parent to 0 (Root). Children are assigned to all hierarchies.     </frame>
        </executionStack>
        <inputbuf> Proc [Database Id = 42 Object Id = 852198086]    </inputbuf>
       </process>
       <process id="processd9b3b048" taskpriority="0" logused="1148" waitresource="KEY: 42:72057594069450752 (0cb5f7c380d7)" waittime="4640" ownerId="205544271" transactionname="user_transaction" lasttranstarted="2011-10-12T09:32:37.607" XDES="0xc4c663b0" lockMode="U" schedulerid="1" kpid="6092" status="suspended" spid="123" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-10-12T09:32:37.603" lastbatchcompleted="2011-10-12T09:32:37.603" clientapp=".Net SqlClient Data Provider" hostname="UDVBIERW001" hostpid="3636" loginname="S-1-9-3-4262486836-1146880358-3282037932-1022498585" isolationlevel="read committed (2)" xactid="205544271" currentdb="42" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="2" stmtstart="16" sqlhandle="0x02000000c1e710020321c51bb3a9d7f983225a7e88b81c7d">
                update mdm . [tbl_2_27_EN] set VersionMember_ID = ID where ID = @0     </frame>
         <frame procname="adhoc" line="2" stmtstart="10" sqlhandle="0x020000000d8ddb20f7fa36c42282dd1f0d8c127db64151eb">
                UPDATE mdm.[tbl_2_27_EN] SET VersionMember_ID = ID
                WHERE ID = 1323;     </frame>
         <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">
                sp_executesql     </frame>
         <frame procname="MDS.mdm.udpMemberCreate" line="144" stmtstart="10130" stmtend="10436" sqlhandle="0x03002a00c682cb324df0a5004a9f00000100000000000000">
                EXEC sp_executesql @SQL;
                --Create the hierarchy relationship(s) and set the parent to 0 (Root). Children are assigned to all hierarchies.     </frame>
        </executionStack>
        <inputbuf> Proc [Database Id = 42 Object Id = 852198086]    </inputbuf>
       </process>
       <process id="processeaafcbc8" taskpriority="0" logused="1436" waitresource="KEY: 42:72057594069450752 (30974298be2d)" waittime="4640" ownerId="205544136" transactionname="user_transaction" lasttranstarted="2011-10-12T09:32:37.597" XDES="0xe1a75950" lockMode="U" schedulerid="1" kpid="5728" status="suspended" spid="121" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-10-12T09:32:37.583" lastbatchcompleted="2011-10-12T09:32:37.583" clientapp=".Net SqlClient Data Provider" hostname="UDVBIERW001" hostpid="3636" loginname="S-1-9-3-4262486836-1146880358-3282037932-1022498585" isolationlevel="read committed (2)" xactid="205544136" currentdb="42" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="2" stmtstart="16" sqlhandle="0x02000000c1e710020321c51bb3a9d7f983225a7e88b81c7d">
                update mdm . [tbl_2_27_EN] set VersionMember_ID = ID where ID = @0     </frame>
         <frame procname="adhoc" line="2" stmtstart="10" sqlhandle="0x02000000ecf2ea161c1d987956abc7370283e411bc16eab4">
                UPDATE mdm.[tbl_2_27_EN] SET VersionMember_ID = ID
                WHERE ID = 1321;     </frame>
         <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">
                sp_executesql     </frame>
         <frame procname="MDS.mdm.udpMemberCreate" line="144" stmtstart="10130" stmtend="10436" sqlhandle="0x03002a00c682cb324df0a5004a9f00000100000000000000">
                EXEC sp_executesql @SQL;
                --Create the hierarchy relationship(s) and set the parent to 0 (Root). Children are assigned to all hierarchies.     </frame>
        </executionStack>
        <inputbuf> Proc [Database Id = 42 Object Id = 852198086]    </inputbuf>
       </process>
      </process-list>
      <resource-list>
       <keylock hobtid="72057594069450752" dbid="42" objectname="MDS.mdm.tbl_2_27_EN" indexname="pk_tbl_2_27_EN" id="lock15f366300" mode="X" associatedObjectId="72057594069450752">
        <owner-list/>
        <waiter-list>
         <waiter id="processd9b3a508" mode="U" requestType="wait"/>
        </waiter-list>
       </keylock>
       <keylock hobtid="72057594069450752" dbid="42" objectname="MDS.mdm.tbl_2_27_EN" indexname="pk_tbl_2_27_EN" id="lock15f366300" mode="X" associatedObjectId="72057594069450752">
        <owner-list>
         <owner id="processeaafcbc8" mode="X"/>
        </owner-list>
        <waiter-list>
         <waiter id="processd9b3b048" mode="U" requestType="wait"/>
        </waiter-list>
       </keylock>
       <keylock hobtid="72057594069450752" dbid="42" objectname="MDS.mdm.tbl_2_27_EN" indexname="pk_tbl_2_27_EN" id="lockfee94d00" mode="X" associatedObjectId="72057594069450752">
        <owner-list>
         <owner id="processd9b3a508" mode="X"/>
        </owner-list>
        <waiter-list>
         <waiter id="processeaafcbc8" mode="U" requestType="wait"/>
        </waiter-list>
       </keylock>
      </resource-list>
     </deadlock>
    </deadlock-list>

    Thursday, October 13, 2011 5:36 AM

Answers

  • We fixed lot of deadlock issues in SQL 2012. As RC0 is already out, I would move to the newer version.
    Monday, November 21, 2011 8:26 PM
  • Hi gnem

    I have checked the codes of udpMemberCreate.

    You are right, if we comment out below executing line. Then no dead lock issue anymore. I have verified this.

    SET @SQL = N'   

     UPDATE mdm.' + quotename(@TableName) + N' SET VersionMember_ID = ID   

     WHERE ID = ' + CONVERT(NVARCHAR(30), @Member_ID) + N';'   

     EXEC sp_executesql @SQL; 

    I believe the deadlock problem is created because of below 2 issues

    1) SET @Member_ID = SCOPE_IDENTITY() works fine for single thread, but if we have multiple threads execute the same clause 

      they will have the same value of IDs, causing next updating fail (while inserting is fine)

    2) Threads when updating the table for Id field will add exclusive locks to the table. While some other threads may have table scan on the same table.

    To fix this issue, we should remove the update clause ( it will cause deadlock) and provide VersionMember_ID field when inserting the record, so below codes need  to be changed.

    • IN SQL 2012(Denali), we can use sequence ( similar as Oracle) to fix this issue.
    • IF we use IDENT_CURRENT(@TableName) + IDENT_INCR(@TableName) as VersionMember_Id, then it may also have problem when multi-thread involves 
    • create a custom made function to simulate getting a unique number for each executing
    • Or make a compensation update ( VersionMemebr_Id=Id  WHERE VersionMemebr_Id IS NULL ) when parallel member creation done in a ( SQL Job or sth)

     

    And  -- EXEC sp_executesql @SQL;  Comment out the update 

    ------------------------------------Test Result--------------------------------------------------------------------------------------

    8 new members created successfully, but as no Version_Member_Id filled, they can only be found in the table

    Yiqian

     


    Monday, November 28, 2011 5:44 AM
  • Hi Gnem

    In the SP of udpMemberCodeCheck, it will lock the tables for duplicate check. if you add with  WITH(NOLOCK) , then it solves the issue. Otherwise concurrent SET @Count will block each other definitely. 

    There are some cases in MDS that TPL can not work with. 

    Here are the updated SP:

     

    USE [MDS]

    GO

    /****** Object:  StoredProcedure [mdm].[udpMemberCodeCheck]    Script Date: 11/16/2011 14:16:47 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [mdm].[udpMemberCodeCheck]  

    @Version_ID INT, @Entity_ID INT, @MemberCode NVARCHAR (250), @Return TINYINT=NULL OUTPUT  

    WITH EXECUTE AS N'mds_schema_user'  

    AS  

    BEGIN  

     SET NOCOUNT ON;  

     

     /****************************************/  

     /* 0(FALE) - DOES NOT EXIST */  

     /* 1(TRUE) - DOES EXIST */  

     /****************************************/  

     

     DECLARE @EntityTable   sysname,  

     @HierarchyParentTable   sysname,  

     @CollectionTable   sysname,  

     @IsFlat BIT,  

     @SQL  NVARCHAR(MAX),  

     @Count  INT;  

     

     SELECT  

     @EntityTable = EntityTableName,  

     @HierarchyParentTable = HierarchyParentTableName,  

    @CollectionTable = CollectionTableName,  

    @IsFlat = IsFlat  

    FROM  

    [mdm].[viw_SYSTEM_TABLE_NAME] WHERE ID = @Entity_ID;  

     

    SET @MemberCode = (SELECT (LTRIM(RTRIM(@MemberCode))))  

     

    --Check type 1  

    SET @SQL = N'  

    SET @Count = CASE   

    WHEN EXISTS(SELECT 1 FROM mdm.' + quotename(@EntityTable) + N' WITH(NOLOCK)  WHERE Version_ID = ' + CONVERT(NVARCHAR(30),@Version_ID) + N'  

    AND Code = @MemberCodeParam ) THEN 1  

    ELSE 0  

    END; --case';  

    EXEC sp_executesql @SQL, N'@MemberCodeParam NVARCHAR(250),@Count INT OUTPUT', @MemberCode,@Count OUTPUT;  

    SET @Count = ISNULL(@Count, 0);  

     

    --Check Type 2  

    IF @IsFlat = 0 AND @Count = 0 BEGIN  

    SET @SQL = N'  

    SET @Count = CASE   

    WHEN EXISTS(SELECT 1 FROM mdm.' + quotename(@HierarchyParentTable) + N' WITH(NOLOCK)  WHERE Version_ID = ' + CONVERT(NVARCHAR(30),@Version_ID) + N'  

    AND Code = @MemberCodeParam ) THEN 1  

    ELSE 0  

    END; --case';  

    EXEC sp_executesql @SQL, N'@MemberCodeParam NVARCHAR(250),@Count INT OUTPUT', @MemberCode,@Count OUTPUT;  

    SET @Count = ISNULL(@Count, 0);  

    END; --if  

     

    --Check Type 3  

    IF @IsFlat = 0 AND @Count = 0 BEGIN  

    SET @SQL = N'  

    SET @Count = CASE  

    WHEN EXISTS(SELECT 1 FROM mdm.' + quotename(@CollectionTable) + N' WITH(NOLOCK)  WHERE Version_ID = ' + CONVERT(NVARCHAR(30),@Version_ID) + N'  

    AND Code = @MemberCodeParam ) THEN 1  

    ELSE 0  

    END; --case';  

    EXEC sp_executesql @SQL, N'@MemberCodeParam NVARCHAR(250),@Count INT OUTPUT', @MemberCode,@Count OUTPUT;  

    SET @Count = ISNULL(@Count, 0);  

    END; --if  

     

    IF @Count = 0 BEGIN  

    SET @Return = 0 --False does not exist  

    END ELSE BEGIN  

    SET @Return = 1  -- True it does exist  

    END; --if  

     

    --Check from ROOT  

    IF LEN(@MemberCode)=0 OR UPPER(@MemberCode) = 'ROOT'  

    BEGIN  

    SET @Return = 1  -- True it does exist  

    END   

     

    --Check from UnUsed(Non mandatory hierarchies)  

        IF UPPER(@MemberCode) = 'MDMUNUSED'  

    BEGIN  

    SET @Return = 1  -- True it does exist  

    END   

     

    SET NOCOUNT OFF;  

    END;

     

     

    Here is the service test codes: ( I have a model Vehicle, with entity also as Vehicle)

     

    List<string> vehicles=new  List<string>(){"06","07"};

                Parallel.ForEach(vehicles, vehicle =>

                    {

                        Volvo.MasterDataServices.Services.Service target0 = new Volvo.MasterDataServices.Services.Service();

                        EntityMembersCreateRequest request0 = new EntityMembersCreateRequest();

                        EntityMembers entityMembers0 = new EntityMembers();

                        System.Collections.ObjectModel.Collection<Member> members0 = new System.Collections.ObjectModel.Collection<Member>();

                        entityMembers0.Members = members0;

                        entityMembers0.EntityId = new Identifier() { Name = "Vehicle" };

                        entityMembers0.ModelId = new Identifier() { Name = "Vehicle" };

                        entityMembers0.VersionId = new Identifier() { Name = "VERSION_1" };

                        Member member0 = new Member();

                        MemberIdentifier memberIdentifier0 = new MemberIdentifier();

                        memberIdentifier0.Code = vehicle;

                        memberIdentifier0.MemberType = MemberType.Leaf;

                        memberIdentifier0.Id = new Guid();

                        memberIdentifier0.Name = vehicle;

                        member0.MemberId = memberIdentifier0;

                        members0.Add(member0);

                        request0.Members = entityMembers0;

                        request0.Members.MemberType = MemberType.Leaf;

                        target0.EntityMembersCreate(request0);

                    }

                 );

                Console.Read();

     

     

    And the process goes to Console.Read and 2 new entity members created successfully

    Yiqian


    Wednesday, November 16, 2011 6:35 AM

All replies

  • Hi Gnem

    In the SP of udpMemberCodeCheck, it will lock the tables for duplicate check. if you add with  WITH(NOLOCK) , then it solves the issue. Otherwise concurrent SET @Count will block each other definitely. 

    There are some cases in MDS that TPL can not work with. 

    Here are the updated SP:

     

    USE [MDS]

    GO

    /****** Object:  StoredProcedure [mdm].[udpMemberCodeCheck]    Script Date: 11/16/2011 14:16:47 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [mdm].[udpMemberCodeCheck]  

    @Version_ID INT, @Entity_ID INT, @MemberCode NVARCHAR (250), @Return TINYINT=NULL OUTPUT  

    WITH EXECUTE AS N'mds_schema_user'  

    AS  

    BEGIN  

     SET NOCOUNT ON;  

     

     /****************************************/  

     /* 0(FALE) - DOES NOT EXIST */  

     /* 1(TRUE) - DOES EXIST */  

     /****************************************/  

     

     DECLARE @EntityTable   sysname,  

     @HierarchyParentTable   sysname,  

     @CollectionTable   sysname,  

     @IsFlat BIT,  

     @SQL  NVARCHAR(MAX),  

     @Count  INT;  

     

     SELECT  

     @EntityTable = EntityTableName,  

     @HierarchyParentTable = HierarchyParentTableName,  

    @CollectionTable = CollectionTableName,  

    @IsFlat = IsFlat  

    FROM  

    [mdm].[viw_SYSTEM_TABLE_NAME] WHERE ID = @Entity_ID;  

     

    SET @MemberCode = (SELECT (LTRIM(RTRIM(@MemberCode))))  

     

    --Check type 1  

    SET @SQL = N'  

    SET @Count = CASE   

    WHEN EXISTS(SELECT 1 FROM mdm.' + quotename(@EntityTable) + N' WITH(NOLOCK)  WHERE Version_ID = ' + CONVERT(NVARCHAR(30),@Version_ID) + N'  

    AND Code = @MemberCodeParam ) THEN 1  

    ELSE 0  

    END; --case';  

    EXEC sp_executesql @SQL, N'@MemberCodeParam NVARCHAR(250),@Count INT OUTPUT', @MemberCode,@Count OUTPUT;  

    SET @Count = ISNULL(@Count, 0);  

     

    --Check Type 2  

    IF @IsFlat = 0 AND @Count = 0 BEGIN  

    SET @SQL = N'  

    SET @Count = CASE   

    WHEN EXISTS(SELECT 1 FROM mdm.' + quotename(@HierarchyParentTable) + N' WITH(NOLOCK)  WHERE Version_ID = ' + CONVERT(NVARCHAR(30),@Version_ID) + N'  

    AND Code = @MemberCodeParam ) THEN 1  

    ELSE 0  

    END; --case';  

    EXEC sp_executesql @SQL, N'@MemberCodeParam NVARCHAR(250),@Count INT OUTPUT', @MemberCode,@Count OUTPUT;  

    SET @Count = ISNULL(@Count, 0);  

    END; --if  

     

    --Check Type 3  

    IF @IsFlat = 0 AND @Count = 0 BEGIN  

    SET @SQL = N'  

    SET @Count = CASE  

    WHEN EXISTS(SELECT 1 FROM mdm.' + quotename(@CollectionTable) + N' WITH(NOLOCK)  WHERE Version_ID = ' + CONVERT(NVARCHAR(30),@Version_ID) + N'  

    AND Code = @MemberCodeParam ) THEN 1  

    ELSE 0  

    END; --case';  

    EXEC sp_executesql @SQL, N'@MemberCodeParam NVARCHAR(250),@Count INT OUTPUT', @MemberCode,@Count OUTPUT;  

    SET @Count = ISNULL(@Count, 0);  

    END; --if  

     

    IF @Count = 0 BEGIN  

    SET @Return = 0 --False does not exist  

    END ELSE BEGIN  

    SET @Return = 1  -- True it does exist  

    END; --if  

     

    --Check from ROOT  

    IF LEN(@MemberCode)=0 OR UPPER(@MemberCode) = 'ROOT'  

    BEGIN  

    SET @Return = 1  -- True it does exist  

    END   

     

    --Check from UnUsed(Non mandatory hierarchies)  

        IF UPPER(@MemberCode) = 'MDMUNUSED'  

    BEGIN  

    SET @Return = 1  -- True it does exist  

    END   

     

    SET NOCOUNT OFF;  

    END;

     

     

    Here is the service test codes: ( I have a model Vehicle, with entity also as Vehicle)

     

    List<string> vehicles=new  List<string>(){"06","07"};

                Parallel.ForEach(vehicles, vehicle =>

                    {

                        Volvo.MasterDataServices.Services.Service target0 = new Volvo.MasterDataServices.Services.Service();

                        EntityMembersCreateRequest request0 = new EntityMembersCreateRequest();

                        EntityMembers entityMembers0 = new EntityMembers();

                        System.Collections.ObjectModel.Collection<Member> members0 = new System.Collections.ObjectModel.Collection<Member>();

                        entityMembers0.Members = members0;

                        entityMembers0.EntityId = new Identifier() { Name = "Vehicle" };

                        entityMembers0.ModelId = new Identifier() { Name = "Vehicle" };

                        entityMembers0.VersionId = new Identifier() { Name = "VERSION_1" };

                        Member member0 = new Member();

                        MemberIdentifier memberIdentifier0 = new MemberIdentifier();

                        memberIdentifier0.Code = vehicle;

                        memberIdentifier0.MemberType = MemberType.Leaf;

                        memberIdentifier0.Id = new Guid();

                        memberIdentifier0.Name = vehicle;

                        member0.MemberId = memberIdentifier0;

                        members0.Add(member0);

                        request0.Members = entityMembers0;

                        request0.Members.MemberType = MemberType.Leaf;

                        target0.EntityMembersCreate(request0);

                    }

                 );

                Console.Read();

     

     

    And the process goes to Console.Read and 2 new entity members created successfully

    Yiqian


    Wednesday, November 16, 2011 6:35 AM
  • Hi Hu,

    Thanks for your reply.

    The trace file with the lock was a stored proc udpMemberCreate which doesn't seem to call udpMemberCodeCheck.

    But along similar lines in udpMemberCreate

    The primary key for data table (mdm.tbl_2_15_EN) is Version_ID, ID while the update doesn't include the first column of PK, which would cause a range scan on the table?

    Line 148:

     

    SET @SQL = N'

    UPDATE mdm.'

    + quotename(@TableName) +

    N' SET VersionMember_ID = ID

    WHERE ID = '

    + CONVERT(NVARCHAR(30), @Member_ID) + N';'

    EXEC sp_executesql @SQL;

    This shoul include Version_ID.

     

    Thoughts?

     

     

     

    Sunday, November 20, 2011 10:42 PM
  • We fixed lot of deadlock issues in SQL 2012. As RC0 is already out, I would move to the newer version.
    Monday, November 21, 2011 8:26 PM
  • Hi gnem

    Glad to see that Microsoft has noticed your issue.

    During my test on MDS with TPL, I did not encounter the same dead-lock problem with udpMemberCreate, while in fact I got it with udpMemberCodeCheck.

    I agree with you if the relevant SPs have a full table scan, then we may consider to change it.

    ( So udpMemberCreate may have similar issues, and I can take a look and try to fix it if you can not wait for SQL 2012 ).

     

    Yiqian



    • Edited by Hu yiqian Monday, November 28, 2011 6:09 AM
    Tuesday, November 22, 2011 4:57 AM
  • Hi gnem

    I have checked the codes of udpMemberCreate.

    You are right, if we comment out below executing line. Then no dead lock issue anymore. I have verified this.

    SET @SQL = N'   

     UPDATE mdm.' + quotename(@TableName) + N' SET VersionMember_ID = ID   

     WHERE ID = ' + CONVERT(NVARCHAR(30), @Member_ID) + N';'   

     EXEC sp_executesql @SQL; 

    I believe the deadlock problem is created because of below 2 issues

    1) SET @Member_ID = SCOPE_IDENTITY() works fine for single thread, but if we have multiple threads execute the same clause 

      they will have the same value of IDs, causing next updating fail (while inserting is fine)

    2) Threads when updating the table for Id field will add exclusive locks to the table. While some other threads may have table scan on the same table.

    To fix this issue, we should remove the update clause ( it will cause deadlock) and provide VersionMember_ID field when inserting the record, so below codes need  to be changed.

    • IN SQL 2012(Denali), we can use sequence ( similar as Oracle) to fix this issue.
    • IF we use IDENT_CURRENT(@TableName) + IDENT_INCR(@TableName) as VersionMember_Id, then it may also have problem when multi-thread involves 
    • create a custom made function to simulate getting a unique number for each executing
    • Or make a compensation update ( VersionMemebr_Id=Id  WHERE VersionMemebr_Id IS NULL ) when parallel member creation done in a ( SQL Job or sth)

     

    And  -- EXEC sp_executesql @SQL;  Comment out the update 

    ------------------------------------Test Result--------------------------------------------------------------------------------------

    8 new members created successfully, but as no Version_Member_Id filled, they can only be found in the table

    Yiqian

     


    Monday, November 28, 2011 5:44 AM