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

Mladen Gogala wrote:
> 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?
>

Thank you very much for the detailed reply, Mladen. In response to your
question, I am running MySQL 4.0.22 using InnoDB table types.

With regards to your explanation, the original scenario I explained:

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

this may not be a deadlock in the sense that you explained, but MySQL
still reports it as a deadlock:

ERROR 1213: Deadlock found when trying to get lock; Try restarting
transaction

I don't think I can avoid this situation above solely by ordering my
queries in a certain order. I have various scripts that access the same
table and do different updates of it, so it is more than just making
sure my queries all execute in a predetermined order.

This is why I am confused as to why I would use a shared lock, since
using an exclusive lock seems to avoid these issues. I should also note
that all my locks are executed in transactions, so there is never a
situation where the database hangs and waits for the user to commit a
transaction. Thanks again in advance.

[Back to original message]