1

I am struggling with the Databases courses I have, so I am trying to find answer to my question.

This has been asked:

Add the appropriate locks to enforce serializability on the following schedule:

T1: W(Y)

T2: R(Y)

And:

Add the appropriate locks to enforce serializability on the following schedule:

T1: R(Y) R(Z) W(Y)

T2: W(Y) R(Z)

For me, right now, it's hocus pocus. Could someone explain to me what exactly I need to do here... as in: what's the structure I need to follow? I need some structure to understand it.

R(Y) is a read action on database object Y

W(Y) is a write action on Y

S(Y) sets a shared (read) lock on Y

X(Y) sets a exclusive (write) lock on Y

U(Y) releases any lock on Y

PS: it's not about the answer to the questions, although I would appreciate it... yet it's about me understand WHY that's the answer.

Hope someone can help me. I'd appreciate it a lot!

1 Answer 1

2

I just invented a lame example that might help.

Have you ever driven by a gas station while they are changing the prices? (And the prices are big numerals that need to be taken down and new numbers put up.)

There can't be two guys changing the same price at the same time -- exclusive lock.

Everyone riding by can read the numbers -- shared lock (sort of). But, if you want to get the price you saw, and don't want it to change before you pump your gas, it gets more complex. You want the shared lock to prevent changing the price until you are finished with your "transaction" (filling your tank at the price you saw).

In my lame example, you can't really take out a shared lock, except with a "dirty read". "Dirty read" is a flavor of "transaction isolation". The price can change while after you see the price and before you pump your gas. With a stronger isolation, you will get the price you saw.

3
  • Thank you Rick, I appreciate it (hence the upvote). What I don't get, though, is the order of T1 and T2. Do they affect each other? What if I don't release a lock? I mean: when does the structure fail and when not? Could you give a couple of examples of faulty transactions and a couple of good ones, where you'd explain what's wrong about it (using my notations)?
    – Siyah
    Commented May 24, 2017 at 17:52
  • SQL is designed to make it easy to release the locks. Write a transaction thus: BEGIN; do stuff; COMMIT : COMMIT releases all the locks. SELECT ...; versus SELECT ... FOR UPDATE; specifies a different level of locking. You use the latter when you may be about to change the fetched row in the same transaction. Again, that helps you get the lock, and COMMIT releases it.
    – Rick James
    Commented May 24, 2017 at 18:02
  • (Sorry, I'm not good at answering your question the way you would like it.) I've never explicitly asked for "serializable"; I don't know the use case that requires it.
    – Rick James
    Commented May 24, 2017 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.