Serializability schedules: can someone help me understand it?

Question

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!

Answer 1

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.