At 8/15/2002, you wrote: >Hi everyone! > >I'm fairly familiar with the table locking methods in MySQL but I have a >problem I can't solve. > >I have two main tables which are read simultaneously by many clients. The >clients only do reads, but they do it very extensively and for 30 secs each. > >The two main tables needs updates every 30-45 minutes. The process that >updates the tables lasts about 50 secs (several queries, updates and >inserts). > >All my tables are properly indexed using EXPLAIN and all the tables are >MyISAM types. I'm using MySQL v3.23.47 >All the processes are background processes. > >The problem: >Doing a write lock on the two main tables ensures that no clients can access >the tables while they are updated, but what about the clients who are in the >middle of a chain of queries from the main tables? > >Solutions? >A) >The process that updates the main tables can maintain a version number which >increases every time the tables are updated. The process WRITE locks the >main tables and thus freezez the many clients reading from them. >A client reads the version number from the tables before it starts to do its >many queries. When the client is finished processing the tables, it reads >the version numbers again and compares them. If they are not the same, the >process must have been freezed and must be restarted again. >The solution is fairly simple to implement, but the problem is the many >client programs taking up resources while they are frozen. > >Any suggestions?
Hi, Just a simple thought pattern: You could freeze clients UI's (message: "please hold on, database is being updated, press refresh to try again") and then wait that all SQL processes are finished (SHOW PROCESSLIST may help here), then flush, lock, update, unlock and open clients again. ------------------------- Pekka Saarinen http://photography-on-the.net ------------------------- --------------------------------------------------------------------- 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]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
