Problem with concurrent insert into a partitioned table

  • Hi

    i've a problem with the concurrent insert into a partitioned table. I have two processes which write data to different partitions at the same time. But one process will nevertheless locked until the first process is finished.

    Following situation:

    - Target Table:

    CREATE TABLE t_target

    (

    [pa_Attribut] int NOT NULL,

    [Id] int NOT NULL,

    [SpalteA] varchar(50) NOT NULL,

    [SpalteB] varchar(50) NOT NULL,

    [SpalteC] varchar(50) NOT NULL,

    CONSTRAINT [pk_target]

    PRIMARY KEY CLUSTERED ([pa_Attribut] ASC, [Id] ASC)

    ON [ps_part1]([pa_Attribut])

    )

    ON [ps_part1]([pa_Attribut])

    - Process 1

    INSERT INTO t_target

    SELECT 1 as pa_Attribut

    , s.id

    ,s.SpalteA

    ,s.SpalteB

    ,s.SpalteC

    FROM

    (

    SELECT * from t_quelle_1

    ) s

    - Process 2

    INSERT INTO t_target

    SELECT 2 as pa_Attribut

    , s.id

    ,s.SpalteA

    ,s.SpalteB

    ,s.SpalteC

    FROM

    (

    SELECT * from t_quelle_2

    ) s

    Both processes are scheduled at the same time. But process 2 still get the lock "LCK_M_IS" until process 1 is running.

    I've analyzed the system table sys.dm_tran_locks. If only process 1 is running following locks are set:

    resource_typeDBNameresource_associated_entity_idObjectrequest_moderequest_statusrequest_session_id

    HOBTcrm72057598424973300Partition 1 v. t_targetXGRANT1

    OBJECTcrm2066106401Tabelle t_targetIXGRANT1

    If then process 2 is started it gets the lock "LCK_M_IS" in the activity monitor and the following entries are in system table sys.dm_tran_locks.

    resource_typeDBNameresource_associated_entity_idrequest_moderequest_statusrequest_session_id

    HOBTcrm72057598424973300Partition 1 v. t_targetSch-SGRANT2

    HOBTcrm72057598424973300Partition 1 v. t_targetXGRANT1

    HOBTcrm72057598424973300Partition 1 v. t_targetISCONVERT2

    OBJECTcrm2066106401Tabelle t_targetIXGRANT2

    OBJECTcrm2066106401Tabelle t_targetIXGRANT1

    Why needs process 2 access to partition 1 (first entry in above listed table)?

    Is it possible that process 2 always needs for a short time an exclusive lock on the whole table? Or what else can be the problem? Can I change anything at the insert syntax so that both processes can work at once? Strangely if i execute a delete (delete from t_target where pa_Attribut = 2) instead of the insert into partition 2 this works in parallel without any locking. So I don't understand it.

    Has anybody an idea?

  • Is there a reason why not do it like this (not that it'll make any difference in the locking)?

    INSERT INTO t_target

    SELECT 1 as pa_Attribut

    ,id

    ,SpalteA

    ,SpalteB

    ,SpalteC

    FROM

    t_quelle_1

    You might try to do the insert on a separate table and switch it into the partitioned one and see if that helps.

  • Hi,

    the listed insert statement is only an example. The mentioned subselect is in reality more complex. So I've only simplified it.

    Yes perhaps it could work over the indirect way by a temp table and switch partition. But the problem exists not only for one process. There are many other stored procedures with the same behaviour. So it would be much work to change all processes.

    So once again the question what the problem could be and what else I can do?

  • mr_effe (4/23/2014)


    Hi,

    Yes perhaps it could work over the indirect way by a temp table and switch partition. But the problem exists not only for one process. There are many other stored procedures with the same behaviour. So it would be much work to change all processes.

    So once again the question what the problem could be and what else I can do?

    It's hard to tell without seeing the sql. But if possible, test the 'switch partition' scenario. If the partitions are empty, that would be the cleanest way to do it. I'll let you know if I think of something.

  • But can you explain me why a parallel insert and delete in two different Partition works meanwhile a parallel insert in the Same partitions will be blocked ?

    This behaviour should be have a logical reason I think !?!

  • Do you have any other indexes on the table? If so, are the "aligned" with the partitions?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No, there aren"t any other indices

  • Hi,

    I could solve the problem. Inside the subselect there was an inline function which prevent the parallel processing. I put this function in front of the insert into a variable and then it works.

    Many thanks for your answers.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply