Database standards OR Am I doing this right?

[wpflum]

I've never worked database objects where the object is connected to an SQL server, Online Azure in this case, is there a standard/recommended way to handle writing back to a query when there are other users also writing back to the same table and possibly the same record?  I'm connecting using Zeos, a ZConnection, and then using queries to get the data for a dbgrid and when i write back to the table i'm using a zquery with an update.  This works correctly but when I try having another copy of the program open I can read fine but if I try and update the record I get a 0 record updated error, I trapped that and  tried to just reread the record and then update the difference but something isn't quit working the way I think it should. Writting two seperate records seems to be working, it's just using the same one is the problem, and that could happen since this is a Check in program designed to let the user enter how may people for a company show up and it's possible to have someone from the same company check in at different entrances.

[dseligo]

if I try and update the record I get a 0 record updated error

I don't know about Azure, but for this error try to put this in Properties of ZConnection:

Code: Text  [Select][+][-]

1. CLIENT_FOUND_ROWS=1

[egsuh]

I do not know very much about this issue. AFAIK you should take care of transaction, and might have to use critical sections, so that other uses may not access the same record whey you are doing something with a record. 

https://www.freepascal.org/docs-html/prog/progse45.html

[cdbc]

Hi
It looks like a locking issue, so if the second /offender/ is *only* reading, it should work.
Can you, by any chance, make sure, that when you only want to read data, the 'ZQuery' is marked "ReadOnly" e.g.: via a property?!? _Live_ so to speak...
Regards Benny

[TRon]

.. and might have to use critical sections, so that other uses may not access the same record whey you are doing something with a record. 

Critical sections apply to code in the same executable and usually when using multiple threads.

[cdbc]

Hi
@TRon: Primoz Gabrielski once wrote an interesting article in "The Delphi Magazine", about 'File-based Locking'.
Would have to sift through all the old magazines to find it, though 
Regards Benny

[paweld]

It is normal that you can't write one record in two separate queries at the same time.
In general, the idea is that you should not keep the transactions open for a long time, but immediately after adding/modifying/deleting data close the transaction, then the record lock will be released and the second job will perform another data modification without any problem.
You can also set in MSSQL the amount of time the queries will wait for the lock to be released (LOCK_TIMEOUT https://learn.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql?view=sql-server-ver16), then if two programs call UPDATE of the same record, the application that did it second will wait the specified time, and if still the record locked then return an error.
But to start with, check if the database has indexes you can use. Because the lack of suitable indexes can strongly increase the execution time of each query.
And remember that each SELECT also waits for the release of the lock on the records it has to retrieve, but here you can use the WITH(NOLOCK) table hint ( https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/ ), the use of which will not wait for the release of locks, but will retrieve the data immediately, and for the locked records the state of the data will be from before the transaction.

[cdbc]

Hi
I seem to remember, that Zeos uses (can use) 'automatic transactions', so he might run into a *snafu* there...
The /Select-hint/ is kind of what I was thinking about...
Regards Benny

[paweld]

The Zeos as of version 8 has a TZTransaction component. But also in SQL itself you can combine several queries into one, for example:

Code: SQL  [Select][+][-]

1. BEGIN tran
2. UPDATE tableA SET col1='aaa' WHERE id=1
3. UPDATE tableB SET [DATE]=getdate() WHERE ida=1
4. INSERT INTO history (ida, changes_date)
5. SELECT 1, getdate()
6. commit tran

Of course, the query can be more compiled and have some additional functions/procedures which can significantly increase the transaction time.   
   
In addition, MSSQL itself runs implicit transaction queries if you do, for example, UPDATE without explicitly starting a transaction

[egsuh]

I think the program should be designed not to try to edit the same record and field by two or more users simultaneously. Let's assume that user A and user B have opened the same record, and they change the same record and update. Even after user A updated the record and committed, user B is looking at the old data (because it is under different transaction), which is before changed by A. And if the user B update it again, then A's modification would be lost. This is nonsense. So, once A opened a record, B should not permitted to view or at least change the record. If two or more users HAVE to access the same data and modify them (e.g. one person edits unit cost for a product, while another person edit production volume for sales), the two fields should be in different tables. Users can see the whole record combined, but do not edit the same field at the same time.

[paweld]

@egsuh: Generally, it should be the case that a record should have a semaphore set to inform other users that it is being edited.
But without information about the specific case, it is difficult to say anything.

[egsuh]

@egsuh: Generally, it should be the case that a record should have a semaphore set to inform other users that it is being edited.
But without information about the specific case, it is difficult to say anything.

@paweld:

Thank you very much for kind advice. I'm not a full-time programmer and do not know much about these basic facts^^. Just from logical reasoning. Are the semaphores done somehow by the DB server or should users prepare them?

[paweld]

I use an additional column for this purpose. Example structure:

Code: SQL  [Select][+][-]

1. CREATE TABLE testtab (
2.   id NUMERIC IDENTITY(1,1),
3.     name VARCHAR(50) NOT NULL,
4.     qty DECIMAL(14,4) NOT NULL,
5.     price DECIMAL(12,2) NOT NULL,
6.     description VARCHAR(200) NULL,
7.     blocked INT NULL, /*is null then if is null then the record is not blocked (not edited) by any user. If it is edited then the field contains the id of the user who is blocking this record*/
8.     CONSTRAINT pk_testtab PRIMARY KEY clustered (id ASC)
9. )

[gidesa]

Hello, Ms Sql Server, as many dbms, has different types of lock: at row level, at page level, at table level, etc.
The isolation level defines the extension of lock.
For example, with "Serializable" level the db engine locks entirely all tables involved in the logic transaction, so that a second user cannot never change data managed by the first user, until the end of first transaction.
See detailed (and long) description: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16
And you have to understand the concept of logic transaction. Simplifying, a transaction contains all db (Zeos) instructions comprised between the  Begin transaction and Commiy (or Rollback if you want to cancel all updates).
In short, the TZConnection component has a property TransactionIsolationLevel that you can set to the 4 possible values, as descripted in previous link.
From another discussion: https://zeoslib.sourceforge.io/viewtopic.php?t=2644 a possible sequence of Zeos instructions:
//At connection:
TransactionIsolationLevel := tiNone;

//At StartTransaction:
TransactionIsolationLevel := tiReadCommited;

// do all select, update, delete, .... on various tables

//At Commit, that is when confirming the updates
Commit;
TransactionIsolationLevel := tiNone;

//Or, at Rollback, that is when you want to cancel the updates,
// for example in case of exception
RollBack;
TransactionIsolationLevel := tiNone;

[MarkMLl]

Critical sections apply to code in the same executable and usually when using multiple threads.

In fact only to multiple threads, they don't prevent a thread reentering the same function.

MarkMLl