Posted by Mladen Gogala on 09/09/05 04:51

On Thu, 08 Sep 2005 23:20:44 +0000, Marcus 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.

Deadlock is a circular wait: session 1 holds a lock on table A and wants
to lock table B, while the session 2 holds the lock on table B and wants
to lock table A. That will produce circular wait situation, otherwise
known as deadlock.
The solution for the deadlock problem is simple: always lock resources in
the same order. If one application locks table A and then locks table B,
don't allow any other application to lock table B first. That is how
deadlocks come into this world.
The problem is that many deadlocks are not actual deadlocks, there are
also hangs and infinite waits, with the same unpleasant effect for the
end-user. End users are rarely happier if you explain them the true
nature of their plight, they will usually come up with an unreasonable
request to get the [EXPLETIVE DELETED] thing done. Infinite waits happen
when user inserts a record into our lucky database, then receives a phone
call from a friend and goes to lunch. Application, on the other hand,
doesn't commit until the user answers the necessary question "Are you sure
[Y/N]:". Anybody else who wants to lock the same resource can only hope
that their colleague had opted for the fast food joint. Usually, fast food
is not fast enough to prevent the others from becoming somewhat unhappy.
The solution: don't create applications which do that. No locks of any
kind should be imposed on the system until the user decides to perform the
actual commit. Technically, that's not a deadlock, it's an infinite wait.
Make sure that your users are aware of that fine distinction.
The next type of lovely condition that resembles deadlocks are hangs.
Hangs are, essentially, infinite waits for the system resources or
deadlocks on them. Lock manager itself is usually implemented as a queue
manager, with locked resources as queueing points and locks as waiters in
those queues. Each lock needs a "lock block" or "enqueue resource". If you
don't have enough of those on the system then you cannot lock the resource
you need until some lock blocks are freed, usually by ending another
transaction. That can manifest itself both as a very long wait or a
condition that the end users call "slow f***ing database" with their
voices pitched to the high decibel levels, rivaling the local airport. The
solution for that is to create enough lock blocks. Those blocks are
typically something like 8 bytes in length, so if you put in 16384 more of
them, you'll end up using 256k more RAM, which really serious today when
desktop PC regularly has 512MB and server machines normally have
multi-gigabyte memories. Of course, it would help a lot if you could tell
us what garden variety of the database software you are using. In some
databases, readers block writers, some have lovely surprises like lock
escalation for the unwary users or come extremely under-configured, so
that you run out of lock resources when you look inquisitively at the
machine. Not all databases are created equal. Lock escalation is the event
of replacing bunch of row locks with a table lock, to prevent high
consumption of lock blocks. Unfortunately, that has unpleasant
consequences for anybody else who may wish to update that table and
effectively turns it into a single-user table.
Did I mention that some databases do not have row locks at all, but are
locking things on block or page level? Welcome to the lovely world of
an Oracle DBA. Do androids dream of electric sheep?

--
http://www.mgogala.com

[Back to original message]