Thanks, I'll consider the sub query solution.
// Nicklas
It's actual safe enough for my user case using the MySQL InnoDB engine, and
default transaction isolation level.
CREATE TABLE report
S1
begin;
S2
begin;
S1
select coalesce(max(r.sequence) + 1, 1) from reports r where
BTW, you can't lock rows that don't exist, so if there were no rows with
the specific report_id your select will be unsafe (unless you use some
additional locking in this corner case).
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To
It's not related in any way with compatibility modes.
Perhaps you can lock the rows in a subquery.
select coalesce(max(sequence) + 1, 1) from (select sequence from r
where r.report_id = ? for update)
--
You received this message because you are subscribed to the Google Groups "H2
Database"
Alright, that is unfortunately. We will have to stick to *1.4.197*.
And it's not possible to override the behaviour using compability modes?
For example *MODE=MYSQL*
Yes, we need to lock all entries containing the `report_id` inorder to
ensure the number serie.
Example table
report_id,
Hello.
SELECT_FOR_UPDATE_MVCC was useless after other changes in H2.
The exception that you got describes the situation pretty clear: you can't
use FOR UPDATE clause when you use aggregates (or distinct). Grouped
queries don't return the original rows, FOR UPDATE is disallowed in such
queries
