Problem with concurrent insert into a partitioned table

Question

Post reply

Problem with concurrent insert into a partitioned table

mr_effe

SSC Veteran

Points: 293
More actions
April 23, 2014 at 7:14 am

#292649

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?

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

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

deepforest SSCommitted Points: 1780 More actions · Answer 1

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.

mr_effe SSC Veteran Points: 293 More actions · Answer 2

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?

deepforest SSCommitted Points: 1780 More actions · Answer 3

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.

mr_effe SSC Veteran Points: 293 More actions · Answer 4

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 !?!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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)

mr_effe SSC Veteran Points: 293 More actions · Answer 6

mr_effe

SSC Veteran

Points: 293

April 23, 2014 at 11:29 pm

#1708300

No, there aren"t any other indices

mr_effe SSC Veteran Points: 293 More actions · Answer 7

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.