Clarification on DBMS Locking
I'm doing an intro class on Database Management Systems and I asked a question that my book didn't answer. This question is not from my homework, I was just curious.
The textbook constantly emphasizes that a transaction is one logical unit of work. However, when you come across shared / exclusive locking modes, I am a little confused.
The book had a diagram that looked like this:
Time | Transaction status
1 blocking request
2 Receive Lock
3 process transaction
4 Release Lock
5 Lock released
Is transaction processing running concurrently, or is it being processed using separate locks?
If there are instructions in two transactions that result in a shared lock as well as an exclusive lock, are these transactions executed concurrently or are they scheduled one after the other?
source to share
The answer is, as usual, "it depends" :-)
Generally speaking, you don't need to take out all your locks before you start; however, you must remove all locks before removing any locks. So you can do the following:
lock resource A
update A
lock resource B
update B
unlock A
unlock B
This allows you to be a little friendlier to other transactions that might want to read B, and don't care about A, for example. This increases the risk - you won't be able to get the lock on B and decide to cancel the transaction. Their breaks.
You also want to always acquire all locks in the same order so you don't get stuck (transaction 1 has A and wants B; trans 2 has B and wants A; noon standoff, no one wins. If you follow sequential order, trans 2 will try to get A to B and either wait, letting trans 2 continue, or fail if trans 1 is already running - no deadlock anyway).
Things get more interesting when you have intent locks - locks that are taken as shared with an option to make them exclusive. This might be covered somewhere in the back of your book :-)
source to share
In practice, each operation obtains the required lock before proceeding. SELECT will first acquire a shared lock on the row and then read the row. UPDATE will first acquire an exclusive lock on that row and then update the row. In theory, you could say "locks are acquired and then transactional processes", but in real life it is every single operation in a transaction that knows which locks are needed.
source to share
"If there are instructions in two transactions that result in a shared lock as well as an exclusive lock, are these transactions executed concurrently or are they scheduled one after the other?"
Locking does not consist exclusively of shared / exclusive. The most important thing about blocking is the resource to which it belongs.
Two transactions, each with an exclusive lock on separate resources (say, two separate tables, or two separate sections, or two separate pages, or two separate lines, or two separate printers, or two separate IP ports, ...) can continue to run simultaneously without any problems.
Serialization of transactions becomes necessary only when a transaction requests a lock on some resource, where the shared mode of that lock is incompatible with the lock held on the same resource by some other transaction.
If your tutorial does indeed give the sequence of events as you claim, then throw it away. Lock requests appear when a transaction is being processed, and there is no definitive and final way for the transaction processor to know at the start of a transaction that it will lock it (otherwise a deadlock would be a non-existent problem).
source to share