Hi!
>>>>> "Dana" == Dana Powers <dana@stripped> writes:
<cut>
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 )
Yes.
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.
Regards,
Monty
