Hello,
The easiest way to figure out where exactly a process might be hanging is
to call jstack on the JVM's process ID:
$ jstack 1234
You can then tell if the process is busy doing jOOQ stuff or if it is
blocking on I/O in the JDBC driver. I suspect it will be the latter. Such a
large batched update will need to acquire quite a few locks consecutively
in a single transaction. This doesn't necessarily produce deadlocks if
there is no other big update on the same table, only small ones.
I'm not that experienced with InnoDB's locking behaviour, but I could
imagine that creating a temp table with all your (id, col1) values and then
merging that temp table in one go into T might be better:
-- jOOQ's DDL support currently doesn't include TEMP tables yet
CREATE TEMPORARY TABLE temp_t (id int, col1 int);
... batch insert your data into temp_t
create.update(t.join(temp_t).on(t.id.eq(temp_t.id)))
.set(t.col1, temp_t.col1)
.execute();
create.dropTable(temp_t).execute();
More info on MySQL's UPDATE with JOIN syntax can be found here:
- http://dev.mysql.com/doc/refman/5.7/en/update.html
-
http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query
Hope this helps,
Lukas
2014-12-14 9:14 GMT+01:00 <[email protected]>:
>
> Hi there,
>
> Thanks in advance for helping with this.
>
> I'm executing a batch update that looks something like this:
>
> Collection<? extends Query> queries = data
> .parallelStream()
> .map(datum -> create
> .update(t)
> .set(t.col1, datum.col1)
> .where(t.id.eq(datum.id)))
> .collect(toList()); // list size is ~4,000
>
> System.out.println("Updating " + queries.size() + " columns");
> create.batch(queries).execute();
> System.out.println("done");
>
> This seems to hang indefinitely. The "Updating" line prints, but "done"
> never does. It doesn't hang every time -- it hangs about 20 minutes into
> running a program that calls this method frequently. When it doesn't hang,
> the batch completes in approximately a second.
>
> I'm using MySQL with "rewriteBatchedStatements=true". No deadlock is
> reported by "SHOW ENGINE INNODB STATUS;". I don't really have anything to
> go on at the moment. The process is quite complex, and a lot of components
> interact, so I can't be sure the problem is even jOOQ -- it might be that
> the thread is getting starved in some other way, but my profiling thus far
> is inconclusive. I figured I'd post this just in case anyone else has
> encountered something like this.
>
> Cheers,
> Tim
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
