PostgreSQL数据库查询表是否被锁，以及解锁表的办法

问题现象：

（1）、CDM任务执行时报错：org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

2025-09-29 10:22:19.663|INFO |cdm-job-submit-pool24|[o.a.s.c.jdbc.GenericJdbcExecutor:596]|Destructive Action Warning: truncate all data: TRUNCATE TABLE "turbo_dev_01"."t_cm_customer_ship_detail"
2025-09-29 10:27:19.763|ERROR|cdm-job-submit-pool24|[org.apache.sqoop.utils.JdbcSandbox:62]|occur exception, actual is :
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:399)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:290)
    at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.lambda$executeUpdate$1(WrapStatement.java:33)
    at java.security.AccessController.doPrivileged(Native Method)
    at org.apache.sqoop.utils.JdbcSandbox.doPrivileged(JdbcSandbox.java:53)
    at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.executeUpdate(WrapStatement.java:33)
    at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeUpdate(GenericJdbcExecutor.java:741)
    at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.truncateTable(GenericJdbcExecutor.java:597)
    at sun.reflect.GeneratedMethodAccessor231.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.sqoop.connector.jdbc.writer.JdbcExecutorProxy$ExecutorInvocationHandler.invoke(JdbcExecutorProxy.java:95)
    at com.sun.proxy.$Proxy36.truncateTable(Unknown Source)
    at org.apache.sqoop.connector.jdbc.GenericJdbcToInitializer.clearBeforeLoading(GenericJdbcToInitializer.java:842)
    at org.apache.sqoop.connector.jdbc.GenericJdbcToInitializer.prepareJob(GenericJdbcToInitializer.java:641)
    at org.apache.sqoop.connector.jdbc.GenericJdbcToInitializer.prepareJob(GenericJdbcToInitializer.java:86)
    at org.apache.sqoop.driver.job.JobInitiator.prepareJob(JobInitiator.java:852)
    at org.apache.sqoop.driver.job.JobSubmissionContext.submitInternal(JobSubmissionContext.java:238)
    at org.apache.sqoop.driver.job.JobSubmissionContext.submit(JobSubmissionContext.java:183)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:750)
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
    at java.net.SocketInputStream.read(SocketInputStream.java:171)
    at java.net.SocketInputStream.read(SocketInputStream.java:141)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:192)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:159)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:144)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:76)
    at org.postgresql.core.PGStream.receiveChar(PGStream.java:476)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2174)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
    ... 28 common frames omitted

（2）重试后，发现执行也会报错

2025-09-29 10:15:14.175|INFO |cdm-job-submit-pool7|[o.a.s.c.jdbc.GenericJdbcExecutor:1446]|execute check sql: SELECT count(1) FROM "turbo_dev_01"."t_cm_customer_ship_detail" WHERE 1 = 2 .
2025-09-29 10:20:14.275|ERROR|cdm-job-submit-pool7|[org.apache.sqoop.utils.JdbcSandbox:62]|occur exception, actual is :
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:399)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:312)
    at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.lambda$execute$3(WrapStatement.java:50)
    at java.security.AccessController.doPrivileged(Native Method)
    at org.apache.sqoop.utils.JdbcSandbox.doPrivileged(JdbcSandbox.java:53)
    at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.execute(WrapStatement.java:50)
    at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.existTable(GenericJdbcExecutor.java:1448)
    at sun.reflect.GeneratedMethodAccessor193.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.sqoop.connector.jdbc.writer.JdbcExecutorProxy$ExecutorInvocationHandler.invoke(JdbcExecutorProxy.java:95)
    at com.sun.proxy.$Proxy36.existTable(Unknown Source)
    at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.configureTableProperties(GenericJdbcFromInitializer.java:750)
    at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.initialize(GenericJdbcFromInitializer.java:206)
    at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.initialize(GenericJdbcFromInitializer.java:111)
    at org.apache.sqoop.driver.job.JobInitiator.initializeConnector(JobInitiator.java:787)
    at org.apache.sqoop.driver.job.JobInitiator.createJobRequest(JobInitiator.java:371)
    at org.apache.sqoop.driver.job.JobSubmissionContext.submitInternal(JobSubmissionContext.java:235)
    at org.apache.sqoop.driver.job.JobSubmissionContext.submit(JobSubmissionContext.java:183)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:750)
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
    at java.net.SocketInputStream.read(SocketInputStream.java:171)
    at java.net.SocketInputStream.read(SocketInputStream.java:141)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:192)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:159)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:144)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:76)
    at org.postgresql.core.PGStream.receiveChar(PGStream.java:476)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2174)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
    ... 28 common frames omitted

 

问题原因：

  由于重试多次，发现还是报相同的错误。怀疑是表被锁导致。

解决办法：

（1）、检查表是否被锁

SELECT
    l.locktype,
    l.MODE,
    l.GRANTED,
    A.pid,       --解除锁表使用
    A.usename,
    A.query,
    A.STATE,
    A.application_name 
FROM
    pg_locks l
    JOIN pg_stat_activity A ON l.pid = A.pid
    JOIN pg_class C ON l.relation = C.oid 
WHERE
    C.relname = 't_cm_customer_ship_detail' -- 将 your_table_name 替换为你的表名
    AND l.relation IS NOT NULL;

 

（2）解锁表，根据pid解锁表

SELECT pg_terminate_backend(139637009676032);   --查询pid解锁表，这里id需要替换成上面查询出的pid