Posted by Mladen Gogala on 09/09/05 07:17

On Fri, 09 Sep 2005 02:39:52 +0000, Marcus wrote:

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

Now, that's simple: only one person at the time can hold an exclusive lock,
while more people can hold shared locks. MySQL has several unpleasant
characteristics:
1) MySQL allows other users to see uncommitted data in the other
transactions, unless every select is protected by a shared lock.
That is so called "dirty read". The problem is this: I want to
pay my phone bill on-line. I go to my bank account, invoke the
appropriate form and enter the amount. The officer of the phone
company, having the bank account at the same bank is running
financial report at the same time. His report sees my payment
and puts it on the report, before I actually committed the transaction.
In the mean time, I decide to pay my cable bill instead, and roll the
transaction back. Result: the officer of the phone company has just
produced a false financial statement. Dirty reads are bad and,
unfortunately, are the basis for the blinding speed of MySQL.
2) You can guard against that by requiring that each query is protected
by the shared lock on the underlying tables. Unfortunately, shared lock
is incompatible with an exclusive one, they cannot coexist on the same
table at the same time. To do updates, inserts or deletes, also known
as DML (Data Modification Language) you need an exclusive lock, which
will prevent anybody to see the data while you're updating the table,
therefore also preventing dirty reads.
Using exclusive locks extensively will, effectively, turn your database
into a single user database. That might be what you want, but I don't
see many of those.

Have you ever considered PostgresSQL? It is by far superior to MySQL with
respect to the transaction mechanism.

--
http://www.mgogala.com

[Back to original message]