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]

Reply via email to