This table has two fields, Price and amount.
I am performing a query on this innodb table which Selects for update all records whose price is less than 100 (query 1).
Whilst this query is going on, I want to be able to insert or update a row in the table which has a price greather 100. Is this certain to work?
At the same time, I want to guarantee that any attempts to update or insert rows into the table, whose price is less than 100 will not happen until (query 1) is complete. Is this also certain?
If I want to perform ordinary select * from queries on this table whilst queries of type 1 are happening, and queries in question 2, will I be able to do these reads or will these reads have to wait until the locks are off? This has to be certain. edit: I would like these selects to be able to access any record in the table
Finally, are indexes crucial to get this working? Or will it work as is?
Many thanks.
If your table only has two fields (price and amount), this seems… bad. Surely there should be some sort of other identifier in there?
Yes. SELECT FOR UPDATE only locks the records it selected.
Maybe. Depending on your transaction isolation mode, MySQL may lock an index range to block inserts into the range that was scanned as part of your query.
This depends on the transaction isolation mode you use. If you are using a higher isolation mode, locking the rows for update will prevent them from being read, as your transaction may or may not end up making changes to them, depending on whether it is committed. See the MySQL manual's section on "SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads" for details.
Yes. The index range locking mentioned in part 2 depends on there being an index. Running unindexed queries is a bad idea, anyway — avoid it.