Hi,
I have some questions regarding HANDLER statement usage:
http://dev.mysql.com/doc/refman/5.1/en/handler.html
-What is the cost associatied with opening/closing a HANDLER?
-Are there any consistency guarantees associated with the
HANDLER ... READ my_idx = (col_a_val,col_b_val,col_c_val) WHERE...
variant? E.g., if a tuple is inserted into a table, is such a subsequent HANDLER statement guaranteed to retrieve the inserted tuple (assuming that it matches it)?
-Does it matter if the HANDLER was already open on insertion? Do I get any additional guarantees after closing/reopening the HANDLER? Do I get any additional guarantees if the insertion and handler read are part of the same transaction?
I cannot suggest a way to replicate this but I believe that I have observed the following sequence of statements and results on an Inno table, MySQL 5.1.
I give some detail in case it matter. Say the table is (pkey1,pkey2,pkey3,value) and in the context of the same transaction we have:
HANDLER READ `PRIMARY`=(v1,v2) WHERE value>=1 OR value IS NULL
(No results, expected as no tuples match the query)
INSERT VALUES (v1,v2,v3,NULL)
HANDLER READ `PRIMARY`=(v1,v2) WHERE value>=1 OR value IS NULL
(Again no results, was expecting the inserted tuple)
Is the above situation something that can occasionally happen? Should it happen if the statements are part of the same transaction? I appreciate your help and insights.
Best,
Nikos