April 23, 2014 at 7:14 am
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?
April 23, 2014 at 8:13 am
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.
April 23, 2014 at 8:24 am
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?
April 23, 2014 at 9:51 am
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.
April 23, 2014 at 12:03 pm
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 !?!
April 23, 2014 at 8:42 pm
Do you have any other indexes on the table? If so, are the "aligned" with the partitions?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2014 at 11:29 pm
No, there aren"t any other indices
April 24, 2014 at 2:42 am
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