I had some time to check this.
Unfortunately the fix does not solve the problem. It does indeed perform a lock correctly. But after the lock is released, the result set of the db_migration table is the old one and the migration will be executed again.
For example:
1- Node A performs a select for update
2- Node B performs a select for update (it will be waiting)
3- Node A executes the migration and writes an entry to db_migration
4- Node A commits and releases the lock
5- Node B gets the result set from db_migration (and it does not contain the entry inserted by Node A)
6- Node B executes the migration and writes an entry to db_migration (it fails with: ERROR: duplicate key value violates unique constraint "pk_db_migration")
Not sure if the solution is to use a different isolation level, like serializable, or to perform a select again.
Best regards,
Pedro
postgres 11.1
Lucky you - nice.
So yes we have a problem here with Node 2. Postgres and Oracle will execute with statement level read consistency hence at 5- Node B gets the result s ... doesn't see the row.
like serializable, or to perform a select again.
Yes and another option would be at 2 ... to use for update nowait, detect that Node B has lost the race and then waits for Node A to release the lock. The other 2 options sound better / simpler than this though.
Right now I'm leaning towards the second select option. We know the second select executes after we have successfully obtained the lock (and the second select will see the commits of any other node that occurred while the first select was waiting for the lock).
Pedro Gonzalez to Ebean
Hi again Rob,
I had some time to check this. Unfortunately the fix does not solve the problem. It does indeed perform a lock correctly. But after the lock is released, the result set of the db_migration table is the old one and the migration will be executed again. For example: 1- Node A performs a select for update 2- Node B performs a select for update (it will be waiting) 3- Node A executes the migration and writes an entry to db_migration 4- Node A commits and releases the lock 5- Node B gets the result set from db_migration (and it does not contain the entry inserted by Node A) 6- Node B executes the migration and writes an entry to db_migration (it fails with: ERROR: duplicate key value violates unique constraint "pk_db_migration")
I think that in step 5 the result set should contain the row inserted from Node A Here is were I think this happens: https://github.com/ebean-orm/ebean-migration/blob/3c3800d3da716590d5958932aca5875909758a31/src/main/java/io/ebean/migration/runner/MigrationTable.java#L144
Not sure if the solution is to use a different isolation level, like serializable, or to perform a select again.
Best regards, Pedro
Lucky you - nice.
So yes we have a problem here with Node 2. Postgres and Oracle will execute with statement level read consistency hence at 5- Node B gets the result s ... doesn't see the row.
Yes and another option would be at 2 ... to use for update nowait, detect that Node B has lost the race and then waits for Node A to release the lock. The other 2 options sound better / simpler than this though.
Right now I'm leaning towards the second select option. We know the second select executes after we have successfully obtained the lock (and the second select will see the commits of any other node that occurred while the first select was waiting for the lock).
Thanks for raising this.