1. What does ACID stand for?
  2. which of the ACID properties does this violate? img
  3. which of the ACID properties does this violate? img
  4. which of the ACID properties does this violate? img
  5. What is a schema?
  6. What is a transaction in SQL?
  7. What is a schedule?
  8. Do transactions always transform a database from one consistent state to another consistent state?
  9. Do concurrent schedules gurantee consistency?
  10. What is a lost update in concurrent scheduling?
  11. What is a dirty ready in concurrent scheduling?
  12. What is inconsistent analysis in concurrent scheduling?
  13. When is a schedule seralisable?
  14. Is this serlisable? img
  15. Is this serlisable? img
  16. Where’s the conflict here? r1(x); w1(x); r2(x); w2(x); r1(y); w1(y)
  17. What is a conflict?
  18. r1(x); w1(x); r2(x); w2(x); r1(y); w1(y) Are there any other conflicts?
  19. When are two schedules conflict equivalent?
  20. Is this schedule conflict-equivalent? S: r1(X); W1(X); r2(X); w2(X); r1(Y); w1(Y); r2(Y); w2(Y)
  21. Is this schedule conflict-equivalent? S: r2(X); r1(Y); w2(X); r2(Y); r3(X); w1(Y); w3(X); w2(Y)
  22. What is a serial scheduler?
  23. What is a concurrent scheduler?
  24. What is two phase locking?
  25. Is this 2 phase locking? img
  26. Is this 2PL? img
  27. If S is a schedule containing only 2PL transactions, is it conflict-serlisable?
  28. What is the notation for shared lock?
  29. What is a shared lock?
  30. What is an exclusive lock?
  31. Can a shared lock on an item, X, be upgraded to an exclusive lock?
  32. What is an update lock?
  33. What’s wrong here? u1(x); r1(x); u2(x)?

    Answers

1.

Atomicity


Consistency


  1. consisnsistency, but not isolation.
    2 people end up sitting in the same seat on the same flight, which isn’t a real world event and violates consistancy. Isolation is a bit more tricky. The definition here: “The effect of concurrently executing a set of transactions is the same as if they had been executed serially. So if you run 2 transactions at the same time it should be just like if you had run them seperately. “ is important. If you put user 1 transaction first, then user 2 transaction whaty we get here is user 1 books seat 14b, and user 2 books seat 14b. If we put user 2 first, nothingf happens. They both have the same seat. No matter who goes first, the outcome is the same. #

We end up having booked seat 14b to both user 1 and user 2. Since this mapped exaclty what happened if we did it serially (user 1 or user 2 first), as long as one of them is the same (which they are), then it satisfies isolation. so isolation is not violated, in fact, it is isolated.

In his own words: “

Hi!

Example 1 was covered in more details in lecture 4, because many people had trouble understanding why it did not break isolation. The reason is that the definition requires 2 things:

(1) A transaction only makes its updates visible to other transactions after it has committed

(2) The effect of concurrently executing a set of transactions is the same as if they had executed serially (“serialisable”)

The first part is not a problem here because both reads are before any writes.

Thus, lets consider (2). The effect on the database of executing the serial scheduler where say we first execute user 1s transaction and then user 2s transaction is to book seat 14b twice (well, to be precise, the effect is to change seat 14b to be occupied). This is also the effect of doing them with the scheduler on the slide. Thus, the effect is the same and thus, the scheduler on the slide does not break requirement (2) of isolation either. Hence, since the scheduler on the slide satisfies all requirements, it does not violate isolation.

The reason example 3 violates “A” is that a part of user 1s transaction is executed but not all.

The reason why example 3 violates “I” is that it breaks requirement (1) of isolation. User 2s read sees a write from user 1, even though user 1 did not commit.”

  1. Atomicity. We either do everything or we do nothing. We added £100 to account 456, but we didn’t subtract it from account 123. We only did half of the things.
  2. Description of all tables in the database
  3. A sequence of SQL statements that must be executed in a certain order to make sure that no problems arise. By using transactions, it ensures serialisable behavior. This means that the transaction is executed as if it was executed in isolation from all other transactions.

Transactions let us state that a transaction be executed atomically (all or nothing). If there is an error, the transaction reverts back to nothing. If there is no error, it does everything.

  1. Holds operations of one or several transactiosn for execution. Operations of individual transactiosn occur in the order in which they occur in the transaction. It decides what comes first and what comes last. It schedules.
  2. Yes, they do. In line with ACID.
  3. No, they don’t.
  4. A successful transaction can be overwritten by another transaction.
  5. A transaction can see the intermediate results of another transaction before it commits
  6. A transaction reads several values but another transaction updates some of them in the meantime.
  7. If there is a serial schedule that has the same effect on every initial database state. So no matter what you start with, the output is always the same.
  8. Yes. Nothing effects anything else, so you can run them in whatever order you want.
  9. No, unless N is 0. If we go back, we end up having X + M at the end of the database. X + m is equal to X+ M.
    img As you can see, we can do all of the adding N stuff at the start. N is 0, so effectively it does nothing to it. In general, even though there is 1 case where it is serilisable we say that it is not because for most cases it is not. This is an exception. Maybe watch out for this in an exam, I can see him trying to trick us with this.
  10. It’s at w1(x); r2(x); because one of them writes and reads. “Operations can only be in conflict if one of them is write and they access the same item”. So we both look at X, once is a write therefore we have a problem here.
  11. A conflict in a schedule is a pair of operations from different transactions such that: the operations access the same item at least one of them is a write operation
  12. Yes. r1(x); w1(x); r2(x); w2(x); r1(y); w1(y);
  13. Two schedules, S and S’ (S prime) are conflict-equivalent if S’ can be obtained from S by swapping any number of consecutive non-conflicting operations from different transactions.
  14. Yes, it is. We can swap W2(x); r1(Y) as we have a different item. r2(X); r1(Y) as we have a different item. w2(X) and w2(Y) as we have a different item. r2(X); w1(Y) as different. Now we have the same scheduler.
  15. No, because if we look at y. r1(Y); …; r2(Y); …; w1(Y); …; w2(Y); all of these are in conflict and we cannot change them around.
  16. We first execute one entire transaction and then then ext one and so on. So we do r1(x) ; w1(x) before moving onto transaction 2 r2(y); w2(y)
  17. Two phase locking is a modifaction of the simple locking mechanism that gurantees conflict seralisability. In each transaction, all locks precede all unlocks. It has 2 phases. Phase 1 is where you request the locks, phase 2 is where you unlock.
  18. No, because we have an unlock followed by a lock. All the locks should be before the unlocks. So no, not 2PL.
  19. Yes, because all the locks are before the unlocks.
  20. Yes.
  21. s-lock(x)
  22. Requested by transactions to read item X. Granted to several transactions at same time.
  23. Requested by transactions to write item X. Granted to at most one transaction at a time.
  24. Yes, but it could cause deadlock.
  25. It says “hey, I want to read now but later on I might want to write to the item”. Syntax: y-lock(X)
  26. You can only have 1 update lock on the object x at any given time. transaciton 1 has to unlock x.