Unexpected auto-commit behaviour when performing "UPDATE... RETURNING *" on Postgres #1189
Description
MyBatis version
3.4.2
Database vendor and version
PostgreSQL 10.1 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
Test case or example project
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.GiftcardInventoryMapper">
<!-- Map database results to Java classes -->
<resultMap id="GiftcardInventoryResult" type="com.test.dao.GiftcardInventoryDAO">
<id property="id" column="id"/>
<result property="status" column="status"/>
<result property="created" column="created"/>
<result property="updated" column="modified"/>
<result property="pan" column="pan"/>
<result property="cvd" column="cvd"/>
<result property="account_id" column="account_id"/>
</resultMap>
<!-- We are doing an "UPDATE...(SELECT FOR UPDATE SKIP LOCKED) RETURNING *" which is not really a select, but we need to declare it as a select so MyBatis will return the results -->
<select id="getNextAvailableGiftcard" resultMap="GiftcardInventoryResult">
UPDATE giftcard_inventory
SET status = 'RESERVED'
WHERE id=(SELECT id FROM giftcard_inventory WHERE status='AVAILABLE' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING *
</select>
</mapper>
Steps to reproduce
I'm doing an "UPDATE... RETURNING *" call to Postgres, which doesn't really fit the normal usage pattern of MyBatis. The SQL performs an UPDATE, but it also returns data.
By defining the SQL in a <select> (see XML above), the update occurs and I get my data back out as desired. The thing that is peculiar is that the UPDATE is auto-commited by the current session/transaction. I have even tried to explicitly call SqlSessionFactory.openSession(false) to disable auto-commit, but the behaviour persists.
I notice that if I make an additional call that invokes a standard <update> in the mapper XML, then MyBatis seems to correctly implement the expected behaviour. Specifically, if I do the UPDATE as part of the open session/transaction, then all my activity (especially the earlier "UPDATE... RETURNING") is not auto-committed, and I can commit or rollback as desired.
As mentioned, this usage pattern doesn't really fit with MyBatis' expectations, and I know that performing an UPDATE in a declared <select> mapper is likely the source of the problem. Is there a more correct way to do this?
Expected result
The session/transaction should not auto-commit.
Actual result
The session/transaction auto-commits.