List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 2 1999 12:53am
Subject:Atomic operations and table locking
View as plain text  
>>>>> "Michael" == Michael Griffith <mgriffith@stripped> writes:

Michael> I understand that mysql does atomic updates. So every time I update a table
Michael> it is locked so that another connection cannot interfere until the update
Michael> succeeds.

Yes.

Michael> I had table with 1 million rows. All clients combined, it averaged several
Michael> SELECTS and several UPDATES every second. Often when there are many updates,
Michael> the machine would slow tremedously because the table spent most of its time
Michael> locked. I need the UPDATES done first, or at least in the order received
Michael> (not after the SELECTS) so I can't use LOW_PRIORITY

Michael> I was able to fix the problem temporarily by splitting the large table into
Michael> 10 smaller tables, but I will soon have the same problem again as those 10
Michael> tables grow to be larger.

Michael> Is there a way to turn off atomic updates. Due to the nature of the
Michael> application I can be certain that no other clients are updating the same
Michael> row, so why do I need atomic updates? Is there a reason why several threads
Michael> cannot read or write to the same table at the same time, when I can be
Michael> positive there will not be row conflicts? then when there is a possibility
Michael> of conflicts I can use "LOCK TABLES"

Michael> Is it possible to turn off atomic updates? Am I missing another solution?

Yes, it would be possible, but one would have to add some extra code
to update some record caches if one deletes or updates dynamic
length tables.  Fixed length tables would be easier to fix. (One can
already do the later on the ISAM level ;  One just have to add some
hooks to allow MySQL to use this!)

I shall put this on the TODO and think about it...
(Sorry, but I am very tired for the moment)

Regards,
Monty
Thread
Atomic operations and table lockingMichael Griffith2 Sep
  • Atomic operations and table lockingMichael Widenius2 Sep