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

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:29
Joined
Feb 28, 2001
Messages
27,001
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.

Either I'm being especially dense, or you are describing something that cannot happen as you describe it. Those are SELECT queries according to your explanation. It is insanely difficult verging on flat-out impossible for a SELECT query to update a table (or as you say, "saved in the same records."). You need an INSERT INTO or UPDATE query to do that, yet you persist in calling these SELECT queries.

I think further clarification is required as to this process. If I have missed something, I deeply apologize, but I just can't make a mental image of whatever is going on here. Without that image, I'm kind of lost.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:29
Joined
May 7, 2009
Messages
19,169
if you are performing mission critical computations, then it would be recommended that
you "gracefully kickout" users (with warning to users). so that you can perform
the operations without alteration.

you can google how to gracefully warned users that the database is for maintenance/ or
will perform an operation (involving money) so that they need to logout of the system
(for a while).
 

GK in the UK

Registered User.
Local time
Today, 23:29
Joined
Dec 20, 2017
Messages
274
Doc_Man, it's my explanation that's lacking I think.

I should have made clear, OTHER users could edit and update the records between the 3 selects of the union query.

When I said 'saved in the same records', I meant, we get the net value grouped by expense category in query 1, we get the vat value grouped by vat code in query 2, and we get the gross value (by summing net and vat) grouped by control account in query 3. All from 1 record, if you like, but at different times (albeit only a very small timescale). Each query selects the same records from the same table, but we've established that those records can be updated between the selects of the union.
 

Minty

AWF VIP
Local time
Today, 23:29
Joined
Jul 26, 2013
Messages
10,355
If these queries are all based on the same intrinsic data simply dump the initial select into a recordset or temptable, and perform your calculations from that.
The subsequent queries cannot then be affected, as the core data is now static regardless of how ninja your end users are at waiting for the exact millisecond to edit records you are reporting on.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:29
Joined
Feb 28, 2001
Messages
27,001
OTHER users could edit and update the records between the 3 selects of the union query.

Not if you make the UNION query "Pessimistic locking." But doing so invites other problems. You could also make everything a Transaction that isolates the DB to prevent those changes. But you have to SAVE those values with an UPDATE or INSERT INTO query because otherwise, as soon as the transaction is over, the values can change. Pending updates can take effect.

If you want what is essentially an atomic action, use the transaction facility to MAKE it atomic. I see this as a subtle design issue. As implemented, you cannot do what you want. It would appear that you have a set of options and need to choose the least of several evils.

For less-familiar readers, "atomic" is database-speak for indivisible, monolithic, or isolated.
 

Users who are viewing this thread

Top Bottom