@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]. For more options, visit https://groups.google.com/d/optout.
