-1

Assume that I have set the isolation level to serializable and a transaction like this:

  1. A new transaction is opened.
  2. All tuples having a specific condition are retrieved.
  3. If no such row exists perform the next step, otherwise go to step 5.
  4. Set a flag in my codes (a non-database operation) that prevents new such tuples to be inserted into the database.
  5. The transaction is commited.

is executed. My question is that considering that what is done is steps 3 and 4 are not database operations and the last database operation performed (except commit) is in step 2, can I still be sure that the flag is set only if there aren't such tuples already in the table? In other words, is it possible that after step 3 is performed and the condition is satisfied and before step 4 is performed (which prevents adding new such tuples) another thread inserts one such tuple into the database and result in a set flag while one such tuple is in the database?

Shayan
  • 2,758
  • 7
  • 36
  • 55
  • 1
    This depends a bit on what you mean by `I have set the isolation level to serializable`. Can you explain more about what you set and how you're enforcing it? – InverseFalcon Aug 14 '19 at 08:32
  • 1
    What system is this under, "relational-database"/SQL or Neo4j? It's also not clear whether your flag is in the DB or what "not database operations" & "another thread" (transaction?) means. Better to give code. PS Under serializable, transaction rollback or commit failure restores the DB to the state before. It doesn't restore non-DB state. Serializable says transactions are implemented as if done serial order. That precludes implementing a successful transaction as if another transaction's effects could be seen during it. – philipxy Aug 14 '19 at 09:03
  • At step 2 are you reading `WITH LOCK`? – AntC Aug 14 '19 at 16:17

1 Answers1

1

It has nothing to do with SQL ISOLATION LEVEL. The requirement regards two back-end systems that need to be synchronised transactionally. The fact that one side, the SQL side, has ISOLATION LEVEL is irrelevant (more, below).

You need what is known as Two-Phase Commit (technical term) in SQL. This allows you to perform a "non-database operation" that is ACID-synchronised with an ACID Transaction in SQL. The method (and SQL commands) that is required is a bit more than the steps you have given in the question.

  • Only a high-end, commercial SQL has the Two-Phase Commit facility (it is beyond the SQL compliance requirement)
  • Beware, not all database systems have the features and functions that is required for SQL compliance, such as ACID Transactions. So there is no hope at all that they will have TPC, which is beyond SQL.
  • Beware, even though your SQL may have ISOLATION LEVEL, it may not support SQL ACID Transactions. Eg. MySQL allows a CHECK constraint to be coded, but it does not perform the coded check.

Alternative

Instead of writing the required facility as SQL-centric (your Question), consider writing it outside SQL, with the call to an SQL proc IFF required. That is, write the non-SQL code as Atomic, with the SQL code inside it.

halfer
  • 19,824
  • 17
  • 99
  • 186
PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90