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