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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]