Puzzle: Creating an INDEX requires EXCLUSIVE use of the table, doesn’t it?


A coworker asked a question about some code. It creates a table with 2 fields, reopens the table SHARED, creates an index tag using the INDEX command, closes all, then tries to do the same thing, but this time it fails. Why?


 


CLEAR ALL


CLEAR


SET EXCLUSIVE OFF


CLOSE DATABASES all


CREATE TABLE foo (name1 c(10), name2 c(10))


TRY


      USE foo SHARED    && reopen the table shared


      ?”Creating index with exclusive=”,SET(“Exclusive”)


      INDEX on name1 TAG name1


      ?”Success creating index”


      CLOSE DATABASES all


      USE foo  SHARED   && change this to EXCLUSIVE to avoid the error


      ?”Creating index with exclusive=”,SET(“Exclusive”)


      INDEX on name2 TAG name2


CATCH TO oMsg


      ?”Error:”,oMsg.Message,oMsg.details,oMsg.LineContents


ENDTRY


 


This code produces the output:


 


 


Creating index with exclusive= OFF


Success creating index


Creating index with exclusive= OFF


Error: File must be opened exclusively.  INDEX on name2 TAG name2    


 


 


Is this a bug? Can you explain the behavior?


 

Comments (7)

  1. wOOdy says:

    That’s an easy one, and of course absolute logical, if you think about it…

    At the first INDEX command, there was no INDEX around, thus nobody else was able to use it, thus FoxPro can happily create it. As soon as there exists an index (the second time), there the possibility that others are using it and therefor you can’t change it anymore.

    Only slight problem with the first index is: After the index was created in shared mode, other instances aren’t aware of the new index, and therefor can’t update the index. For the user, who created the index it’s just a snapshot index, which reflects the state of the dbf at the time he indexed plus all of HIS changes, but he can’t see other changes reflected in the index. Therefor an immediate REINDEX should get issued, which of course would need an exclusive access anyway…

    IMHO, this behaviour is a followup of the old INDEX ON… TO IdxFile syntax, where you always have been able to create new "personal" indices without having exclusive access. With the addition of the CDX index, which gets added to the header, there needs to be exclusive access to a) modify the header, and b) get all other workstations updated with the new structure, which basically means close and reopen the app. Thus I tend to call this feature a bug, an oversight or infelicity (MS will surely label this "by Design" ;), because this will propably not change anymore)

  2. Fabio Lunardon says:

    This is a bug for structured cdx

    U1 : open the table with exclusive= OFF

    U2 : open the table with exclusive= OFF

    U1 : Creating index with exclusive= OFF

    U2 : APPEND BLANK

    * The index is corrupted

    U2 : close the table

    U2 : open the table and the corrupted index

    U2 : SELECT * FROM foo WHERE name1==”

    * _tally=0

    U2 : SELECT * FROM foo WHERE ”==name1

    * _tally=1

  3. Max Zambrano says:

    Apparently when opening the table shared immediately after creating it, VFP is not updating the header as it should, creating the anomaly.

    I tested it and was able to open the table from another machine, demonstrating it was shared and still able to create the index from the original computer.

    Then the cdx was not available for the second machine as it was exclusively opened in the first.

    It looks like a VFP bug to me.