官网文档:
https://wiki.pentaho.com/display/EAI/Configuring+log+tables+for+concurrent+access?showComments=false
本文档由官网文档整理得出,请参阅官网文档,但是国内访问pentaho网站不太稳定,特此整理
环境:
kettle 8.3和Pentaho-Server 8.3
背景:
使用kettle 的服务器端Pentaho-Server ,做定时任务时,发现假如多个任务同时并发,并启用了kettle自带的日志时,会因为日志表的行锁造成任务执行失败。
问题特征:
前端发现数据异常,后台检查Pentaho-Server后台日志,发现如下报错:
2021-04-01 00:10:03.729 ERROR <Thread-134959> [/home/LGETL/DMS/job/JOB_DMS_MAC_TMP_NEW.kjb] Unable to write log record to log table [log_job1]
2021-04-01 00:10:03.729 ERROR <Thread-134959> [/home/LGETL/DMS/job/JOB_DMS_MAC_TMP_NEW.kjb] Unable to write log record to log table [log_job1]
2021-04-01 00:10:03.730 ERROR <Thread-134959> [/home/LGETL/DMS/job/JOB_DMS_MAC_TMP_NEW.kjb] org.pentaho.di.core.exception.KettleDatabaseException:
Error inserting/updating row
(conn=7837) Deadlock found when trying to get lock; try restarting transaction
at org.pentaho.di.core.database.Database.insertRow(Database.java:1324)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1248)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1236)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1224)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1187)
at org.pentaho.di.core.database.Database.writeLogRecord(Database.java:3598)
at org.pentaho.di.job.Job.beginProcessing(Job.java:1009)
at org.pentaho.di.job.Job.execute(Job.java:472)
at org.pentaho.di.job.Job.run(Job.java:384)
Caused by: java.sql.SQLTransactionRollbackException: (conn=7837) Deadlock found when trying to get lock; try restarting transaction
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:179)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeUpdate(MariaDbPreparedStatementClient.java:183)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1291)
... 8 more
Caused by: java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction
Query is: INSERT INTO log_job1 (ID_JOB, CHANNEL_ID, JOBNAME, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDATE, LOGDATE,
DEPDATE, REPLAYDATE, LOG_FIELD) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters [4394,'e3fd0d67-5aca-44f0-a6e0-7714bd889213','JOB_DMS_MAC_TMP_NEW','sta
rt',<null>,<null>,<null>,<null>,<null>,<null>,<null>,'2021-03-31 00:10:02.0','2021-04-01 00:10:03.682','2021-04-01 00:10:03.682','2021-04-01 00:10:03.682','2021-04-01 00:10:03.682','2021/03/3
1 20:20:00 - RepositoriesMeta - Reading repositories XML file: FromInputStream
问题原因:
kettle设计时,未妥善处理并发任务,插入BATCH_ID值时,只是简单的select max(ID_JOB)+1,当同一时间内多个任务执行时,获取的新一个BATCH_ID是重复的,这样就造成了行锁。
解决方案:
由于kettle并未指定后台日志表的种类,针对不同的数据库,使用额外的BATCH ID生成表,或者序列来解决
方案1:使用额外的BATCH ID生成表
该方案适合无sequence类型的数据库,我们使用的MariaDB,同理MySQL也适用该方案
在日志表的库中创建ID生成表LOG_COUNTER
create table LOG_COUNTER(ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100)) engine=InnoDB;
插入一行数据,注意有且仅有一行
insert into LOG_COUNTER(ID,NAME) values(100000,'LOG_TABLES');
commit;
--注意:这里的100000,是因为我之前的任务表中已经有25000行数据了,我打算让新的日志BATCH_ID从10万开始计数,总之一定要确保让这里的ID大于之前的日志表中最大BATCH_ID
然后修改kettle中日志表的数据库连接选项:
在选项中添加参数
参数名:AUTOINCREMENT_SQL_FOR_BATCH_ID
参数值:UPDATE LOG_COUNTER SET ID=LAST_INSERT_ID(ID+1)
原文中说:
This will explain to PDI to use a value from the LOG_COUNTER table every time a new batch ID needs to be generated for a transformation or a job table.
这将向 PDI 解释每次需要为转换或作业表生成新batch ID 时使用 LOG_COUNTER 表中的值
然后尝试在本地和SERVER端执行任务,可以发现每执行一次后,LOG_COUNTER中的ID值也会随之更新,同时日志表中的新的最大ID变成了LOG_COUNTER表中的当前ID值
方案2:使用序列Sequence
--未测试,直接放上原文,请结合源文档自己测试:适用于ORACLE等支持Sequence类型的日志表
You can use a database sequence if your database supports this. To enable support for this, create a sequence like this (Oracle example):
CREATE SEQUENCE LOGGINGSEQ START WITH 10000;
In the logging database connection in Pentaho Data Integration (Spoon), add the following line in the Options panel:
Parameter: SEQUENCE_FOR_BATCH_ID
Value: LOGGINGSEQ
This will explain to PDI to use a value from the LOGGINGSEQ sequence every time a new batch ID needs to be generated for a transformation or a job table.