List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:September 18 2001 9:32pm
Subject:Re: BDB table error
View as plain text  

>>>>> "Dana" == Dana Powers <dana@stripped> writes:


 For which statements do you get errors ?
Dana> Deadlock errors occur from inserts and updates.
Dana> I also see 'Can't lock file (errno: 12)' from all 4 statements after raising
Dana> the load past the initial deadlock prone level, but this is probably a
Dana> secondary effect.

Dana> Im not certain why the update should cause deadlocks - shouldnt a simple
Dana> update on a non-key column using a primary key where clause only need a
Dana> single lock on the primary index? ( Im assuming here that a single lock
Dana> statement cannot cause a deadlock )


Dana> It appears that as the table gets larger ( and more pages created
Dana> for
Dana> locking, hehe ), the probability that we get a deadlock grows.
>> Normally when the table grows, page locks conflicts should be more
>> uncommon.  The problem in your case is that for 3 of 4 of your
>> statements (all except the 'select based on secondary key', you will
>> create a lock on the last block for the primary index; In other words
>> all threads are trying to get a lock on the same key block.

Dana> Understood. Question: are blocks ordered by value, insert time or some other
Dana> hash algorithm?

By value.

>> Another problem is that for every update of type:
>> UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID()

>> You are hitting and locking the same block in the val1 index.
Dana> val1 here is not indexed, so this shouldnt be a problem, should it? As far
Dana> as I can tell, the UPDATE should only need a lock on the primary index - is
Dana> that incorrect?

Sorry, my mistake;  I accidently thought that val1 was also an index :(
Yes, you are right. This should only block the primary key block.

>> Page locks or row locks will never work good if you are always hitting
>> the same block.  If you can't change your application to not do this,
>> then I suggest you to try InnoDB tables instead.

Dana> Switching to InnoDB is certainly on the top of my list!

The main benefit of InnoDB is that Heikki is actively working on
improving both InnoDB and the MySQL-InnoDB interface.  For the moment
we don't have anyone actively working on the MySQL-BDB interface;
We do fix bugs, but we don't have time to add new improvements until
we find someone that can help us maintaining this interface.

Re: BDB table errorHeikki Tuuri16 Sep