transaction will be broken when using select

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

transaction will be broken when using select

Plexus Cranialis
Hello,

when i using a "select" block ("insert"/"update" etc are fine) with callableStatement, the transaction will automatically commitet. (I have to use "select" because the procedure genereates one)

Example:
<select id="updateZ" resultType="com.my.data.ZClass" statementType="CALLABLE">
{
            call test.updateZ
(
@P1 = null,
@P2 = #{testP1}
)
    }
</select>

JavaCode:

try (SqlSession session = MapperEngine.openSession("envXY"))
{
  session.getMapper(Mapper.class).updateZ("test1"); //will be saved no mater what
  if(true) throw new Exception("error happend");
  session.getMapper(Mapper.class).updateZ("test2");  
  session.commit();
}

Is this a Bug?
Is there a workarround?

Best Regards, Hannes

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/ff0139c8-2cb1-4b81-b2f9-120368410055n%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: transaction will be broken when using select

Guy Rouillier-2
Do you have autocommit set on your connection?

--
Guy Rouillier

On 9/8/2020 12:22:49 PM, "Plexus Cranialis" <[hidden email]> wrote:

Hello,

when i using a "select" block ("insert"/"update" etc are fine) with callableStatement, the transaction will automatically commitet. (I have to use "select" because the procedure genereates one)

Example:
<select id="updateZ" resultType="com.my.data.ZClass" statementType="CALLABLE">
{
            call test.updateZ
(
@P1 = null,
@P2 = #{testP1}
)
    }
</select>

JavaCode:

try (SqlSession session = MapperEngine.openSession("envXY"))
{
  session.getMapper(Mapper.class).updateZ("test1"); //will be saved no mater what
  if(true) throw new Exception("error happend");
  session.getMapper(Mapper.class).updateZ("test2");  
  session.commit();
}

Is this a Bug?
Is there a workarround?

Best Regards, Hannes

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/ff0139c8-2cb1-4b81-b2f9-120368410055n%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/em8556b784-6c73-4449-9c13-610ccb415194%40asus.
Reply | Threaded
Open this post in threaded view
|

Re: transaction will be broken when using select

Plexus Cranialis
No, autocommit is off. I also tried in Java one Line above manually: getConnection().setAutoconnect(false). With no effect. As i said when in xml using <update... unstead <select... the statement is correctly rolling back. But i need <select for my procedure-call because of the result set. I use ms sql server.

Guy Rouillier <[hidden email]> schrieb am Mi., 9. Sep. 2020, 06:23:
Do you have autocommit set on your connection?

--
Guy Rouillier

On 9/8/2020 12:22:49 PM, "Plexus Cranialis" <[hidden email]> wrote:

Hello,

when i using a "select" block ("insert"/"update" etc are fine) with callableStatement, the transaction will automatically commitet. (I have to use "select" because the procedure genereates one)

Example:
<select id="updateZ" resultType="com.my.data.ZClass" statementType="CALLABLE">
{
            call test.updateZ
(
@P1 = null,
@P2 = #{testP1}
)
    }
</select>

JavaCode:

try (SqlSession session = MapperEngine.openSession("envXY"))
{
  session.getMapper(Mapper.class).updateZ("test1"); //will be saved no mater what
  if(true) throw new Exception("error happend");
  session.getMapper(Mapper.class).updateZ("test2");  
  session.commit();
}

Is this a Bug?
Is there a workarround?

Best Regards, Hannes

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/ff0139c8-2cb1-4b81-b2f9-120368410055n%40googlegroups.com.

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/NPE_BpvC2yk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/em8556b784-6c73-4449-9c13-610ccb415194%40asus.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAM4L2hmfYbSkhh-S%2BZ-1TJPagUHu54zDEFzFGm%3D2qk%3DaKNzztw%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: transaction will be broken when using select

Iwao AVE!
In reply to this post by Plexus Cranialis
Hello Hannes,

When only `<select />` is executed in a transaction, the transaction will be committed automatically.
If you want to rollback the transaction, you may need to call `session.rollback(true)` explicitly.

Note that, in case the transaction is managed by Spring, only an unchecked exception (i.e. `RuntimeException`) triggers the rollback by default.

Regards,
Iwao

On Wed, Sep 9, 2020 at 1:22 AM Plexus Cranialis <[hidden email]> wrote:
Hello,

when i using a "select" block ("insert"/"update" etc are fine) with callableStatement, the transaction will automatically commitet. (I have to use "select" because the procedure genereates one)

Example:
<select id="updateZ" resultType="com.my.data.ZClass" statementType="CALLABLE">
{
            call test.updateZ
(
@P1 = null,
@P2 = #{testP1}
)
    }
</select>

JavaCode:

try (SqlSession session = MapperEngine.openSession("envXY"))
{
  session.getMapper(Mapper.class).updateZ("test1"); //will be saved no mater what
  if(true) throw new Exception("error happend");
  session.getMapper(Mapper.class).updateZ("test2");  
  session.commit();
}

Is this a Bug?
Is there a workarround?

Best Regards, Hannes

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/ff0139c8-2cb1-4b81-b2f9-120368410055n%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2T%2Bj%3DVzH7bSPNJgcFm6Mdv9KRJx%3D-he43-N2Xb%2BR5dxhg%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: transaction will be broken when using select

Plexus Cranialis
Thanks for your reply. Then i change our paradigm to:

try (SqlSession session = MapperEngine.openSession("envXY"))
{
  session.getMapper(Mapper.class).updateZ("test1"); //will be saved no mater what
  if(true) throw new Exception("error happend");
  session.getMapper(Mapper.class).updateZ("test2");  
  session.commit();
}
catch(Exception ex)
{
   session.rollback(true);
}

I try it out and reply again, about what the results are. If it is so easy i, make a Wrapper for SqlSession which makes the rollback automatically when not committed in close() (like some would expect SqlSession works).


Am Do., 10. Sept. 2020 um 12:09 Uhr schrieb Iwao AVE! <[hidden email]>:
Hello Hannes,

When only `<select />` is executed in a transaction, the transaction will be committed automatically.
If you want to rollback the transaction, you may need to call `session.rollback(true)` explicitly.

Note that, in case the transaction is managed by Spring, only an unchecked exception (i.e. `RuntimeException`) triggers the rollback by default.

Regards,
Iwao

On Wed, Sep 9, 2020 at 1:22 AM Plexus Cranialis <[hidden email]> wrote:
Hello,

when i using a "select" block ("insert"/"update" etc are fine) with callableStatement, the transaction will automatically commitet. (I have to use "select" because the procedure genereates one)

Example:
<select id="updateZ" resultType="com.my.data.ZClass" statementType="CALLABLE">
{
            call test.updateZ
(
@P1 = null,
@P2 = #{testP1}
)
    }
</select>

JavaCode:

try (SqlSession session = MapperEngine.openSession("envXY"))
{
  session.getMapper(Mapper.class).updateZ("test1"); //will be saved no mater what
  if(true) throw new Exception("error happend");
  session.getMapper(Mapper.class).updateZ("test2");  
  session.commit();
}

Is this a Bug?
Is there a workarround?

Best Regards, Hannes

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/ff0139c8-2cb1-4b81-b2f9-120368410055n%40googlegroups.com.

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/NPE_BpvC2yk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2T%2Bj%3DVzH7bSPNJgcFm6Mdv9KRJx%3D-he43-N2Xb%2BR5dxhg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAM4L2hkzvLSLLswrr1jqoK5B_JKCs7HZ5rHJND102VAam4c36w%40mail.gmail.com.