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')
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
On Thursday, March 21, 2019 at 3:27:04 PM UTC-4, João Matos wrote:
>
> @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.
>
>
> quinta-feira, 21 de Março de 2019 às 15:36:56 UTC, Anthony escreveu:
>>
>> 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
>>
>
--
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.
