0

From what I read to analyze transactions the basic approach is the concept of conflict serializable.
So the database system must ensure that the scheduling of transaction is equivalent to a serializable via this approach.
But I can not see how this model works in this trivial example.
Assume the following: 

T1  
READ(A)  
A = A - 50  
WRITE(A)

and transaction 2: 

T2  
READ(A)  
A = A - 50  
WRITE(A)

According to conflict serializability only WRITE(A) are conflicting. But besides or a possible execution may be:
T1 and T2 do READ(A) concurrently. As a result each read the original value. So the final result is A - 50 and not A -50 -50 as is the outcome of a serial execution.

So from my point of view these 2 reads are conflicting (but the theory of conflict equivalency considers them non conflicting)

So I don't understand how this basic example is covered by the approach, moreover this is the standard execution in databases with REPEATABLE READ isolation level.

Jim
  • 18,826
  • 34
  • 135
  • 254

1 Answers1

1

According to conflict serializability WRITE operation conflicts both with READ and WRITE operations. Therefore, even though each READ (A) does not conflict with the other one, it does with the WRITE (A) instruction of the other transaction. For a deeper explanation, you could consider checking "Database System Concepts", by Abraham Silberschatz et. al. The chapter 14.6 of the 6th edition should be clarifying.

EDIT: Based on what I said before, and adding that a schedule is conflict-serializable if, and only if, it can be derived from a serial one (no pair of transactions can be executed in parallel) swapping the order of pairs of instructions which are not conflicting with each other, it is evident that the effect of conflict-serializable schedules on any database will be equivalent to this of serial ones. Therefore, we can conclude that conflict serializability grants consistency.

Being that said, database managers can implement this kind of scheduling or not, with the corresponding effect on consistency of transactions.

user3407000
  • 188
  • 2
  • 10
  • This is irrelevant. Because nothing stops READ(A) to use the same value in both transactions. BTW that is the book I am reading (unfortunately). – Jim Mar 11 '14 at 21:02
  • A schedule is conflict-serializable if it can be derived from a serial one just by swapping the order of execution of pairs of instructions which are not in a conflict with each other. A serial schedule for your example would be executing T1 and then T2 or viceversa. Either case, you cannot swap the WRITE(A) from one with the READ(A) from the other, so the case in which both READ(A) instructions are executed before any of the WRITE(A) is not a possible scenario. – user3407000 Mar 11 '14 at 22:11
  • 1)I think you are going backwards.A schedule is equivalent to a serial one by swapping non conflicting instructions (and not vice versa). 2)Try it out. Just open 2 client sessions in MySQL and run the code wrapped in BEGIN-COMMIT. The final result is `A-50` and not `A -50 -50`. This means that *both* read the original A – Jim Mar 11 '14 at 22:20
  • Answering to your point 1), you can get to a serial schedule by swapping non-conflicting instructions of another one if, and only if, you can do the reverse path (i.e. get to the conflict-serializable one from the serial one swapping non-conflicting instructions). Regarding the point 2), I don't know what you have tried and how you have done your testing, but I believe that you executed each instruction as an independent transaction instead of as a part of an atomic transaction (T1 or T2). Maybe I'm wrong, I would like to know the process you have followed to find out. – user3407000 Mar 11 '14 at 22:36
  • For the process you ask see the test of akuzminsky at http://dba.stackexchange.com/questions/60681/transaction-basics-what-is-the-result-of-2-transactions-running-concurrently – Jim Mar 11 '14 at 22:45
  • I have seen akuzminsky's test. He is setting directly amount to 400 not to amount-100, so even if the sessions were serial instead of parallel, the results would have been the same, unless there is something I have missed. Regarding MySQL, if it does by default allow to execute the instructions in this order, it is not making a conflict-serializable schedule. I am not sure about that, but it would be really surprising to me if it did allow this scheduling. – user3407000 Mar 11 '14 at 23:04
  • That is a great catch!I will retest and let you know – Jim Mar 12 '14 at 07:11
  • Anyway, I have realized that the test akuzminsky is performing imposes a schedule to the DB manager, since it doesn't know beforehand all the instructions that will be executed. The only thing it can do is setting locks properly: there is no way it could make a conflict-serializable schedule without knowing the whole body of the transactions. I'd bet that if you send the queries grouped in atomic transactions, there will be no concurrency problems (for example, through a program that sends T1 in parallel N times; I guess the result would be correct). – user3407000 Mar 12 '14 at 11:37
  • By the way, if the locks are set properly and you force the READ and WRITE instructions in the order you have suggested, the result should be a deadlock, for each transaction would be waiting for the other one to release the shared lock to set its exclusive lock. I don't know how MySQL deals with that, anyway; there are several recovery algorithms for that. So, finally answering your initial question, any conflict-serializable schedule would grant consistency for your transactions. Whether MySQL applies this kind of planning or not is another question. – user3407000 Mar 12 '14 at 11:46
  • I tried it.If I do `SELECT amount` and then `UPDATE` then on `UPDATE` I get the lock and I end up with 300.But I did not test if I do a SELECT in my application code if I would have read the 500 and the UPDATE would end up as 400 or the shared lock is upgraded and one of the transactions is restarted to end up with 300 – Jim Mar 12 '14 at 21:37
  • If you try that on an external application, don't forget to disable autocommit for transactions so all the instructions are executed together as an atomic transaction in the end. If you do that, you should get the result right independently on how many transactions you run in parallel. – user3407000 Mar 12 '14 at 21:44
  • +1. But I think you should modify your answer with the explanation in your comments so that I can mark it as an accepted answer – Jim Mar 23 '14 at 11:37