Certainly I understand how the deadlock can occur, but the question is why
is this acceptable behaviour? Is the application developer expected to
handle this case? If so, how? and shouldnt this be included in the manual
under BDB problems? If Im supposed to wrap _every_ sql call I make to a BDB
table with something like: 'do { query } while ( !deadlock );' why would I
even bother trying to use BDB tables? This seems to me like a _fundamental_
weakness of the BDB table type, and should at least be documented as such...
dpk
----- Original Message -----
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, September 16, 2001 2:14 AM
Subject: Re: BDB table error
> Dana,
>
> if you have page level locking, you can easily
> get a deadlock from an atomic insert done as
> a single transaction, and another user doing
> an atomic select in another single transaction.
>
> It happens this way:
>
> User A User B
>
> insert record select record from secondary
> to primary index index, get S-lock on page 2
> get X-lock on page 1
>
> select record from primary index
> get X-lock on page 1 -> MUST WAIT
> insert record to
> secondary index
> get X-lock on page 2 ->MUST WAIT -> DEADLOCK
>
>
> Regards,
>
> Heikki
> http://www.innodb.com
>
> >Ok, so the problem Im seeing is that any table that has an index other
than
> >the primary key is prone to deadlocks.
> >I cant think of any reason that this is acceptable behaviour ( i.e. not a
> >bug ), because I am doing 4 atomic operations on a single table with no
user
> >level locks.Here is a test:
> >CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary
key,
> >key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB;
> >( replacing unique(key1) with index(key1) also results in deadlocks ).
> >Now run the following 4 queries in parallel:
> >## Insert a new row with 2 key values - 1 auto_incremented and 1
specified
> >INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some
> extratext'));
> >## Update a non key column in row
> >UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID();
> >## Select based on secondary key
> >SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some
extratext');
> >## Select based on primary keySELECT * FROM bdb_test WHERE
id=LAST_INSERT_ID();
> >I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running
> >linux ) with 64 simultaneous threads.
> >It appears that as the table gets larger ( and more pages created for
> >locking, hehe ), the probability that we get a deadlock grows.
>
>
>
> ---------------------------------------------------------------------
> 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 [EMAIL PROTECTED]
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
>
>
>
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
