Lock all edits whilst query is running ? (1 Viewer)

GK in the UK

Registered User.
Local time
Today, 20:10
Joined
Dec 20, 2017
Messages
274


I've got a form view which takes a second or two to populate and has to make three passes on the same table to get a result. I need to lock out all users for the period that the union query is running or the result may not be valid. I could read data in Pass 1 and if a user then edits something the result in Pass 2 could be inconsistent with Pass 1. Is there an Access way of doing this ? I don't suppose Access is clever enough to optimise the query so it gets the result of all 3 queries in one read ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
Hi. That is a good question. However, I don't understand the part about an edit during the query execution will produce an invalid result. If you were able to lock the tables and get the result of your queries, what happens if the user then edits the table after? Wouldn't the result still be different than what it would have been after the edit?
 

GK in the UK

Registered User.
Local time
Today, 20:10
Joined
Dec 20, 2017
Messages
274
Hi, Thanks, yes you are right, if we edit after the result is run then it's out of date.

But, at the moment of getting the result, I need it to be consistent (if possible, or I would have to have a caveat about possible invalid results. This is an on-the-fly result, if you like).

Each record has net value and vat value, expense code, sales or purchase code (simplified but that's the gist of it)
Query 1 reads every record and groups by expense code.
Query 2 reads every record that has VAT and groups by sales or purchase code
Query 3 reads every record, sums net and vat and groups into debtors or creditors.
Query 4 unions the first 3 and displays the result.

If a user edits a record after Query 1 has run, and changes the value of something, the VAT value, and the Debtor/Creditor value will change. So the result isn't consistent with itself.
 

Isaac

Lifelong Learner
Local time
Today, 13:10
Joined
Mar 14, 2017
Messages
8,738
I'm kind of with @theDBguy on my thinking here, too.

If the following occurs:
  1. You successfully lock everyone out from making edits
  2. You run the Union query
  3. The results are what they are, but the results will be different next time it's run, because someone made an edit immediately after the lock-out period (which they had no idea was happening anyway)
Or if the following occurs:
  1. You don't lock anyone out of making edits.
  2. The Union query starts running, taking what is already committed to disk
  3. The results are what they are, and the results will be different next time, because someone started to edit a record after the Union query had picked up whatever committed records it picks up (but they had no idea that was happening anyway).
...I don't see that as any meaningful difference.

Unless you're talking about, like, 10 minutes of processing, AND, during those 10 minutes, people are communicating and they are aware that during minute #3 someone made a change, AND they expect that change to show up at the end--then I can understand.

One way to handle that would be to create a timestamp when the Union query first starts running (or should I say, immediately prior) - then return the results (form, report, whatever you are doing) to the end users with: "Valid AS-OF ____________, Last Edit Included: By John Doe, at __________"

Just give them enough information and the interpretation will be forced correct.
 

GK in the UK

Registered User.
Local time
Today, 20:10
Joined
Dec 20, 2017
Messages
274
Well, my records are timestamped for edit time but it won't work. The key thing here, isn't so much that it's a few minutes out of date, but that it's not consistent with itself.

If a record has 100 net and 20 VAT, I need to fetch those values on all 3 passes for it to be consistent. If I fetch 100 on pass 1, then someone edits that record to 200 and 40 VAT, I can't get the original values of 20 and 120. All 3 queries have different joins on different supporting tables.

My application has 'committed' transactions and 'pending'. If I query on 'committed' it's not an issue, they're not editable. Currently when the user checks 'include pending' I pop up a warning stating the result may be inconsistent. So I'm thinking that will have to stay.
 

Isaac

Lifelong Learner
Local time
Today, 13:10
Joined
Mar 14, 2017
Messages
8,738
what is meant by all 3 passes - passes
 

GK in the UK

Registered User.
Local time
Today, 20:10
Joined
Dec 20, 2017
Messages
274
Sorry, queries. I think of it as 3 passes of SELECT on the same table, one after the other. 3 queries plus a union.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
Sorry, queries. I think of it as 3 passes of SELECT on the same table, one after the other. 3 queries plus a union.
Hi. Are we talking about three SELECT queries here? If so, then I hope you're not actually running them one at a time before running the UNION query, because running the UNION query will simply run them again.

So, all you really have to do is run the UNION query.

Also, are you asking this question because the issue you described happened before? Meaning, someone (a user) was quick enough to edit something in the table that affected the result of the UNION query? If not, why are you worrying about it?

Just curious...
 

GK in the UK

Registered User.
Local time
Today, 20:10
Joined
Dec 20, 2017
Messages
274
Yes I have a union query which unions the 3 select queries which feeds a tree view. I'm not running the select queries separately. Has it ever been an issue ? Well, no, not that I'm aware of. And I agree it's a really tight timescale for something to happen, maybe I shouldn't give it a second thought. The whole thing runs in less than 2 seconds and there's a lot going on to populate this form. But if we accept that it's a possibility that a record *might* change, I've got to say so, because someone will add it up one day and say hey, this balance sheet - doesn't balance. Which it won't, if we get an edit on a 'pending' transaction, in between the selects in the union query.

Really my question started out, can I lock users out for a short period whilst the union query runs, and it seems not.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
Really my question started out, can I lock users out for a short period whilst the union query runs, and it seems not.
I wouldn't give up just yet; however, the answer may not be worth it. Let's wait and see if someone else has a better idea.

That is why I said this was a good question in the first place. I have an idea what you could do, but I don't know if it's a good approach. For example, you can kick users out of the BE, but that would be too drastic.

Still though, I don't get the comment about someone adding up the balance sheet "someday." Why can't they just run the UNION query again? If table data are getting updated all the time, I hope you're not "storing" the result of the UNION query for posterity, because there is no way to verify it, since the tables are continually updated.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 13:10
Joined
Mar 14, 2017
Messages
8,738
I wouldn't give up just yet; however, the answer may not be worth it. Let's wait and see if someone has a better idea.

That is why I said this was a good question in the first place. I have an idea what you could do, but I don't know if it's a good approach. For example, you can kick users out of the BE, but that would be too drastic.

Still though, I don't get the comment about someone adding up the balance sheet "someday." Why can't they just run the UNION query again? If table data are getting updated all the time, I hope you're not "storing" the result of the UNION query for posterity, because there is no way to verify it, since the tables are continually updated.
Did you really say Two Seconds though? I mean, every option I can even begin to imagine would take at least the full 2 seconds just to implement! You might actually end up adding more confusion that way ...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:10
Joined
Feb 28, 2001
Messages
27,001
There is not a database system in the world that wouldn't have the exact same problem. The ANSI SQL 92 standard allows updates to intervene between the runs of the three singles vs. the UNION. Any DB that follows the SQL 88 or SQL 92 standard could give you changes between the initial and final runs.

If you can run a UNION query that uses the three individual tables, there is a subtle implication that they are related tables. How close to each other are they in layout? OR does each query use data from the previous query as its input? I'm trying to imagine the data flow here because it may be crucial to your question.

And the answer to your original question is that if you store the UNION query as a named query, you can easily change its lock properties to PESSIMISTIC - although that would cause errors with the OTHER users if any fell into the mix. THEY would get record locking errors, which could be equally as bad for them. You are not running in a vacuum, and your actions affect them as much as their actions affect you. There's a sort of "Heisenberg" principle here in that the act of measurement is rarely silent or perfectly isolated.

The other approach is that if you have dates and times on your transactions, isolate them with a WHERE clause that is "all times earlier than X" - and then you make your report "consistent as of time X."
 

Isaac

Lifelong Learner
Local time
Today, 13:10
Joined
Mar 14, 2017
Messages
8,738
Doc inspired me to throw in one more thing I just thought of. Any chance the back end is SQL Server? If so, many more additional options (as well as many more challenging things to study) come into play regarding the possibility of transactions, acquiring locks, etc. At the very brute-est, you could perform a specific action on the tables that might be selected-from by the users, such an action as would acquire an exclusive lock or block deliberately. Then proceed, then release the lock. But I'm probably going overboard.
 

GK in the UK

Registered User.
Local time
Today, 20:10
Joined
Dec 20, 2017
Messages
274
Dbuy, The user won't necessarily know that the result is wrong, unless they do add it up. They could run it again, but how do they know that the report is still flawed ?

To take the most trivial example (Sorry you may already have got this):

A new business is adding invoices continuously. The report is requested. There are invoice in the table, totalling £10000 plus £2000 in VAT.

The first select query runs to get the Sales analysis. We get the result, Sales of Widgets, £10000. The VAT content is saved in the same records, £2000
Another invoice is added.
The second query runs to get the VAT. Now we have £11000 of sales and £2200 of VAT saved in the table.
We get VAT £2200 returned
Another invoice is added.
The third query runs to get the debtors, by summing the net and VAT values. Now we have £12000 of sales and £2400 of VAT saved in the table.
We get £14400 returned as debtors total.

It might not be just new transactions that are being added, the existing ones might be edited. All this could happen in two seconds, couldn't it?

So the user is presented with the result
You generated sales of widgets, £10000
You charged VAT of £2200 and owe the taxman £2200
You are owed by customers £14400.
And the user is going to say, at the moment in time when that report was presented:
how is that I'm owed £14400 when I generated sales of only £10000 and charged VAT of £2200 ?
how is that I owe the taxman £2200 when the VAT on sales of £10000 is only £2000 ?
On big numbers, they may not realise but I think they expect to see a consistent result, or we disclose that it might not be.

Currently I pop up a warning saying that the results may be inconsistent, if they choose to report on 'pending' transactions. Which is sort of OK if there's no better way.

Really interesting to hear everyone's thoughts about this. Doc_Man, thank you, the timestamps would provide a cut-off for new records but wouldn't stop existing ones from being edited. I'm pretty sure the queries can't be combined for a one-hit read, because of the relationships. I've wondered if it's a possibility but it's definitely beyond my skill level.

It's an Access BE.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
Dbuy, The user won't necessarily know that the result is wrong, unless they do add it up. They could run it again, but how do they know that the report is still flawed ?

To take the most trivial example (Sorry you may already have got this):

A new business is adding invoices continuously. The report is requested. There are invoice in the table, totalling £10000 plus £2000 in VAT.

The first select query runs to get the Sales analysis. We get the result, Sales of Widgets, £10000. The VAT content is saved in the same records, £2000
Another invoice is added.
The second query runs to get the VAT. Now we have £11000 of sales and £2200 of VAT saved in the table.
We get VAT £2200 returned
Another invoice is added.
The third query runs to get the debtors, by summing the net and VAT values. Now we have £12000 of sales and £2400 of VAT saved in the table.
We get £14400 returned as debtors total.

It might not be just new transactions that are being added, the existing ones might be edited. All this could happen in two seconds, couldn't it?

So the user is presented with the result
You generated sales of widgets, £10000
You charged VAT of £2200 and owe the taxman £2200
You are owed by customers £14400.
And the user is going to say, at the moment in time when that report was presented:
how is that I'm owed £14400 when I generated sales of only £10000 and charged VAT of £2200 ?
how is that I owe the taxman £2200 when the VAT on sales of £10000 is only £2000 ?
On big numbers, they may not realise but I think they expect to see a consistent result, or we disclose that it might not be.

Currently I pop up a warning saying that the results may be inconsistent, if they choose to report on 'pending' transactions. Which is sort of OK if there's no better way.

Really interesting to hear everyone's thoughts about this. Doc_Man, thank you, the timestamps would provide a cut-off for new records but wouldn't stop existing ones from being edited. I'm pretty sure the queries can't be combined for a one-hit read, because of the relationships. I've wondered if it's a possibility but it's definitely beyond my skill level.

It's an Access BE.
Okay, this may help solve the problem (or maybe not). Can you modify your SELECT queries to add a WHERE clause to limit the records to existing records?

In your original post/concern, you kept saying a user might "edit" the data. But, in your latest example, it seems you're more concerned about users "adding" new data. If so, then perhaps by limiting the calculation to pre-existing data (by maybe using a date and time criteria), you can avoid miscalculations or misrepresentation in your reports.

Just a thought...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
It might not be just new transactions that are being added, the existing ones might be edited. All this could happen in two seconds, couldn't it?
Oops, I guess I missed that one. Still though, this goes back to what I was wondering about. If the user can "edit" existing data that affects "balance sheet" calculations, then why is this allowed? Or, at the very least, the calculations should be redone.

How would you do that? Maybe by adding a date/timestamp to when the record was modified (ModifiedDate). That way, you can if you need to rerun the report (that is if you also put a date/timestamp on when that particular report was calculated).

In any case, we already said what you want to do is possible. However, whether it's really worth the effort was still questionable.

Cheers!
 

Isaac

Lifelong Learner
Local time
Today, 13:10
Joined
Mar 14, 2017
Messages
8,738
And I reiterate my idea of simply adding more information - "as of" type of information. Then there is no wondering as you have described they might wonder.

I.E.,

No one would wonder: "Why is the report called Report As Of 2020-11-25 14:47:10 not showing something I entered at 2020-11-25 14:47:12 ??
Of course, I agree with anyone who thinks "Well who is keeping track of the 2 seconds anyway?" - which just leads back to the same answer, though--if the report is produced in Second "1", do you REALLY have users who are going to intelligently wonder why it doesn't include something that they know they entered at roughly, approximately, the same second?

I serve a large population including at least a few very foolish users, like everyone, of course, but I think few would Edit or Add a record at the exact same time a report is being pulled and then wonder why the report doesn't include it--knowing there was a likely overlap. Especially if the overlap in question is known to be seconds.
 

GK in the UK

Registered User.
Local time
Today, 20:10
Joined
Dec 20, 2017
Messages
274
The user that's running the report may not have any idea that someone else is editing or adding records. So they just get a duff result and rely on what's reported. Yes, there's a very short time window to insert/edit a record but wrong is wrong ...

On balance I decided that because it sounds tricky to work around, and relatively trivial / unlikely anyway, I would leave the warning message in place. But, I enhanced it a bit. I use Isladogs' login form with associated tblLoggedInUsers so I made a routine to only pop up the message if other users are actually logged in.

Thanks all for useful input.
 

Attachments

  • Logged In Users.jpg
    Logged In Users.jpg
    29.7 KB · Views: 99

Isaac

Lifelong Learner
Local time
Today, 13:10
Joined
Mar 14, 2017
Messages
8,738
Well, interesting discussion for sure. I always figured every report everywhere that took a few seconds to run (most), they all are only as 'good' as the last edit they picked up, and that's never considered to be 'wrong' in my book, but I'm glad you got something that you are satisfied with. Display & Information is everything in a GUI - i.e. managing expectations
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
The user that's running the report may not have any idea that someone else is editing or adding records. So they just get a duff result and rely on what's reported. Yes, there's a very short time window to insert/edit a record but wrong is wrong ...

On balance I decided that because it sounds tricky to work around, and relatively trivial / unlikely anyway, I would leave the warning message in place. But, I enhanced it a bit. I use Isladogs' login form with associated tblLoggedInUsers so I made a routine to only pop up the message if other users are actually logged in.

Thanks all for useful input.
Hi. Sounds like a good compromise to me. Cheers!
 

Users who are viewing this thread

Top Bottom