How to avoid multi user overwrite each other in Base

[VillageHubertChen]

I have successfully configure LibreOffice Base in a Multi User Scenario and all works well, except for protection or warning that two user could edit the same record and overwrite each other changes.

This is my setup:

Database: MySQL Community Edition
LibreOffice Base: v5.01
Connection: JDBC (32 bit JRE)
OS: Windows 7 64 Bit
Two PCs:

• First PC runs server and LibreOffice Base to create one user access
• Second PC runs LibreOffice Base to create second user access

Test Scenario

1. First PC: Open Base, Open Form, Go to first record of first table, enter data into field but do not commit
2. Second PC: Open Base, Open Form, Go to first record of first table, enter data into field but do not commit
3. No warning give. In other databases I used a warning was given as soon as I try to edit the same record that somebody else is editing at the same time
4. Random saving on both clients. No warning is every given. Save is executed. A refresh on the other client shows the saved record of the alternate client.

Question
How to I solve the problem / change configuration so it is not possible for two clients to edit the same record without any warning?

Any help is appreciated,

Thanks in advance,

Hubert

[BlueBike]

This is an interesting question but I have 2 questions, or may be I just do not understand 100%.
Did you definitely observed a data corruption or lost update or something like that?
I understand you are using Mysql as a backend DB, if the table in charge is in the mysql backend DB then this is a Mysql question.
Further it would be nice to read what you are expecting, what should have happened.

If you are indeed using Mysql as the table backend DB you might have a look at the locking mechanism see:
mysql.com/doc/en/Internal-locking.html. Further you should realise that not each RDBMs have the same locking mechanism, but a self respecting RDBMS should guarantee the data integrity, but how exactly might differ.

[doug]

This seems like something LO should handle natively, in an ideal world. Since you say it does not, you can create a set of macros that take either of two approaches:

Approach One: Manual Row Locks

It is possible to impose row locks manually on some MySQL tables. For LO purposes, you would run a macro probably on a Form level event that displays the current record, because you would need to freeze the record as soon as you load it. This will result in a larger number of locks than is desired, and a risk of unnecessary conflict, to ensure there are no changes to a record after it is displayed. The macro would run a SQL query in the background that imposes the lock. I am not going to give an example, because I have not personally tried this, but it appears that if you pull records using a background select query that includes “LOCK IN SHARE MODE” at the end, then the rows will remain locked until you commit or rollback a transaction. See discussion here and here. If you can get that to work, that probably would be the most artful solution.

Approach Two: Comparing update timestamps

On all my tables I maintain a column auto-populated by the database engine with the datetime of the last row update. You can load this column on a hidden control on the form, and when you want to update, before doing so use a macro to run a SQL SELECT query on the present value of the datetime column (even, run it with the “LOCK IN SHARED MODE” command above). Then, compare it to the datetime on your form control. If the datetimes match, allow the commit to proceed. If they are mismatched, send the user into a conflict-resolution dialog.

For running SQL queries in the background, see this answer.