Hello. > Does this have anything to do with the variable: > > | tx_isolation | REPEATABLE-READ |
It seems it is. If you use InnoDB only due to speed issues, change the transaction isolating level to READ UNCOMMITED. See: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html This might be helpful as well: http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html http://dev.mysql.com/doc/refman/5.0/en/table-locking.html George Law wrote: > Hi All, > > I just had a question on selects on a innodb table. > > Looking on google, I find that there are different types of selects: > select .... IN SHARE MODE and FOR UPDATE > > I am administering a database with a fairly large innodb table. I am > running into > problems with one of my users insisting on using selects on a datestamp > field that is not indexed. Moreover, she is doing selects/updates like : > > select .... from .... where left(start_time,10)= '2006-01-10' > update xxx set ... where .... left(start_time,10)= '2006-01-10' > > I also cannot get through to her to use BEGIN/COMMIT on her updates. > > While her perl scripts are running, I am finding that I have imports > running in the background to "load data infile" into the same table she > is reading from/updating. These updates happen 6 times an hour. > Probably 1 in 6 import fails, presumably because one of her queries has > the table locked. > > Other times I have seen this happen when she is doing a bulk select and > importing into another table. > > What is the default mode for SELECT statements with INNODB table. I had > assumed that it would always allow for inserts to happen elsewhere in > the table (ie - SHARED mode). That was my big reason for using INNODB > for this table. It just doesn't seem right that the whole table seems > to get locked on the select. > > Does this have anything to do with the variable: > > | tx_isolation | REPEATABLE-READ | > > > Thanks! > > George Law > > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]