Cause: org.postgresql.util.PSQLException: ERROR: deadlock detected 检测到死锁异常排查处理

报错内容如下：

org.springframework.dao.DeadlockLoserDataAccessException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: deadlock detected
  详细：Process 7372 waits for ShareLock on transaction 46061491; blocked by process 4624.
Process 4624 waits for ShareLock on transaction 46061490; blocked by process 7372.
  建议：See server log for query details.
  在位置：while locking tuple (0,1) in relation "tas_book_type"

sql语句如下：

SELECT p.book_code, p.book_type_code, p.ind_blk_u, d.tree_data_code, d.data_name
    , d.data_type, d.berth_type, d.linked_code, d.city_code, d.stg_sec_code
    , d.tree_code, ps.ef_status, ps.execute_status, ps.interrupt, ds.lock_source
FROM tas_book p
    LEFT JOIN tas_book_type pt ON p.book_type_code = pt.book_type_code
    LEFT JOIN tas_book_status ps ON p.book_code = ps.book_code
    LEFT JOIN tas_tree_data_status ds ON ps.pen_code = ds.tree_data_code
    LEFT JOIN tas_tree_data d ON d.tree_data_code = ds.tree_data_code
WHERE p.ind_blk_u = 0
    AND pt.is_fixed = '0'
    AND pen_code != ''
    AND pen_code IS NOT NULL
    AND d.city_code = '13'
    AND ps.ef_status = '1'
    AND ps.execute_status = '1'
ORDER BY random()
LIMIT 1
FOR UPDATE

经分析发现tas_book_type与tas_book是一对多的关系，且tas_book_type内的数据基本不用修改，但上面的sql语句把它用来关联加锁查询，这样每次查到一条数据就会对这张表进行加锁，导致很多事务都要对其加锁，严重影响查询效率，还会造成死锁问题。经过研究最终sql优化如下：

SELECT p.book_code, p.book_type_code, p.ind_blk_u, d.tree_data_code, d.data_name
    , d.data_type, d.berth_type, d.linked_code, d.city_code, d.stg_sec_code
    , d.tree_code, ps.ef_status, ps.execute_status, ps.interrupt, ds.lock_source
FROM tas_book p
    LEFT JOIN tas_book_status ps ON p.book_code = ps.book_code
    LEFT JOIN tas_tree_data_status ds ON ps.pen_code = ds.tree_data_code
    LEFT JOIN tas_tree_data d ON d.tree_data_code = ds.tree_data_code
WHERE p.ind_blk_u = 0
    AND p.book_type_code= 'student'
    AND pen_code != ''
    AND pen_code IS NOT NULL
    AND d.city_code = '13'
    AND ps.ef_status = '1'
    AND ps.execute_status = '1'
ORDER BY random()
LIMIT 1
FOR UPDATE skip locked

通过测试发现极大的提升了查询效率，并且不会出现死锁情况。