How does table locking work?

1

Hi,
I'v a big table that can be both be written by Human clients, and some server side scripts.
Often, when the server side script runs, my clients have lots of slowdowns with heavy beach balling.

But of course, the data written by the server side scripts are not for the same record (most of time its new records), but it's as if the whole table slows down.

What's going on.

2

It's been a while since I have refreshed my understanding about the locking behavior in FMP, but, per the last understanding that I had, what you are seeing makes sense to me.

Long ago, like in version 13 or 14, it used to be that, one person performing a write operation on a file would block others from even being able to read data. I recall those as tough times. Fortunately, major improvements have happened since then, but there are still some limitations.

The last I was aware:

One user performing a write operation (new record, edit record, delete record) would create a lock that would prevent any other user from doing a write operation at the same time. Fortunately, read operations are still possible while the write lock happens (this was a huge improvement).

The scope of the write-lock was at the FMP file level, so it did not matter if users were targeting different records, or even different tables within the same file -- as long as there were multiple write operations wanting to happen within the same FMP file, they would each have to wait their turn for one-at-a-time processing. The fact that the lock happens at the file level has sparked numerous discussions and strategies regarding the potential benefits of moving some tables, e.g., a log table, to their own separate file, so as to strategize how to work more efficiently with the locking behavior.

As I indicated, it has been some time since I have studied up on the locking behavior. If nothing has changed since I last understood it, then you may have an explanation for the behavior that you are seeing. If the locking behavior has changed since what I described above, I welcome anyone to chime in with a recent, or better, understanding.

Addendum: The locking described above is related to the action of writing data back to the DB. It is different from the "record locking" mechanism that allows different users to open different records for editing, but which prevents different users from opening the same record for editing.

2 Likes
3

Thanks a lot.
Wow FILE locking is crazy

1 Like
4

We'll see if anyone has any word about it being improved since File-level write locking, but I honestly don't recall hearing of such a change, and I do try to keep my ear to the ground...

1 Like
5

I recall discussion of the engineering issues involved in record locking in Claris webinars some time ago, perhaps 18 months or more. At that point in time, a record is locked when it is in use. They do not want to have two copies of the record out-of-synch. That is the core issue. They were discussing ways to make that more granular, e.g., field level locking, but there were a lot of issues to overcome.

The fact is that FileMaker client and server are very chatty. If the table being updated was nothing but data fields, numbers and text, then any slowdown would be minimal and associated with CPU and I/O on the server. However, if the table has summary fields, calculations, etc, then the new data may need to be incorporated. If there is a sort order, then new data may need to be sorted. There are a lot of scenarios in which the client may need to be aware of the changes being made on the server, and to update in real time.

2 Likes
6

Thanks Malcom,
The being edited records locking seesm fair to me, but what's not cool is if it locks the entire table.
If, just because someone i editing 1 record in a 10s of thousands records table, it locks the whole table that's a problem. That's sadly what I'm experiencing it seems

7

Here are a few relevant links - in particular, the parts that discuss the "sharing lock" feature, and the history that led up to it.

This is the last info on the locking strategy topic that I am aware of, and it's worth pointing out that it came out at the time of FMS 19.1 / 19.1.2.

https://community.claris.com/en/s/article/Performance-stability-and-reliability-improvements-in-Claris-FileMaker-Server-19-1-2

I've always been really grateful for the Soliant posts such as the ones linked above.

Also, I realize there could be some ambiguity around the terms "locking" and "record locking".

There's the "record lock" in the sense that one user is not allowed to edit a record that another user already has open -- it is "locked", and this sort of record locking happens at the record level. And thus users are able to open and edit records at the same time on different records.

But, that said, there is a different kind of locking (described in the articles linked above) that has to do with the actual writing back to the DB when those open records are committed at the server level. That is where there is a file level write lock exists that prevents the writes from happening simultaneously.

So, again, two types of locks:

  • One lock which prevents two users from opening the same record for editing at the same time, but which does allows different records to be opened by different users.

  • Another lock, which enforces that the actual write operations (that happen during the commit) occur one at a time. (Assuming that this has not changed since v. 19.1.2)

Apologies if my use of the term "lock" in my earlier post was too vague, and caused any confusion. I'll go to take a look at that post, and amend for clarity, as necessary.

HTH.

2 Likes