3 Replies Latest reply on Feb 6, 2012 7:56 AM by LSNOVER

    Need SQL advice

    fmpros

      I'm hoping someone may have some insight into this. I'm putting together a complex app and I use SQL heavily using the Dracoventions SQL Runner plugin. Every now and then I notice that an SQL statement pauses for a time and then displays a message stating that there is a record locked (usually when I have multiple windows open). Does anyone know the rules regarding SQL operation with record locking? Specifically, if I'm just retrieveing data through a SELECT statement and one of the records in the target set is in use by another user, will that trigger the SQL pause? I can understand it if I'm doing an UPDATE or DELETE but would the SELECT still pull the data from a record in use?

       

      I suppose there are ways of testing whether a record is locked in advance but the whole idea is to operate without having to get context sensitive. I need the confidence that a data set pulled is complete and/or don't want the big pauses and the exception thrown to the user. I know I can set the command to wait for idle time but that just throws in another unknown operational outcome.

       

      Thanks for any information,

       

      Bill O'Keefe

      Frenel Solutions

        • 1. Re: Need SQL advice
          Michael Frankel

          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.

           

          --

          Michael Frankel

          FileMaker Certified Developer

          Wizard Consulting Group

          Office: (818) 706-8877

          Skype-In: (818) 483-4686

          Skype: michaelfrankel

          Website: www.wizardconsultinggroup.com

          • 2. Re: Need SQL advice
            fmpros

            Thanks Michael,

             

            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,

             

            Bill O'Keefe

            Frenel Solutions

            • 3. Re: Need SQL advice
              LSNOVER

              Bill:

               

              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.

               

              Regards,

              Lee Snover