Posted by Gordon Burditt on 09/09/05 05:21

>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

[Back to original message]