@John and @Anthony Thanks for your time.
@John
I disagree with you about the corner thing.
With Anthony suggestion I enclosed all my update_record and insert (I don't
have delete) around this
while True:
try:
# SQLite only does database lock.
db.executesql('BEGIN IMMEDIATE TRANSACTION')
break
except sqlite3.OperationalError:
sleep(0.5)
# Do update_record or insert in one or more tables.
db.commit()
I don't see a problem with this solution. Even if has a problem area it is
much smaller than not using Anthony's suggestion.
In relation to the flag, you are correct. There is no need for it and I
removed it.
a) What I understood by Anthony's explanation is that the transaction is
active but in deferred state. Only when the function ends is it commited.
But in either case the code I'm using with Anthony's suggestion corrects
both a non-existing and deferred transaction.
b) I don't think I ever said they didn't work in SQLite or in general. What
I might have said is that they didn't work for my case (using the
auto-created transaction from web2py). Again, with Anthony's suggestion I'm
using SQLite transaction (bypassing web2py's).
c) You understood correctly.
d) I only need to create the transaction before writing. I don't see the
need when reading. Am I missing something?
What you indicate in
> db.executesql('BEGIN IMMEDIATE TRANSACTION')
>
> before you start reading and updating your counters, and the work order,
> and then
>
> db.commit()
>
is what I'm using after Anthony's suggestion.
1. I don't understand why I must use the transaction when reading. Can you
explain?
2. I'm doing it in batch and after user editing. Even on my previous code
(before Anthony's suggestion) it was done that way.
segunda-feira, 25 de Março de 2019 às 02:52:59 UTC, John Underhill escreveu:
>
> @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] <javascript:> <[email protected]
> <javascript:>> *On Behalf Of *João Matos
> *Sent:* Sunday, March 24, 2019 2:47 PM
> *To:* web2py-users <[email protected] <javascript:>>
> *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]
> 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] <javascript:>.
> 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.
