Posted by Gordon Burditt on 09/09/05 06:38

>>>I am trying to figure out when it is appropriate to use shared and
>>>exclusive locks with InnoDB. Using shared locks (lock in share mode), I
>>>can easily create a scenario with 2 clients that deadlocks - start 2
>>>transactions, open 2 shared locks, and both try to insert a new row
>>>before either commits.
>>>
>>>Using exclusive locks (for update) I cannot come up with a scenario that
>>>results in a deadlock, since the very nature of the exclusive lock means
>>>no other queries should be able to access the row(s) in question until I
>>>commit.
>>>
>>>If this is the case, my question then is this: why would I ever use a
>>>shared lock if doing so can result in deadlocks?
>>
>>
>> Because no other queries should be able to access the row(s) in
>> question until you commit. That means that if you go to lunch with
>> an uncommitted transaction, the CEO's hourly sales report won't be
>> ready on time, and you may come back to a pink slip. Worse, you'll
>> block the credit card billing process. It also may hang your web
>> server waiting on locks for just about every page containing info
>> from the database, depending on what you locked.
>>
>>
>>
>>>There must be a reason
>>>because we have them, and going through tutorials I can see HOW they are
>>>used, but I am not sure what advantage they would have over exclusive
>>>locks. It seems to me that exclusive locks are "safer", but I know I
>>>must be overlooking something.
>>
>>
>> Exclusive locks are in some ways like everyone in the world waiting
>> in line for ONE restroom. They may limit the number of users your
>> webserver(s) can handle regardless of how many parallel web servers
>> you add, or how much you spend on more RAM and fast CPUs.
>>
>> Gordon L. Burditt
>
>Thanks for the answer, Gordon. I was just wondering, if all my
>transactions either commit or rollback immediately, i.e. are not
>dependent on any user input once the transaction is started, is this
>still an issue?

The "gone to lunch" isn't an issue. The bottleneck may still remain,
as the database does not execute transactions "immediately", and
if most transactions need to hold locks, you can't speed up the
database server by adding more CPUs as only one thread can run.
This does depend on how granular your locks are and how much updates
tend to update the same records, but you can still have the "world
waiting to use one restroom" problem.

Note that for data integrity, you may *NEED* at least a "virtual" lock
on the data before you bring up the data screen to after you commit
the transaction, and this problem won't go away. You (and perhaps the
customer on the phone) look at the data and together compute a set of
changes, which may not be valid if the original data changes out from
under you. In the web environment, a "virtual" lock is sometimes
implemented by putting the original record data in hidden fields on
a web page. When you submit, if the record in the database doesn't
match the record data in the hidden fields, reject the update. (This
can also avoid the user using the BACK button and submitting the
same update twice.)

>Let me give a real world example, it would help to clarify things a lot
>for me. Say I am booking a hotel online. For the transaction where I
>am actually updating the database with my reservation, should I use a
>shared or exclusive lock? It seems to me that with a shared lock,
>"potentially" someone could see that same room as being available even
>though once my transaction finishes, it will be gone.

>With an exclusive
>lock, no one could search room availability until I am done making my
>reservation (I am only talking about locking the rows once the user has
>actually confirmed everything, i.e. no more user input is involved).

And with heavy reservation traffic, the wait time to get a reservation,
or even search for possibilities, may become unacceptable, and the
800 reservation line phone bill may skyrocket.

You *CANNOT* always eliminate the user interaction. For example,
after the reservation is confirmed, you discover the price on the
room has changed, and the customer cancels the reservation or asks
to try for a cheaper room. Or the customer asks for a particular
room. Or five adjacent rooms. It may also depend on what you can
do in one transaction. For example, how do you deal with the guy
who wants 120 rooms, or forget the whole thing, when you are limited
to reserving 3 rooms at a time, or he refuses to give you 120 names,
addresses, and credit card numbers until you GUARANTEE him reservations
for all 120? Oh, yes, there's a another guy with another convention
asking for a lot of rooms on another line for the same weekend, and
you don't have enough rooms for both.

Gordon L. Burditt

[Back to original message]