2

Is it possible to execute an update query then a delete query right after the update one in the same transaction? I'm trying to activate an account based on a token's hash and then remove that token in the same transaction.

transaction.begin();
entityManager
.createNativeQuery(
                      "UPDATE accounts AS ac "
                    + "INNER JOIN account_tokens AS ak ON ac.id = ak.account_id "
                    + "SET ac.account_state = "
                    + "CASE "
                    + "WHEN ac.account_state = 'AWAITING_ACTIVATION' THEN 'ACTIVATED' "
                    + "END "
                    + "WHERE ak.token_hash = :tokenHash")
                    .setParameter()
                    .executeUpdate();
em.createNativeQuery(
                      "DELETE FROM account_tokens AS ak "
                    + "WHERE ak.token_hash = :tokenHash")
                    .setParameter()
                    .executeUpdate(); // delete
transaction.commit();
Vio Ariton
  • 407
  • 1
  • 5
  • 17
  • 1
    logically possible. you are updating and deleting on different tables that's fine. doing on same table may lead data inconsistency. – JAVAC Aug 09 '18 at 16:09
  • 1
    Its logically quite okay. I dont find any problem with that. Whats you specific confusion ? Did you get any problem with any scenarion ? – Shafin Mahmud Aug 09 '18 at 16:22
  • @ShafinMahmud thing is that I'm deleting the first entity after executing the update query - I'm using an inner join and update a table on Its foreign key and after that, I'd like to remove the entity. Maybe I should have mentioned this in the question, my bad. Would this be alright?-- I won't have any data inconsistency since this is one-time thing – Vio Ariton Aug 09 '18 at 16:29
  • You can update your question anytime. Could you please mention the the *update* and *delete* query that your are handling? You have described that, I know. But seeing the queries would clear the picture more concisely. Please update your question with these details. – Shafin Mahmud Aug 09 '18 at 16:54
  • @ShafinMahmud Done. Thanks. – Vio Ariton Aug 09 '18 at 17:03
  • Interesting, +1. You may also want to consider if there's a need for locking. I asked a [question](https://stackoverflow.com/q/38664913/1214974) last year that might be relevant in that case. – Janus Varmarken Aug 09 '18 at 17:07
  • @VioAriton How about calling `em.flush()` right after first update query? I sense it will force update query execution before delete. And thus you could avoid uncertainity of query execution order. – Shafin Mahmud Aug 09 '18 at 18:07

1 Answers1

1

Yes, Use PL/SQL Procedure.

You can't reduce the number of queries - they all do different things - but you could reduce the number of round trips to the database and the number of parses by wrapping it all as a PLSQL function.

   CREATE PROCEDURE s_u_d(a)
    BEGIN

    UPDATE tab_x SET tab_x.avalue=1 WHERE tab_x.another=a;

    DELETE FROM tab_y WHERE tab_y.avalue=a;

    SELECT * 
    FROM tab_x
    WHERE tab_x.another=a;

    END;
Angad Bansode
  • 825
  • 6
  • 15