Hi Bill -
From what I know, record-locking in SQL takes place only when a record is being modified but the changes have not been committed.
However, in applying that same logic to FileMaker, other factors need to be taken into consideration. If I remember correctly (based on a presentation I saw by Todd Giest at the last DevCon), record-locking in FileMaker occurs:
1. When a record is on the screen and the user's cursor is in an editable field, and they have typed something into any field in that record.
2. When a script accesses a record and a set field statement is executed but the changes have not yet been committed.
3. In either of the previous two situations and there are related records in a portal on the same layout, I believe that those related records (or perhaps just the first related
record), are also locked.
Keep in mind that FileMaker defines a record as being locked if it is open. There is a calculation, Get (RecordOpenState), that will tell you the open state of an record. Anything other than 0 means the record is locked (or inaccessible in the case of a newly created but not committed record).
I hope that helps.
FileMaker Certified Developer
Wizard Consulting Group
Office: (818) 706-8877
Skype-In: (818) 483-4686
I have done a little testing on my own and found that SELECT's will pull data from locked records, with the same caveat as with "getting" data into variables to return (running a context sensitive script rather than SQL), you will not get changes that haven't been committed. With UPDATE's and DELETE's understandably, SQL will pause and throw the 301 error on locked records. INSERT's are no problem.
I haven't used the RecordOpenState (and will test); have you used it, best practice, have to be context sensitive, etc.?
Thanks for the comments,
I haven't used MySQL alot, but I know with Microsoft SQL server you can get locks by default with Selects depending on how things are configured. Also, if you have triggers and stored procedures, keep in mind you may be editing records at times where you are not particularly aware from what is going on in the interface. In Oracle and most other SQL dbs, you can also do a "Select for Update", which will lock records. Filemaker does not give you the ability to do this with ESS, but if you have other non-Filemaker applications working on the data, this can catch your FM app.