UpdateAllQuery and Persist executed in wrong order!

[ossaert]

Hi,

I encountered the following problem. When doing an UpdateAllQuery before a persist-operation, the INSERT is executed first, then the UPDATE instead of the other way around.

I roughly have the following code:

public void create(.....) {

        // Update index = index + 1
        if (....)
           this.moveIndex(portalGuid, userGuid, index);
       
        // Persist bookmark
        BookmarkEntity bookmark = new BookmarkEntity();
        bookmark.setGuid(bookmarkGuid);
        bookmark.setUserGuid(userGuid);
        bookmark.setPortalGuid(portalGuid);
        bookmark.setForURI(forURI);
        bookmark.setCaption(caption);
        bookmark.setIndex(index);
        this.entityManager.persist(bookmark);
        this.entityManager.flush();
        return bookmark;
    }

    // Move index one position upwards
    private void moveIndex(String portalGuid, String userGuid, int fromIndex) {

        // Create expression
        ExpressionBuilder expressionBuilder = new ExpressionBuilder();
        Expression expression =
                expressionBuilder.get(BookmarkEntity.FIELD_USER_GUID).
                equal(userGuid).
                and(expressionBuilder.get(BookmarkEntity.FIELD_PORTAL_GUID).
                equal(portalGuid)).and(
                expressionBuilder.get(BookmarkEntity.FIELD_INDEX).
                greaterThanEqual(fromIndex));


        UpdateAllQuery modifyQuery = new UpdateAllQuery(BookmarkEntity.class);
        modifyQuery.setSelectionCriteria(expression);        
        modifyQuery.addUpdate(expressionBuilder.get(BookmarkEntity.FIELD_INDEX),
                ExpressionMath.add(expressionBuilder.get(BookmarkEntity.FIELD_INDEX), 1));

        // Execute delete query
        JpaHelper.getEntityManager(entityManager).getActiveSession().
                executeQuery(modifyQuery);

        // Make sure that statement is flushed to database
        JpaHelper.getEntityManager(entityManager).flush();

    }


The following SQL is executed:

1) INSERT INTO ooi_bookmarks (bookmark_id, bookmark_guid, bookmark_for_uri, bookmark_index, bookmark_caption, portal_guid, bookmark_date_creation, user_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        bind => [null, xxx, z, 1, null, y, 2009-07-28 15:42:09.527, x]

2) UPDATE ooi_bookmarks SET bookmark_index = (bookmark_index + ?) WHERE (((user_guid = ?) AND (portal_guid = ?)) AND (bookmark_index >= ?))
        bind => [1, x, y, 1]

3) UPDATE ooi_bookmarks SET bookmark_index = (bookmark_index + ?) WHERE (((user_guid = ?) AND (portal_guid = ?)) AND (bookmark_index >= ?))
        bind => [1, x, y, 1]

So, when issuing the FLUSH, then two UPDATES are executed. When I omit the flush, then only one UPDATE is executed.

What I expect, but do not achieve, is the following order:

1) UPDATE ooi_bookmarks SET bookmark_index = (bookmark_index + ?) WHERE (((user_guid = ?) AND (portal_guid = ?)) AND (bookmark_index >= ?))
        bind => [1, x, y, 1]

2) INSERT INTO ooi_bookmarks (bookmark_id, bookmark_guid, bookmark_for_uri, bookmark_index, bookmark_caption, portal_guid, bookmark_date_creation, user_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        bind => [null, xxx, z, 1, null, y, 2009-07-28 15:42:09.527, x]

When I do the update after the persist, then everything works ok and the SQL is executed as one INSERT and one UPDATE, but of course, with the wrong logical result.

I suppose I could have something to do with the JpaHelper being executed first?

Have been moving the code around for a while now, but not coming closer to the solution. I am using 2.0.0-SNAPSHOT.

Greetings
Jan

[Andrei Ilitchev]

Could you please log a bug describing the exact scenario that leads to
UpdateAllQuery executed twice:
(is it execute updateAllQuery; then flush, then persist? or updateAllQuery,
then persist, then flush?)

Set the following flag
  modifyQuery.setShouldDeferExecutionInUOW(false);
to force modifyQuery to be executed right away (by default it waits until
commit).

Note that modifyQuery won't update the cache.

Thanks,

Andrei


----- Original Message -----
From: "ossaert" <decooman@...>
To: <eclipselink-users@...>
Sent: Tuesday, July 28, 2009 9:58 AM
Subject: [eclipselink-users] UpdateAllQuery and Persist executed in
wrongorder!

>
> Hi,
>
> I encountered the following problem. When doing an UpdateAllQuery before a
> persist-operation, the INSERT is executed first, then the UPDATE instead
> of
> the other way around.
>
> I roughly have the following code:
>
> public void create(.....) {
>
>        // Update index = index + 1
>        if (....)
>           this.moveIndex(portalGuid, userGuid, index);
>
>        // Persist bookmark
>        BookmarkEntity bookmark = new BookmarkEntity();
>        bookmark.setGuid(bookmarkGuid);
>        bookmark.setUserGuid(userGuid);
>        bookmark.setPortalGuid(portalGuid);
>        bookmark.setForURI(forURI);
>        bookmark.setCaption(caption);
>        bookmark.setIndex(index);
>        this.entityManager.persist(bookmark);
>        this.entityManager.flush();
>        return bookmark;
>    }
>
>    // Move index one position upwards
>    private void moveIndex(String portalGuid, String userGuid, int
> fromIndex) {
>
>        // Create expression
>        ExpressionBuilder expressionBuilder = new ExpressionBuilder();
>        Expression expression =
>                expressionBuilder.get(BookmarkEntity.FIELD_USER_GUID).
>                equal(userGuid).
>
> and(expressionBuilder.get(BookmarkEntity.FIELD_PORTAL_GUID).
>                equal(portalGuid)).and(
>                expressionBuilder.get(BookmarkEntity.FIELD_INDEX).
>                greaterThanEqual(fromIndex));
>
>
>        UpdateAllQuery modifyQuery = new
> UpdateAllQuery(BookmarkEntity.class);
>        modifyQuery.setSelectionCriteria(expression);
>
> modifyQuery.addUpdate(expressionBuilder.get(BookmarkEntity.FIELD_INDEX),
>
> ExpressionMath.add(expressionBuilder.get(BookmarkEntity.FIELD_INDEX), 1));
>
>        // Execute delete query
>        JpaHelper.getEntityManager(entityManager).getActiveSession().
>                executeQuery(modifyQuery);
>
>        // Make sure that statement is flushed to database
>        JpaHelper.getEntityManager(entityManager).flush();
>
>    }
>
>
> The following SQL is executed:
>
> 1) INSERT INTO ooi_bookmarks (bookmark_id, bookmark_guid,
> bookmark_for_uri,
> bookmark_index, bookmark_caption, portal_guid, bookmark_date_creation,
> user_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
>        bind => [null, xxx, z, 1, null, y, 2009-07-28 15:42:09.527, x]
>
> 2) UPDATE ooi_bookmarks SET bookmark_index = (bookmark_index + ?) WHERE
> (((user_guid = ?) AND (portal_guid = ?)) AND (bookmark_index >= ?))
>        bind => [1, x, y, 1]
>
> 3) UPDATE ooi_bookmarks SET bookmark_index = (bookmark_index + ?) WHERE
> (((user_guid = ?) AND (portal_guid = ?)) AND (bookmark_index >= ?))
>        bind => [1, x, y, 1]
>
> So, when issuing the FLUSH, then two UPDATES are executed. When I omit the
> flush, then only one UPDATE is executed.
>
> When I do the update after the persist, then everything works ok and the
> SQL
> is executed as one INSERT and one UPDATE, but of course, with the wrong
> logical result.
>
> I suppose I could have something to do with the JpaHelper being executed
> first?
>
> Have been moving the code around for a while now, but not coming closer to
> the solution. I am using 2.0.0-SNAPSHOT.
>
> Greetings
> Jan
> --
> View this message in context:
> http://www.nabble.com/UpdateAllQuery-and-Persist-executed-in-wrong-order%21-tp24698099p24698099.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[ossaert]

Hello,

Andrei Ilitchev wrote:

(is it execute updateAllQuery; then flush, then persist? or updateAllQuery,
then persist, then flush?)

The flush seems to play a minor part in this. When I omit the flush, the result is the same except that I have only ONE update statement after the INSERT.

Anyway, this code above should lead to the bug. I tried also a Delete-query, but when the same result. The DELETE is then executed AFTER the INSERT eventhough it is executed BEFORE the persist in the source-code.

Greetings,
Jan

[Andrei Ilitchev]

As I mentioned before, UpdateAll/DeleteAll executing after all other sql is
intentional default behaviour - it's not a bug  (but UpdateAll executed
twice would be).
Postponing execution of UpdateAll/DeleteAll queries done because after
UpdateAll/DeleteAll objects registered in the persistence unit (em cache)
may become stale.

To execute UpdateAll/DeleteAll query immediately, set
  modifyQuery.setShouldDeferExecutionInUOW(false);


----- Original Message -----
From: "ossaert" <decooman@...>
To: <eclipselink-users@...>
Sent: Wednesday, July 29, 2009 4:27 AM
Subject: Re: [eclipselink-users] UpdateAllQuery and Persist executed
inwrongorder!

>
> Hello,
>
>
> Andrei Ilitchev wrote:
>>
>> (is it execute updateAllQuery; then flush, then persist? or
>> updateAllQuery,
>> then persist, then flush?)
>>
>
> The flush seems to play a minor part in this. When I omit the flush, the
> result is the same except that I have only ONE update statement after the
> INSERT.
>
> Anyway, this code above should lead to the bug. I tried also a
> Delete-query,
> but when the same result. The DELETE is then executed AFTER the INSERT
> eventhough it is executed BEFORE the persist in the source-code.
>
> Greetings,
> Jan
> --
> View this message in context:
> http://www.nabble.com/UpdateAllQuery-and-Persist-executed-in-wrong-order%21-tp24698099p24714670.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users