JOOQ and autoCommit on false

[FB]

Hi,

I read on the documentation that JOOQ doesn't provide transaction
management and I have to take care of that.
So to start a transaction and commit/rollback the data I was thinking
to do:


datasourse.setAutoCommit(false) //this set
connection.setAutoCommit(false)

factory.insertInto(T, T.FIRST_NAME, T.LAST_NAME)
.values("laura", "natali")
.execute();

datasourse.commit();


The problem is the factory inserts the data before the .commit()
command.
Is there another way to do that?

Thanks. F.

[Lukas Eder]

Your insert is indeed executed on the database, but only your transaction can see the change. Check things in a different transaction to see if you're getting it right. I.e.

1. Set auto-commit to false
2. Insert
3. Create a break point before the commit
4. Use squirrel or phpmyadmin or your favorite database tool, to check whether the table was updated. It shouldn't be
5. Commit
6. Check again in your tool. Now, the update should be visible

Note, there might be some confusion with Hibernate, which has the notion of "flushing". Hibernate has a second-level cache. When you write data, it is possible that the writes are kept in memory first, before they're "flushed" to the database. jOOQ has no second-level cache, but executes everything directly on your database connection.

Cheers
Lukas

2012/4/1 FB <fabio...@gmail.com>

[fabio...@gmail.com]

Dear Lukas,

first of all, thank you for your support.

Yes, the steps that you wrote are exactly what I did before posting :)

1. Set auto-commit to false

2. Insert

3. Create a break point before the commit  (I didn't do that, this is the only difference)

4. Use squirrel or phpmyadmin or your favorite database tool, to check whether the table was updated. It shouldn't be (I'm using MYSQL query builder)

5. Commit

6. Check again in your tool. Now, the update should be visible 

The factory .insertInto .execute updates the data before the commit.

To be sure about the connection on autoCommit=false, I also did:

factory.getConnection().setAutoCommit(false)

 factory.insertInto(....)

.execute() 

Can be a missing configuration? Or can it depend by MySql driver (I'm using version x.x.15 instead x.x.18 as in your tutorial)?

Many thanks.

Fabio 

[Lukas Eder]

Hello Fabio,

> Yes, the steps that you wrote are exactly what I did before posting :)

Great

> Can be a missing configuration? Or can it depend by MySql driver (I'm using
> version x.x.15 instead x.x.18 as in your tutorial)?

I don't think the driver's patch version is relevant, although you
should use the latest version, of course. However, have you considered
these MySQL features?
http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

MySQL allows for tampering with transaction isolation levels. So for
debugging purposes, it is actually possible to read uncommitted state
from another transaction...

Cheers
Lukas

[fabio...@gmail.com]

You are right! I forgot that...

I'm going to check but probably will work fine.

Thank you again.

[FractalizeR]

Please also note, that certain database engines (MyISAM, for instance, which was the default engine for a long time) does not support transactions and ignore all transaction control statements. If you want to use transactions, please ensure all tables, involved into the transaction, are using InnoDB engine, for example.

You can change engine with

ALTER TABLE t1 ENGINE = InnoDB;

понедельник, 2 апреля 2012 г. 13:26:40 UTC+4 пользователь fabio...@gmail.com написал:

[fabio...@gmail.com]

Everything works fine.

Thank you guys for your quick good reply! :)

[FractalizeR]

What was the problem? :)

вторник, 3 апреля 2012 г. 19:35:17 UTC+4 пользователь fabio...@gmail.com написал:

[Lukas Eder]

I was going to ask the same question! :)
(thanks for your help, btw, Vladislav)

2012/4/4 FractalizeR <Fract...@yandex.ru>: