Does web2py/DAL allow for creating atomic operations (begin transaction/end transaction)?

[João Matos]

Hello,

I need to ensure that a single transaction updates 3 tables (1 record each) in the same db.

The transaction must comply with:

1. Begin transaction - don't allow anyone else to write to those records/rows in the 3 tables.

2. Write and commit the updated records in those 3 tables.

3. End transaction, allowing everyone to write to all tables and rows.

4. There can only be 1 transaction per record/row (for each table) at the same time.

5. If anyone tries to write while a transaction is in place either receives an error or some kind of message that identifies that the record is being updated.

Does anyone know if web2py/DAL allow me to do/have this?

I'm  using SQLite and apparently it only does database lock (not record/row or even table).

But I willing to accept that if I can follow the requirements above replacing record/row lock with database lock.

Another option is to change the db backend to MySQL or PostgreSQL if only they would comply with the requirements above.

Thanks,

JM

[Leonel Câmara]

Web2py always "wraps" your controller functions in a db transaction, so yes, you can do that with any database which has transactions correctly implemented. You don't need to worry about doing it manually.

[João Matos]

What I read about the commit/rollback "wrapping" that web2py does, it doesn't seem to solve my problem.

Let me explain.

I have 3 tables (sn_counter, wo_counter and wo).

The procedure I must do is the following:

1. Get the record from wo_counter table a extract the counter value (read operation), let's suppose I get the number 10.

2. Increase the counter to 11 and update the wo_counter record.

3. Get the record from sn_counter table a extract the counter value (read operation), let's suppose I get the number 20.

4. Increase the counter to 21 and update the sn_counter record.

5. Write a new record in the wo table with the original counters (10 and 20).

During all these operations, no one can write to any of the 2 records used in steps 1 to 4.

I don't think the existing commit/rollback does that. Does it?

[Leonel Câmara]

It does. A transaction is an atomic operation.

https://www.geeksforgeeks.org/acid-properties-in-dbms/

[João Matos]

 Yes, I know.

See the steps I mentioned on my previous message.

From web2py's perspective (if I'm not mistaken) is like this:

0. Enter function (activate web2py "transaction").

My steps go in the middle.

6. Commit "transaction".

If anyone changes the record from wo_counter or sn_counter in the middle of the commit/rollback, web2py/DAL won't stop them because no locking is in place until the commit/rollback.

I am seeing this the wrong way?

[Leonel Câmara]

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.

[Anthony]

On Wednesday, March 20, 2019 at 9:56:11 PM UTC-4, João Matos wrote:

What I read about the commit/rollback "wrapping" that web2py does, it doesn't seem to solve my problem.

Let me explain.

I have 3 tables (sn_counter, wo_counter and wo).

The procedure I must do is the following:

1. Get the record from wo_counter table a extract the counter value (read operation), let's suppose I get the number 10.

At this step, you can lock this particular wo_counter record using for_update:

row = db(query).select(db.wo_counter.counter, for_update=True).first()

The above will (a) wait until the relevant rows are unlocked via a similar query in another request before doing the select and then (b) lock the relevant rows until the current transaction is complete. So, once you've read the counter value, you can proceed with the rest of the transaction without worrying about other requests making intervening changes.

Anthony

[João Matos]

@Anthony

If I try for_update=True in the 1st SELECT I get this error sqlite3.OperationalError: near "FOR": syntax error.

My original code

row = db.wo_counter(year_=request.now.year)

My interpretation of your suggestion

row = db(db.wo_counter.year_ == request.now.year).select(
    db.wo_counter.year_, db.wo_counter.last_assigned, for_update=True).first()

The error I get

  File "C:\web2py\applications\ajc\controllers/open_wo.py", line 319, in new_par
t2
    db.wo_counter.year_, db.wo_counter.last_assigned, for_update=True).first()
  File "C:\web2py\gluon\packages\dal\pydal\objects.py", line 2378, in select
    return adapter.select(self.query, fields, attributes)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\sqlite.py", line 82, in sele
ct
    return super(SQLite, self).select(query, fields, attributes)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 763, in selec
t
    return self._select_aux(sql, fields, attributes, colnames)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 719, in _sele
ct_aux
    rows = self._select_aux_execute(sql)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 713, in _sele
ct_aux_execute
    self.execute(sql)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\__init__.py", line 66, in wr
ap
    return f(*args, **kwargs)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 413, in execu
te
    rv = self.cursor.execute(command, *args[1:], **kwargs)
sqlite3.OperationalError: near "FOR": syntax error

If I remove the for_update=True from the 1st SELECT and add it to the 2nd I get a different error sqlite3.OperationalError: cannot start a transaction within a transaction.

My original code

        sn_counter_id = db.equipment(session.new_form_part1.equipment_id).sn_counter_id

My interpretation of your suggestion

        sn_counter_id = db(db.equipment.id == session.new_form_part1.equipment_id).select(db.equipment.sn_counter_id, for_update=True).first().sn_counter_id

The error I get

ERROR:web2py:Traceback (most recent call last):
  File "C:\web2py\gluon\restricted.py", line 219, in restricted
    exec(ccode, environment)
  File "C:\web2py\applications\ajc\controllers/open_wo.py", line 708, in <module
>
  File "C:\web2py\gluon\globals.py", line 421, in <lambda>
    self._caller = lambda f: f()
  File "C:\web2py\gluon\tools.py", line 3868, in f
    return action(*a, **b)
  File "C:\web2py\applications\ajc\controllers/open_wo.py", line 331, in new_par
t2
    ).select(db.equipment.sn_counter_id, for_update=True).first().sn_counter_id
  File "C:\web2py\gluon\packages\dal\pydal\objects.py", line 2378, in select
    return adapter.select(self.query, fields, attributes)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\sqlite.py", line 81, in sele
ct
    self.execute('BEGIN IMMEDIATE TRANSACTION;')
  File "C:\web2py\gluon\packages\dal\pydal\adapters\__init__.py", line 66, in wr
ap
    return f(*args, **kwargs)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 413, in execu
te
    rv = self.cursor.execute(command, *args[1:], **kwargs)
sqlite3.OperationalError: cannot start a transaction within a transaction

If I remove the for_update=True everything works either with my original code and with the changes made to be able to add the for_update=True.

I'm confused regarding the 1st error.

The 2nd error might be because there is already an implicit transaction from web2py itself?

I'm using SQLite.

[João Matos]

@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.

[Leonel Câmara]

That's weird. It seems looking at the adapter in pydal, sqlite does not start a transaction after connection like most adapters. Reading the sqlite documentation, that's probably because sqlite starts a transaction automatically as soon as anything writes to the database. 

 

So, basically, you can use for_update in a select to start a transaction but only if sqlite didn't have any reason to start one before you did, which you want to do because by default sqlite starts transactions in deferred mode which means it only locks when it thinks it needs to. So what you want to do is to have a for_update select as the very first line in your controller and then use no other for_update=True as that will try to start a transaction in immediate mode again.

[João Matos]

Thanks, but I can't do that, because the controller is used for the grid and several other functions (onvalidation, ondelete, etc.).

Besides, "locking" a record for the entire time a user is looking at it isn't a good practice.

Until I found/learn a better solution with web2py, I'm using an old school lock file. I create the file when I start the procedure I mentioned on one of the first messages and delete it in the last step.

This is not a perfect solution but is better that none.

If you checked my answer to Anthony I suspected that the 2nd error message would be something like what you describe.

What I'm confused about is the 1st error message. I can't understand it.

[Anthony]

Looks like a bug was introduced. SQLITE does not support the SQL "FOR UPDATE" command, so the DAL simulates it by executing "BEGIN IMMEDIATE TRANSACTION", which simply starts a transaction and locks the database. Originally, the SQLite adapter did not add the "FOR UPDATE" to queries when for_update=True, but it looks like in the refactor, that was overlooked, and SQLite now simply uses the same code as other SQL adapters. That should be fixed.

When you added for_update=True to the first query, no transaction had been started at that point, so the "BEGIN IMMEDIATE TRANSACTION" command did not result in an error, and instead the "FOR UPDATE" command (which is run after) created the error. When you added for_update=True to the second query, a transaction had already started, so the "BEGIN IMMEDIATE TRANSACTION" command triggered an error (before the "FOR UPDATE" command even got executed).

If for_update were working properly, you could use it on your first query and simply leave it out of the second query, as it would be unnecessary. However, since for_update is currently broken for SQLite, you can instead do the following -- right before the first select:

db.executesql('BEGIN IMMEDIATE TRANSACTION')

That will do exactly what for_update=True is supposed to do without generating the error. From that point until the end of the request when web2py automatically closes the transaction, the database should be locked (as long as you haven't put SQLite into WAL mode, in which case, you cannot use this approach).

Anthony

[João Matos]

@Anthony

Thanks. That solves it until the SQLite code is fixed.

Is there a way to question SQLite if an immediate transaction is currently open?

sexta-feira, 22 de Março de 2019 às 11:38:48 UTC, Anthony escreveu:

Looks like a bug was introduced. SQLITE does not support the SQL "FOR UPDATE" command, so the DAL simulates it by executing "BEGIN IMMEDIATE TRANSACTION", which simply starts a transaction and locks the database. Originally, the SQLite adapter did not add the "FOR UPDATE" to queries when for_update=True, but it looks like in the refactor, that was overlooked, and SQLite now simply uses the same code as other SQL adapters. That should be fixed.

When you added for_update=True to the first query, no transaction had been started at that point, so the "BEGIN IMMEDIATE TRANSACTION" command did not result in an error, and instead the "FOR UPDATE" command (which is run after) created the error. When you added for_update=True to the second query, a transaction had already started, so the "BEGIN IMMEDIATE TRANSACTION" command triggered an error (before the "FOR UPDATE" command even got executed).

If for_update were working properly, you could use it on your first query and simply leave it out of the second query, as it would be unnecessary. However, since for_update is currently broken for SQLite, you can instead do the following -- right before the first select:

db.execute_sql('BEGIN IMMEDIATE TRANSACTION')

[junde...@qisproject.com]

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

[João Matos]

@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.

[Anthony]

On Sunday, March 24, 2019 at 2:29:41 PM UTC-4, junde...@qisproject.com wrote:

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.

At the point of the first read, it is not in a transaction (a transaction is triggered by the first write operation). That is why the "BEGIN IMMEDIATE TRANSACTION" is necessary - to start the transaction, which in the case of SQLite (in non-WAL mode) locks the database.

Anthony 

[John Underhill]

@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

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

[Anthony]

c) Anthony tried to force the transaction open with the FOR UPDATE clause, but that doesn’t work in SQLite.

To clarify, in recognition of the fact that SQLite does not support "FOR UPDATE", the DAL attempts to simulate it with SQLite by instead preceding the select with "BEGIN IMMEDIATE TRANSACTION" (this works in non-WAL mode because it locks the entire database for reads and writes). However, a bug was introduced to the DAL whereby in addition to executing "BEGIN IMMEDIATE TRANSACTION", it now also adds "FOR UPDATE" to the select itself, which results in an exception. My suggested workaround is to not use .select(..., for_update=True), which is broken, and to manually do what for_update used to do with SQLite, which is to execute "BEGIN IMMEDIATE TRANSACTION". I have opened an issue on Github regarding the bug.

Anthony

[João Matos]

@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.