Posted by Marcus on 09/09/05 05:46

Gordon Burditt wrote:
>>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?

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).

Thanks again!

[Back to original message]