postgresql - 两个并发请求能够锁定 Postgres sql 中的同一行

Question

当对以下代码发出两个并发请求时，两个请求都能够同时获取锁，因此能够执行代码块

在生产中运行的示例代码：

示例代码供参考：

//Starting point for the request
@Override
    public void receiveTransferItems(String argumet1, String refernceId, List<Item> items, long messageId)
            throws Exception {
        ParentDTO parent = DAO.lockByReferenceid(referenceId);
        if (parent == null) {
            throw new Exception(referenceId + "does not exist");
        }

        updateData(parent);

        for (Item item : items) {
            receiveItem(td, td.getWarehouseId(), item.getItemSKU(), item.getItemStatus(), item.getQtyReceived(), messageId);
        }

    }
    
    
    private void updateData(ParentDTO td) throws DropShipException {

        //perform some logical processing and then execute update 
        DAO.update(td);
    }
    
    
    
     private void receiveItem(ParentDTO td, String warehouseId, String asin, String itemStatus, int quantity, long messageId)
            throws Exception {
    
        /**
         * perform some logical processing
         * 
         **/
        //call is being made to another class to do the rest of the processing
        service.receive(td, asin, quantity, condition, container, messageId);
    }

@Override
    public void receive(
            ParentDTO parentDTO,
            String asin,
            int quantity,
            Condition condition,
            Container container,
            long messageId,
            DataAccessor accessor) throws Exception {



        List<ChildDTO> childDTOs =
            DAO.lockChildDTOItems(parentDTO.getReferenceId(), asin, condition,
                                                  CostInfoSource.MANIFEST);

        List<ChildDTO> filterItems = DAO
            .loadChildDTOItems(parentDTO.getReferenceId(), asin, condition.name());

        long totalExpectedQuantity = getTotalExpectedQuantity(filterItems);
        long totalReceivedQuantity = getTotalReceivedQuantity(filterItems);

        int quantityNormalReceived = 0;
        for (ChildDTO tdi : childDTOs) {
        
            int quantityReceived = 0;
            if (asinDropShipMsgAction != null) {
                quantity -= asinDropShipMsgAction.getInitialQuantity();
                quantityNormalReceived += asinDropShipMsgAction.getInitialQuantity();
            } else {
                quantityReceived = new DBOperationRunner<Integer>(accessor.getSessionManager()) {
                    @Override
                    protected Integer doWorkAndReturn() throws Exception {
                        return normalReceive(tdi, quantityLeft, container, MessageActionType.TS_IN, messageId);
                    }
                }.execute();
            }
          
        }

    }
    
    
    
    private int normalReceive(final ChildDTO childDTO,
                              int quantity,
                              final Container container,
                              final MessageActionType type,
                              long messageId)
        throws Exception {

        /**
         * perform some business logic
         * 
         * */
         
        DAO.update(childDTO);
        return someQuantity;
    }

lockByReferenceId 函数的实现：

@Override
    public ParentDTO lockByReferenceId(String referenceId) {
        Criteria criteria = getCurrentSession().createCriteria(ParentDTO.class)
            .add(Restrictions.eq("referenceId", referenceId)).setLockMode(LockMode.UPGRADE_NOWAIT);
        return (ParentDTO) criteria.uniqueResult();
    }

DBOperationRunner 类的实现：

public T execute() throws Exception {
        T t = null;
        Session originalSession = (Session) ThreadLocalContext.get(ThreadLocalContext.CURRENT_SESSION);
        try {
            ThreadLocalContext.put(ThreadLocalContext.CURRENT_SESSION, sessionManager.getCurrentSession());
            sessionManager.beginTransaction();
            t = doWorkAndReturn();
            sessionManager.commit();

        } catch (Exception e) {
            try {
                sessionManager.rollback();
            } catch (Throwable t1) {
                logger.error("failed to rollback", t1);
            }
            throw e;
        } finally {
            ThreadLocalContext.put(ThreadLocalContext.CURRENT_SESSION, originalSession);
        }

        return t;
    }

最近我在生产代码中观察到一个问题，其中两个或多个同时请求能够同时获取相同数据的锁定。我使用休眠和标准作为数据库框架，使用 c3p0 作为连接池框架，使用 Postgres 作为数据库。注意：此问题是间歇性的，仅在一些难以调试的随机并发请求中观察到。

我无法理解两个并发请求如何能够同时锁定相同的行。您能帮我确定在这种情况下出了什么问题吗？

提前致谢！！！！

标签： postgresqlhibernateconcurrencyc3p0