Concurrent Database Access & Conflict Resolution lazarus / 16-Databases ---------------------------------------------------------------------- SteveMilner · 2012-12-12 13:11 Hello. I am new to both this forum and Free Pascal & Lazarus, but I am not new to Pascal Programming and have in the past been the architect and significant developer of an EPoS system for 800 lanes (180+ Stores) for a £200M turnover retailer in the UK. I have been browsing the database components and I haven't been able to see any obvious method of resolving conflict when writing to a database. Let me give you an example. Using a TSQLQuery I retrieve the stock figure for an item I want to sell. I have an application that sells the item and I decrement the stock field held in the TSQLQuery and then I APPLYUPDATES to commit the change. However, if between the read and the APPLYUPDATES another application has altered the stock figure is there a way I can recognise there has been a change in the underlying data in the database between the read and APPLYUPDATES. I've clearly not been up to my usual standards of google foo, as I've not been able to find anything that points to the solution, but if someone could post a link that would be of benefit. I am planning on using a Firebird database. Regards, Steve ---------------------------------------------------------------------- Leledumbo · 2012-12-12 13:35 The solution is standard and is language independent: use mutex via critical section. Some DBMS already has the feature built-in, so you might want to consider reading your DBMS documentation first. ---------------------------------------------------------------------- taazz · 2012-12-12 14:09 1) which DB components do you use? 2) If I remember correctly all query classes has a property named upda]"]>Blockedde where you can select how the update is to be executed with 3 options a) upWhereKeyOnly this will fail if the key fields have been changed in the db backend before the upplyupdates is called. b)upWhereChanged this should only check the fields that have been changed only including the key fields I think. c) upWhereAll all fields are checked for changes regardless of their state. in all cases if the values do not much an exception is raised which you can catch and react as needed. ---------------------------------------------------------------------- SteveMilner · 2012-12-12 14:46 Quote from: taazz on December 12, 2012, 03:09:09 pm1) which DB components do you use? 2) If I remember correctly all query classes has a property named upda]"]>Blockedde where you can select how the update is to be executed with 3 options a) upWhereKeyOnly this will fail if the key fields have been changed in the db backend before the upplyupdates is called. b)upWhereChanged this should only check the fields that have been changed only including the key fields I think. c) upWhereAll all fields are checked for changes regardless of their state. in all cases if the values do not much an exception is raised which you can catch and react as needed. That's great. UpWhereChanged seems to be the correct way forward for me. Are there any examples of the exceptions that ApplyUpdates will raise? I could also do with understanding how deal with the exception. Is it possible within the exception handling to be able to: 1) Identify which of the updates have failed within a dataset. 2) Examine the initial, modified and latest states of the data. 3) Change the modified data and re-apply. So imagine a transaction that was updating the the stock fields of several items, one of which has had it's stock field modified by a different user. You would  need to know which of the items had cause the problem. Imagine the initial stock figure you read for the item was 10, you were selling one, so the modified stock figure you wish to write would be 9, but someone had already changed that stock figure to 5. You could then use the initial read of 10 and the modified data of 9 to create a delta to apply of -1. Using the latest stock figure of 5, you could then add the delta and get a new modified figure of 4. Changing the initially read data, for the latest read data it would then be possible to execute the ApplyUpdates again, which should then set the stock figure to 4, correctly. As a previous Kylix user I had access to these 3 datasets, initial, modified and latest and the ability to modify them accordingly. I might add, it was broken on kylix and I had to re-write some to the code to fix it, but nonetheless it was possible. If there isn't access to these datasets I'll have to write code to ensure I have access to the datasets needed to handle the conflict, but if there's an easier way, I'll take it. Regards, Steve ---------------------------------------------------------------------- Zoran · 2012-12-12 15:37 If you use correct transaction isolation level (TIL), you will not be able to apply updates if another transaction changed the record after our transaction had started - exception will be raised when you try to ApplyUpdates. Then you should catch the exception and respond appropriately (e.g. by informing the user that another instance of the application changed the record). SQL standard describes four transaction isolation levels (see here). Firebird has three. You should try to set the appropriate one and test the behaviour with two instances of the application running at the same time. I'd say you should set TIL to "Serializable" (in Firebird, it's "Snapshot table stability"). How to set this TIL? It depends on which library you use to access database: 1. SQLdb - you should add line "isc_tpb_consistency" to TSQLTransaction.Parameters property (see here). 2. Zeos - set TZConnection.TransactionIsolationLevel property to tiSerializable (see here and find "Transaction Isolation Levels of TZConnection" in the document). 3. IBX - I coudn't find it documented, but I guess adding "isc_tpb_consistency" to TIBTransaction.Params property (you can try and you can ask on their site: http://www.mwasoftware.co.uk/ibx). You can also try with "snapshot" ("repeteable read") TIL. Try, test and see what is the TIL you need... ---------------------------------------------------------------------- taazz · 2012-12-12 16:00 Quote from: SteveMilner on December 12, 2012, 03:46:45 pm So imagine a transaction that was updating the the stock fields of several items, one of which has had it's stock field modified by a different user. You would  need to know which of the items had cause the problem. Imagine the initial stock figure you read for the item was 10, you were selling one, so the modified stock figure you wish to write would be 9, but someone had already changed that stock figure to 5. You could then use the initial read of 10 and the modified data of 9 to create a delta to apply of -1. Using the latest stock figure of 5, you could then add the delta and get a new modified figure of 4. Changing the initially read data, for the latest read data it would then be possible to execute the ApplyUpdates again, which should then set the stock figure to 4, correctly. I haven't used the databased components directly for a few years now so everything is a bit fuzzy I need to create a demo and test the various cases before answering the exception question of yours, since my free time is very limited at this time and I do not know when it will be possible for me to spend some time on it I'll just give you an alternative instead. On the above cases I usually write a custom updatesql (now days this is auto generated and executed outside the existing dataset components) as Update mytable set field1 = :field1, field2 = Field2 -(:Oldfield2 - :Field2) field3 = :field3 etc... This way I do not set the database value to a specific value, instead it uses what ever value is in the database and adds/subtracts the delta value from it eliminating the need for extra check's in the client app making it lighter, of course there is the need to guard against the limits of the field which can be a check on the database field it self instead of client based code as well. I think that the above can be used directly on the existing query components but I need to check farther I'll try to give a concrete answer as soon as possible. Regards Jo. ---------------------------------------------------------------------- otorres · 2013-01-11 04:44 The better way to manage this situation is using stored procedures. Regards.