Why You Need (More Than) Locks: Ensuring Consistency with Transactions in Your No-Code Applications

TL;DR

Consistency is vital in no-code development for user trust. Many platforms struggle with data reliability due to locking issues. Momen.app uses PostgreSQL’s Multi-Version Concurrency Control (MVCC) to ensure data integrity, allowing developers to focus on building features without worrying about consistency problems.

Why Consistency Matters in No-Code Development

No-code development is often associated with feature delivery, problem solving and quick realization of business value. And it should be, after all, time-to-market, is probably one of the most important reasons one would choose to use no-code in the first place. But underneath every app lies one critical need: consistent, reliable data. Users’ trust can quickly be eroded when they observe inconsistent, illogical or missing data.

Most no-code platforms sacrifice consistency in the name of more flexibility and ease of development. Then no-code developers try to get some of that consistency and predictability back using homebrewed “locks”—mechanisms that stop multiple processes from accessing the same data at the same time. While this sounds like a great solution, it turns out locks alone aren’t enough to ensure data consistency. In this article, we’ll explore why, as a no-code developer, you need more than locks to keep your data safe, and how by properly exposing abstractions PostgreSQL (a popular database) provides, Momen.app ensures the right level of protection without extra work on your part.

What are Locks?

At its core, a lock is a simple concept: it prevents two processes (or users) from accessing (reading or writing) the same piece of data simultaneously. Think of it like locking a door—you have exclusive control over the room while the door is locked, and no one else can come in until you’re done. In computer science lingo, this is also known as a “mutex”.

Fundamentally, locks are not a database idea, but they are often used in databases. When used properly, it can eliminate a lot of data consistency issues. Though in complex applications, relying on locks alone can still lead to data inconsistencies, inefficiencies, or even errors.

When Locks Work as Intended

Locks work when their functionality is limited to mutual exclusion alone. For example, inventory management.

1. Obtain a mutex on a row that represents a particular SKU
2. Check the remaining inventory represented as a field in that row, ensuring it is larger than the number to deduct
3. Subtract remaining inventory by the number of items to be sold
4. Update the row with new inventory
5. Release the mutex

In this example, as long as ALL access in EVERY part of your application to the SKU table follows this sequence of action, it is reasonable to expect consistency to be maintained.

When Locks Fall Short: Common Issues You Might Face

However, even with that same inventory management example, there are plenty of potential pitfalls.

1. Partial Isolation: What if not “ALL” access was protected by the same mutex (you forgot to follow the strict protocol somewhere in your application)? Now you may have two pieces of logic running simultaneously (called threads) both seeing inventory for the same SKU being 1, and both try to deduct 1 from it.
2. Deadlocks: Deadlocks happen when two users try to access resources in a circular way—each waiting for the other to finish. Imagine Thread A tries to lock SKU 1 then SKU 2, and Thread B tries to lock SKU 2 and then 1. Well now they are both stuck waiting for the other to finish, but that will never happen. In addition to prevention, you need to build a mechanism to detect deadlocks in order to break them.
3. Granularity Issues: Locking too much or too little can also be a problem. If you lock an entire table when only modifying a single row, other users have to wait unnecessarily, significantly reducing the system’s throughput. In the worst case, 99.99% reduction is not unheard of. Also, do you lock for both read and write or just write?
   4.** Lack of Atomicity**: What if half way through the sequence of actions that the system is supposed to perform, something unexpected arises? That “something” can range from as drastic as power outage to as minor as a logical bug. Now what do you do with the rows you have already modified? Do you have to manually build out a rollback mechanism that handles unexpected exits at every stage of every sequence? That would be both tedious and error-prone.
4. Lack of Constraints: Business logic sometimes dedictates that a particular field’s value must be unique amongst all rows in a table (unique constraint), or a particular field’s value can only be one of the values in a field in a different table (foreign key constraint). Imagine an automatic SKU entry process where packages must be scanned individually and recorded. You may have multiple scanners pointing at a conveyor belt from different angles to capture the barcode written on the package regardless of the orientation of the package, which means potentially more than one scanner will be reading the barcode on that package simultaneously. Most no-coders will implement this as a two step process: check for the existence of that SKU in the database, if it does not exist, then write it in. Locks on a particular row do not work here because there is no row to lock. So you may end up with the two scanners simultaneously think that package is not yet recorded according to the “check” step, and then both writing that value into the table. Now you have two rows in the table representing the same SKU, a breakdown of logical unique constraint. Similar, if not more insidious inconsistencies can arise with the breakdown of foreign key constraints.

How Momen.app Address These Issues

Fortunately, Momen.app properly leverages PostgreSQL, which employs Transactional MVCC (Multi-Version Concurrency Control), a more advanced approach to managing data consistency. MVCC lets multiple users read and write data simultaneously without locking the whole system by maintaining multiple versions of the data for reads and writes instead of using locks. By exposing the transactional MVCC semantics in actionflow / batch mutations and letting users directly / indirectly define database-native constraints, Momen.app solves many of the shortfalls of no-coders homebrewing lock mechanisms.

• Partial Isolation: Because MVCC is the default behavior, within the actionflows (which run entirely on the backend and are transactional) there is no chance of “forgetting” to lock something.
• Deadlocks: While MVCC can’t eliminate all deadlocks, it significantly reduces the need for locking by allowing transactions to operate on different versions of the data. Also PostgreSQL has deadlock detection / abort mechanisms built-in, whose reliability has stood the test of decades of production use.
• Granularity Issues: MVCC avoids the need to lock entire tables by letting each transaction work on its own version of the data, preventing unnecessary delays while ensuring the data remains consistent, giving you best of both worlds (throughput AND consistency)
• Lack of Atomicity: Operations are all-or-nothing. Either everything in a transaction happens, or nothing happens, avoiding partial updates. This again is provided out of the box, removing the burden of implementing your own rollback mechanism.
• Lack of Constraints: Before any other threads can see your modifications (committing), all constraints such as unique / foreign key are checked, ensuring they are always honored. In the scenario where two barcode scanners might read the same package and both attempt to write the same SKU simultaneously, in momen, the “two identical SKU” problem can be entirely avoided entirely by performing INSERT on unique conflict (barcode) DO NOTHING. So you can rest assured that your SKU table always contains unique SKUs!

Focus on Building, Let Us Handle Consistency

As a no-code developer using Momen.app, you can focus on building the best features for your users while trusting that the underlying systems are handling the tough problems of data consistency. With Momen.app leveraging PostgreSQL’s advanced transactional model and MVCC, you’re protected from partial isolation, deadlocks, excessive-locking-induced bad performance, partial updates and race-conditions-induced breakdown of logical constraints—so you can keep creating.