RE: Transaction Does Not Write-Lock Table or Record

Hello:

We are evaluating MysSQL for use at our bank. I wanted to verify the
locking behaviour
so have conducted a simple experiment which seems to reveal that records
(or table) are
not properly locked on read, within the context of a transaction.

The experiment is to simply try accessing the same balance record..field
from two
different connections and see if the "first-come-first-serve" instance
gets the record
locked and blocks the second instance from reading it - until the first
instance commits;

How-To-Repeat:
Basically, create a 'test' database table called 'Balance' with a single
field calle 'b' (doesn't matter if its integer, decimal, or whatever,
for experiment purpose).

Terminal Window 1 Terminal Window 2
mysql> begin;
mysql> begin;
mysql> SELECT b FROM Balance
(returns number 'n')
. . .
mysql> SELECT b FROM Balance
(also returns same number 'n')
mysql> UPDATE Balance SET b=n+m;
(returns ok)
. . .
mysql> UPDATE Balance SET b=n+k;
(returns ok)
mysql> commit;
(returns ok)
mysql> commit;
(returns ok)

The obvious problem that results is that the amount 'm' (added in
Window 1) never gets added to the balance because both transactions
are allowed to READ the initial amount 'n'.

Under Microsoft and other DBs, the implementation of "READ COMMITTED"
results in a WRITE lock being placed on the source record (or table)
on the 1st read from a transaction. The definition of a WRITE lock
being that no other process/thread is allowed to READ the record/table
until the 1st transaction does either a COMMIT or ROLLBACK, releasing
the WRITE Lock. Under these other SQL systems the above experiment
results in the second SELECT being blocked (sleeps) until the first
transaction commits. You type in the second SELECT in its window, and
there is no response. When you go back to the first window and do the
Commit, you see its immediate response, then you see the response follow
in the second window (slept process is resumed).

I tried this with the default READ COMMITTED isolation-level, set the
default isolation level to SERIALIZABLE, and also tried the inline
SET AUTOCOMMIT=0, and SET ISOLATION LEVEL [READ COMMITTED | REPEATABLE
READ | SERIALIZABLE] options, and also with a BDB, MyISAMM, and InnoDb
table types - all with the same non-blocking result.

Either I'm doing something fundamentally wrong, or . . .

I am using the 3.23.52 version of MySQL that ships with RedHat Linux 8.0
personal.

Please help.
Best Regards,
Tom Ballister
Chief Technical Officer, EuroDirekt National Savings
+1 530 587 0358
tballister@eurodirekt.com
tom@ballister.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12992@lists.mysql.com
To unsubscribe, e-mail

Re: Transaction Does Not Write-Lock Table or Record

On Thursday 14 November 2002 23:17, Tom Ballister wrote:
> Hello:
>
> We are evaluating MysSQL for use at our bank. I wanted to verify the
> locking behaviour
> so have conducted a simple experiment which seems to reveal that records
> (or table) are
> not properly locked on read, within the context of a transaction.
>
> The experiment is to simply try accessing the same balance record..field
> from two
> different connections and see if the "first-come-first-serve" instance
> gets the record
> locked and blocks the second instance from reading it - until the first
> instance commits;

Basically this is quite expected behavior for Innodb tables. Innodb by default
has non-locking read - so transactions just read the old snapshot of records
which are updated at the same point by other transactions.

In general this gives much better concurrency due to less lock conflicts,
however there are some applications which would require different behavior as
you're stating.

MySQL has the following features

1) You can force SELECT statements to actually set locks.
SELECT * FROM TBL IN SHARE MODE will set shared locks on accessed records,
SELECT * FROM TBL FOR UPDATE will set exclusive locks on the records so other
transactions will not able to read them until the locks are released

2) Additionally In 4.0.5 (Sheduled for release in a few days) You will be
able to select transaction isolation mode to alter this behavior globally.
The isolation mode as in MS SQL is also supported.

You can find more information about this issue in MySQL Online documentation
or Innodb documentation at http://www.innodb.com/ibman.html





--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12993@lists.mysql.com
To unsubscribe, e-mail