Novulo Record locks are one of the locks in Novulo.
They play an important part in the behaviour of every Novulo application.
Novulo Locks ensure that, when your current record is being edited (it has a lock), that all expression that refer to the record being locked, use the data of your editing process, rather than the database value.
Example
Imagine you have a page with two dropdowns:
- Country
- Postal code, filtered on this country
In this example, the country that’s stored in the database is Netherlands. Now, you’ll see the provinces of the Netherlands, from
{provinces, name, country.equals(parent.country)}
This will result into an SQL-query that’s referring to the country in the Database.
Now, when you press Edit, and change the country to Belgium, you want to see the provinces of Belgium. At this stage, Belgium is not stored in the database. The data handler will understand that now, it should not get the query from the database, but from the current session storage of your user.
This session storage is also relevant to temporarily store data. If you navigate away from the page you are editing, the data is still stored in the session until you log-out. This allows you to recover unsaved data, but can also slow down your application.
You will see an orange triangle in the right top of your screen:
When you click it, you see the record that’s in a lock:
This allows you to either discard the changes, or open the page again.
Behaviour in grids and why it can slow down
The application behaviour is to, unless specified differently, the data in the lock is always retrieved when this is available.
This also means that in grids, you might see unsaved data - as this is in the session storage. It’s good to be aware of this: if you edit a record, don’t save it but navigate back to the grid, you’ll see the unsaved data. When you log out and log in again, the unsaved data is gone. This will only be valid for your session - on another session (user, computer), database data is shown.
If you want to force database data to be shown, you can use the old() function, which exists on every record.
For example:
products.name will show the product name; if an unsaved record transaction exists, this will be shown. If not, the database value will be shown.
products.old().name will always show the product name as it’s in the database and it will ignore the session storage.
Impact of session storage on the database
When retrieving data in a grid, the application will take into account data in the session storage.
In a grid with only products and a name, the expression:
{products, <id, code, name>, true}
will result into the query:
SELECT
TOP 100 q0t1.[id],
q0t1.[name]
FROM
[products] AS q0t1
If records exist in the session, the query will become different. In this case, the record I’m editing has ID 96.
SELECT
TOP 100 q0t1.[id],
CASE
q0t1.[id]
WHEN 96 THEN 'Product name Changed, not saved'
ELSE q0t1.[name]
END,
q0t1.[id]
FROM
[products] AS q0t1
The specific part with CASE [id] WHEN (value) THEN shows that there’s a record transaction / record lock.
When will it become a performance issue?
The performance issues arise (mostly) when you are searching (for example, with column searches), you might get this query:
SELECT
TOP 100 q0t1.[id],
CASE
q0t1.[id]
WHEN 96 THEN 'Product name Changed, not saved'
ELSE q0t1.[name]
END,
q0t1.[id]
FROM
[products] AS q0t1
WHERE
CASE
q0t1.[id]
WHEN 96 THEN 'Product name Changed, not saved'
ELSE q0t1.[name]
END LIKE 'search text'
The CASE WHEN-set-up in searching, makes that the database won’t be able to use indexes anymore. As a result, SQL server will typically retrieve all data in the table (for that column).
This is not noticeable in tables with less than 1000 records, but will greatly increase server load when over.
Therefore:
- Try to eliminate searching when records are in a lock
- Instruct users to save or cancel records, and how to handle the triangle
- Use old() when you can’t avoid it