@João, you are programming yourself into a corner. No matter what you do,
you’ll keep running into more problems. The reason for this is because you’re
trying to do atomic operations at the application level. You simply can’t do
that.
If you check whether or not something is locked at the application level, and
then take some action, you risk making a mistake. What happens if the lock
state changes between when you checked, and the next operation in your program?
You will take the wrong action. By definition, *anything* can happen between
*any* two operations in your program. Another process could change the state:
1: if databaseislocked() then:
2: doseomthing()
3: else:
4: dosomethingelse()
What if the database state changes between 1 and 2 or 4? Perhaps you think if
it ends up being locked, and you try to lock it again, then your subsequent
attempt will fail. But then what? Do you go into to a loop trying to lock it?
Now you can get blocked waiting for a lock, or worse, get into a deadlock
situation, with two processes blocked, each waiting for the other one to free
up something.
So you say you created a flag? Now you’ve just pushed the problem back onto
another application-level ‘lock’ that can’t possibly be relied upon (unless
you’re using a low-level locking mechanism, and even then this can be tricky).
My understanding of what I read between you and Anthony is this:
a) SQLite defers starting the actual transaction until the first write, so your
initial code wasn’t actually wrapped in a transaction at all.
b) As a result, your tests failed. But this doesn’t mean transactions don’t
work in SQLite, or in general.
c) Anthony tried to force the transaction open with the FOR UPDATE clause, but
that doesn’t work in SQLite.
d) So the solution for SQLite is to explicitly start the transaction before
reading and/or writing anything.
So all you have to do is
db.executesql('BEGIN IMMEDIATE TRANSACTION')
before you start reading and updating your counters, and the work order, and
then
db.commit()
as soon as you’re done. You *do not* need to check if the database is already
locked. SQLite will handle this during the commit process. It will guarantee
that any two operations running concurrently are isolated from each other—each
process will see a consistent view of the database, and the final result will
be the same as if they had executed one after the other (serially). If they
collide, SQLite will abort one of them, and restart it. For this to work, it’s
important that:
1. You start the transaction before you *read* anything, not just before you
write. SQLite needs to know what you’re looking at, as well as what you intend
to change.
2. You do the whole process in batch. You can’t stop to wait for the user
to do something. Get all the input you need from the user first, then do the
update without stopping.
This process will be more scalable, and not require locking the whole database,
on a multi-user MVCC database like Postgres. This would also avoid the
silliness with the SQLite adapter bugs. We use Postgres with Nginx and Web2py
in production systems with lots of contention and it works beautifully. But
this really only matters if you have lots of users. SQLite is very solid for
smaller, single-server deployments.
John
From: [email protected] <[email protected]> On Behalf Of João Matos
Sent: Sunday, March 24, 2019 2:47 PM
To: web2py-users <[email protected]>
Subject: [web2py] Re: Does web2py/DAL allow for creating atomic operations
(begin transaction/end transaction)?
@John
I think you didn't understand the problem.
I have to assign a wo_counter and a sn_counter to each new record of the wo
table.
To do that, I must disable any attempt to change them during the process of
saving the wo record to the database.
That process consists of:
1. If the user didn't select an existing wo_counter, then I must read the last
assigned wo_counter, increase it, save it in memory to assign it to the wo
record, and save the new value in the wo_counter table.
2. Read the last assigned sn_counter (which is different by equipment_id
selected by the user), increase it, save it in memory to assign it to the wo
record, and save the new value in the sn_counter table.
3. Save the wo record with the wo_counter and sn_counter that were stored in
memory.
The existing transaction auto-created by web2py within each action does not
allow the protection I need. I tested it. If you want you can follow this test
procedure I used.
Put a sleep command between step 1 and 2.
Open session 1 where the wo record procedure I listed above is running until it
pauses in the sleep command.
On session 2 access the table for the wo_counter or sn_counter. Change the
value of the counter and save it.
Wait until the pause ends on session 1 and the wo record function will save
it's value over the value stored by the session 2.
This means that information was lost.
What is needed and works with Anthony's solution is to force the lock of the
database (this is a limitation of SQLite, it would be a better solution to lock
only the table or even better only the record in question).
This way I'm sure that if someone else tries to change the counters while a
session is changing them to save a record, they will not succeed.
I tested Anthony's solution and it does work because when using the same test I
explained above, the 2nd session is not able to change the record. SQLite
returns an error because the db is locked.
This is exactly what I need.
The only thing missing is a way to check if the SQLite db is in locked state or
not.
Until now I couldn't find any way to check that, so I'm complementing Anthony's
solution with a flag and check for the flag on the wo_counter and sn_counter
edit/delete actions.
The reasons for not using the auto-increment id field auto-created by the
web2py for the wo table as a wo_counter is both legal and format related.
The sn_counter because it depends on the equipment_id makes it impossible to
use the auto-increment id field auto-created by the web2py.
domingo, 24 de Março de 2019 às 18:29:41 UTC,
[email protected]<mailto:[email protected]> escreveu:
João,
No, SQLite transactions are not only ACID, the Isolation is Serializable, which
means even if the execute concurrently, the result should be same as if they
executed in series (on after the other). What @Anthony describes should not be
necessary, since you already in a transaction.
https://www.sqlite.org/transactional.html
https://www.sqlite.org/atomiccommit.html
If everything is set up correctly, it should work. Please note that both
sessions must be in a transaction to guarantee a consistent result. You can't
just go change the database while the transaction is pending. That can fail.
This is the purpose of transactions.
Also please note that this is probably note the best way to do this. SQL has
auto-increment fields that are good for assigning numbers to things, such as
unique IDs to records. Web2py automatically creates an auto-increment ID field
for all your tables. So if you have a table for Items, let's say, the ID field
in that table will already be a unique number for every new record, which you
could use for your serial number. Just insert a new record to get a new ID.
If you don't want to use the built-in ID, you can create your own
auto-increment field.
If you have a table for Work Orders, it will already have an ID field that's
automatically set to a new unique number for each record. Just insert a new
record to get a new number. If you don't want to use the ID field as your work
order number, you can create your own field. No need to read, update, and
store counters.
John
On Thursday, March 21, 2019 at 3:18:20 PM UTC-7, João Matos wrote:
@Leonel
Maybe I'm not explaining myself correctly.
I tested and it isn't working the way I need and explained.
My test was:
I put a sleep(10) between step 1 and 2.
On another session I changed the record in question. Checked the changed was
done.
Waited for the sleep to end and then step 2 wrote over the change I made on the
other session.
The end result is that the transaction isn't protecting (locking) the records
in question, which is what I need.
quinta-feira, 21 de Março de 2019 às 13:11:27 UTC, Leonel Câmara escreveu:
The transaction is atomic, that means there's nothing happening in the middle.
Your use case is fine. Of course, that if you give the user a form, while he's
editing it, the form can be changed by others as well, in that case you need to
add your own locking or check the record for modifications while the user was
editing the form.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
[email protected]<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
