Deadlock may occur when executing a jobnet with the job scheduler.
[Occurrence conditions]
Using Microsoft SQL Server
Unnecessary implicit type conversion is performed when deleting or updating records in the table used by jobs cheduler. Therefore, table scans are more likely to occur.
When table scans are performed, locks are acquired on a page-by-page basis, which can lead to deadlocks.
This FAQ provides information on how to address this issue.
【Workaround】
Prevent unnecessary implicit type conversions.
Please download and apply the module (im_job_scheduler_8_0_7_later_specific_patch-1.0.0.imm) which is attached.
* Please apply the attached module after checking the notes and application (installation) method described below.
[Improvement/modification requirements included in the module]
The attachment module includes the following requirement:
A deadlock may occur when executing a jobnet with the jobs cheduler.
https://issue.intra-mart.jp/issues/29271
【Notes】
1. The attached module can be applied to intra-mart Accel Platform 2014 Spring (Granada) or later.
2. Although we have conducted thorough testing, please be sure to check the operation yourself when applying it to your environment.
Others are in accordance with the following product restrictions.
intra-mart Accel Platform 2014 Spring (Granada) and updated products listed on the left or later
4. The attached module executes SQL (*1) to change the VARCHAR type of the table below to the NVARCHAR type only once when intra-mart Accel Platform is started.
* imjob_monitor (table that manages job net execution status and execution results)
* imjob_monitor_task (table that manages detailed task information for each job on the monitor)
Since all copies of the above table are copied twice, if the number of records is large, it is assumed that it will take time to start intra-mart Accel Platform for the first time after applying the attached module.
Therefore, please carry this out when you can spare the time.
FYI, it took about 20 minutes to start the intra-mart Accel Platform under the following conditionswhen when we verified.
・imjob_monitor: Approximately 2 million items
・imjob_monitor_task: Approximately 8 million items
It is not executed on the second or subsequent startups.
*1. SQL to change to NVARCHAR type
========================================================
CREATE TABLE imjob_monitor_bk (
tenant_id VARCHAR(100) NOT NULL,
id VARCHAR(200) NOT NULL,
jobnet_id VARCHAR(200) NOT NULL,
trigger_id VARCHAR(200) NOT NULL,
status VARCHAR(24) NOT NULL,
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd VARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd VARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, id)
);
INSERT INTO imjob_monitor_bk SELECT * FROM imjob_monitor;
DROP TABLE imjob_monitor;
CREATE TABLE imjob_monitor (
tenant_id NVARCHAR(100) NOT NULL,
id NVARCHAR(200) NOT NULL,
jobnet_id NVARCHAR(200) NOT NULL,
trigger_id NVARCHAR(200) NOT NULL,
status NVARCHAR(24) NOT NULL,
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd NVARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd NVARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, id)
);
INSERT INTO imjob_monitor SELECT * FROM imjob_monitor_bk;
DROP TABLE imjob_monitor_bk;
CREATE TABLE imjob_monitor_task_bk (
tenant_id VARCHAR(100) NOT NULL,
monitor_id VARCHAR(200) NOT NULL,
task_id VARCHAR(200) NOT NULL,
job_id VARCHAR(200) NOT NULL,
status VARCHAR(24),
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd VARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd VARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, monitor_id, task_id)
);
INSERT INTO imjob_monitor_task_bk SELECT * FROM imjob_monitor_task;
DROP TABLE imjob_monitor_task;
CREATE TABLE imjob_monitor_task (
tenant_id NVARCHAR(100) NOT NULL,
monitor_id NVARCHAR(200) NOT NULL,
task_id NVARCHAR(50) NOT NULL,
job_id NVARCHAR(200) NOT NULL,
status NVARCHAR(24),
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd NVARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd NVARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, monitor_id, task_id)
);
INSERT INTO imjob_monitor_task SELECT * FROM imjob_monitor_task_bk;
DROP TABLE imjob_monitor_task_bk;
========================================================
[Application (installation) method]
1. Start IM-Juggling.
2. Open the existing project (juggling.im) file.
3. Select the "User Module" tab and click the "+" icon in the upper right corner.
4. Add the downloaded im_job_scheduler_8_0_7_later_specific_patch-1.0.0.imm file.
https://document.intra-mart.jp/library/iap/public/setup/iap_setup_guide/texts/create_war/user_module.html
5. Output the WAR file (*1) and static file (*2).
6. Undeploy.
https://document.intra-mart.jp/library/iap/public/setup/iap_setup_guide/texts/uninstallation/index.html#war
7. After undeploying, deploy the files *1 and *2.
https://document.intra-mart.jp/library/iap/public/setup/iap_setup_guide/texts/deploy/index.html
[How to uninstall]
1. Open the IM-Juggling project and delete the user module added in this FAQ.
You can delete the user module by clicking the "X" icon in the upper right corner after selecting it.
2. Output the WAR file (*3) and static file (*4).
3. Undeploy.
https://document.intra-mart.jp/library/iap/public/setup/iap_setup_guide/texts/uninstallation/index.html#war
4. Execute the following SQL in the system database.
*If the purpose is only to uninstall the attached module, it is not necessary to execute SQL, but if the purpose is to update the product, execution of SQL is required.
================================================== ======
CREATE TABLE imjob_monitor_bk (
tenant_id VARCHAR(100) NOT NULL,
id VARCHAR(200) NOT NULL,
jobnet_id VARCHAR(200) NOT NULL,
trigger_id VARCHAR(200) NOT NULL,
status VARCHAR(24) NOT NULL,
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd VARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd VARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, id)
);
INSERT INTO imjob_monitor_bk SELECT * FROM imjob_monitor;
DROP TABLE imjob_monitor;
CREATE TABLE imjob_monitor (
tenant_id VARCHAR(100) NOT NULL,
id VARCHAR(200) NOT NULL,
jobnet_id VARCHAR(200) NOT NULL,
trigger_id VARCHAR(200) NOT NULL,
status VARCHAR(24) NOT NULL,
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd VARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd VARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, id)
);
INSERT INTO imjob_monitor SELECT * FROM imjob_monitor_bk;
DROP TABLE imjob_monitor_bk;
CREATE TABLE imjob_monitor_task_bk (
tenant_id VARCHAR(100) NOT NULL,
monitor_id VARCHAR(200) NOT NULL,
task_id VARCHAR(200) NOT NULL,
job_id VARCHAR(200) NOT NULL,
status VARCHAR(24),
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd VARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd VARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, monitor_id, task_id)
);
INSERT INTO imjob_monitor_task_bk SELECT * FROM imjob_monitor_task;
DROP TABLE imjob_monitor_task;
CREATE TABLE imjob_monitor_task (
tenant_id VARCHAR(100) NOT NULL,
monitor_id VARCHAR(200) NOT NULL,
task_id VARCHAR(200) NOT NULL,
job_id VARCHAR(200) NOT NULL,
status VARCHAR(24),
start_date_time DECIMAL(13,0) NOT NULL,
end_date_time DECIMAL(13,0),
message NVARCHAR(1000),
create_user_cd VARCHAR(100) NOT NULL,
create_date DATETIME2 NOT NULL,
record_user_cd VARCHAR(100) NOT NULL,
record_date DATETIME2 NOT NULL,
PRIMARY KEY (tenant_id, monitor_id, task_id)
);
INSERT INTO imjob_monitor_task SELECT * FROM imjob_monitor_task_bk;
DROP TABLE imjob_monitor_task_bk;
========================================================
5. After undeploying, deploy * 3 and * 4 files.
https://document.intra-mart.jp/library/iap/public/setup/iap_setup_guide/texts/deploy/index.html
--Target-------------------------------------------------------------------------
iAP/Accel Platform/2023 Autumn
iAP/Accel Platform/2023 Spring
iAP/Accel Platform/2022 Winter
iAP/Accel Platform/2022 Spring
iAP/Accel Platform/2021 Winter
iAP/Accel Platform/2021 Summer
iAP/Accel Platform/2021 Spring
iAP/Accel Platform/2020 Winter
iAP/Accel Platform/2020 Summer
iAP/Accel Platform/2020 Spring
iAP/Accel Platform/2019 Winter
iAP/Accel Platform/2019 Summer
iAP/Accel Platform/2019 Spring
iAP/Accel Platform/2018 Winter
iAP/Accel Platform/2018 Summer
iAP/Accel Platform/2018 Spring
iAP/Accel Platform/2017 Winter
iAP/Accel Platform/2017 Summer
iAP/Accel Platform/2017 Spring
iAP/Accel Platform/2016 Winter
iAP/Accel Platform/2016 Summer
iAP/Accel Platform/2016 Spring
iAP/Accel Platform/2015 Winter
iAP/Accel Platform/2015 Summer
iAP/Accel Platform/2015 Spring
iAP/Accel Platform/2014 Winter
iAP/Accel Platform/2014 Summer
iAP/Accel Platform/2014 Spring
--------------------------------------------------------------------------------
FAQID:1300
attachment