Database standards OR Am I doing this right? lazarus / 16-Databases ---------------------------------------------------------------------- wpflum · 2024-04-18 17:18 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 · 2024-04-18 20:50 Quote from: wpflum on April 18, 2024, 07:18:20 pmif 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CLIENT_FOUND_ROWS=1 ---------------------------------------------------------------------- egsuh · 2024-04-19 03:53 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 · 2024-04-19 04:16 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 · 2024-04-19 04:16 Quote from: egsuh on April 19, 2024, 05:53:03 am.. 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 · 2024-04-19 04:24 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 · 2024-04-19 05:21 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 · 2024-04-19 05:40 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 · 2024-04-19 05:52 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i BEGIN tranUPDATE tableA SET col1='aaa' WHERE id=1UPDATE tableB SET [DATE]=getdate() WHERE ida=1INSERT INTO history (ida, changes_date)SELECT 1, getdate()commit tranOf 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 · 2024-04-19 06:56 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 · 2024-04-19 08:38 @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 · 2024-04-19 10:02 Quote@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 · 2024-04-19 11:07 I use an additional column for this purpose. Example structure: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE testtab (  id NUMERIC IDENTITY(1,1),    name VARCHAR(50) NOT NULL,    qty DECIMAL(14,4) NOT NULL,    price DECIMAL(12,2) NOT NULL,    description VARCHAR(200) NULL,    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*/    CONSTRAINT pk_testtab PRIMARY KEY clustered (id ASC)) ---------------------------------------------------------------------- gidesa · 2024-04-19 12:37 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 · 2024-04-20 08:56 Quote from: TRon on April 19, 2024, 06:16:49 amCritical 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 ---------------------------------------------------------------------- MarkMLl · 2024-04-20 08:57 Quote from: cdbc on April 19, 2024, 06:24:05 amHi @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 I'd strongly recommend against trying to use file-based locking for this, particularly if there are other forms of locking available at the API or SQL level ("transaction isolation"). MarkMLl ---------------------------------------------------------------------- cdbc · 2024-04-20 09:17 Hi @MarkMLl: I agree, the best solution comes from the database itself. At least I assume that, they've put a lot more work and knowhow into the database-code, than I'll ever do with my client-code... That poat was only a sidenote to TRon's post... Regards Benny ---------------------------------------------------------------------- TRon · 2024-04-20 09:28 Quote from: cdbc on April 20, 2024, 11:17:40 amThat poat was only a sidenote to TRon's post... .. and one that I completely missed. My apologies. Yes, it is a good addition and I am aware that you can do it that way albeit better not in the context of TS's post (indeed better let the database engine/API take care of that for us). ---------------------------------------------------------------------- gidesa · 2024-04-20 12:16 The concurrent access to db from different users is one of the first and most important problem that a dbms has to resolve. So, it's no surprise that it's much better to use dbms concurrency mechanism (logical transaction, lock, isolation level, etc.), than handmade limited solutions. Initially there is an effort to understand that mechanism, yes, but it's well worth. Quote from: cdbc on April 20, 2024, 11:17:40 amI agree, the best solution comes from the database itself. At least I assume that, they've put a lot more work and knowhow into the database-code, than I'll ever do with my client-code... ---------------------------------------------------------------------- MarkMLl · 2024-04-22 08:22 Quote from: cdbc on April 20, 2024, 11:17:40 am@MarkMLl: I agree, the best solution comes from the database itself. At least I assume that, they've put a lot more work and knowhow into the database-code, than I'll ever do with my client-code... That poat was only a sidenote to TRon's post... I did tack on a couple of additional points but was in a rush to get out and it didn't get posted properly. The first is that while transaction isolation is the "correct" way to go, how to actually set it up is not very well standardised from the POV of a client API and one might find onesself having to use a text parameter: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     SQLTransaction1.Params.Clear;    scratch := '';    if ListBoxIsolation.ItemIndex >= 0 then      scratch := '''' +  Trim(ListBoxIsolation.Items[ListBoxIsolation.ItemIndex]) + '''';    if scratch <> '' then      SQLTransaction1.Params.Append('default_transaction_isolation=' + scratch);    SQLQuery1.Open;                     (* Remaining components auto-activated  *)  The second is that graphical database controls are fundamentally incompatible with long-running transactions: if there's e.g. a network glitch during the lifetime of a transaction the application program has no way of recovering. I explored that in conjunction with PostgreSQL at https://github.com/MarkMLl/testdb/tree/main, and since my findings were basically the same as those reported by somebody running a different backend I anticipate that they will be much the same for everything. That was, TBH, something I'd anticipated for a long time but hadn't actually experimented with. The problem is that SQL was originally a "mainframe thing", and the designers anticipated that it would be used by "middleware" rather than directly by a remote client application. MarkMLl