select_for_update should not be executed outside of transactions

[Tim Graham]

Seeing these test failures after 0f9560855e5ed203b8c911c23237826e28a62a38:

$ ./runtests.py --settings=test_oracle select_for_update
Testing against Django installed in '/home/tim/code/django/django'
Creating test database for alias 'default'...
Creating test user...
Creating test database for alias 'other'...
Creating test user...
.EE...s
======================================================================
ERROR: test_for_update_sql_generated (select_for_update.tests.SelectForUpdateTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 916, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/select_for_update/tests.py", line 80, in test_for_update_sql_generated
    list(Person.objects.all().select_for_update())
  File "/home/tim/code/django/django/db/models/query.py", line 141, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 961, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/tim/code/django/django/db/models/query.py", line 265, in iterator
    for row in compiler.results_iter():
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 694, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1150, in cursor_iter
    sentinel):
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1149, in <lambda>
    for rows in iter((lambda: cursor.fetchmany(GET_ITERATOR_CHUNK_SIZE)),
  File "/home/tim/code/django/django/db/utils.py", line 101, in inner
    return func(*args, **kwargs)
  File "/home/tim/code/django/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/tim/code/django/django/db/utils.py", line 101, in inner
    return func(*args, **kwargs)
  File "/home/tim/code/django/django/db/backends/oracle/base.py", line 905, in fetchmany
    return tuple(_rowfactory(r, self.cursor) for r in self.cursor.fetchmany(size))
DatabaseError: ORA-01002: fetch out of sequence


======================================================================
ERROR: test_for_update_sql_generated_nowait (select_for_update.tests.SelectForUpdateTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 916, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/select_for_update/tests.py", line 89, in test_for_update_sql_generated_nowait
    list(Person.objects.all().select_for_update(nowait=True))
  File "/home/tim/code/django/django/db/models/query.py", line 141, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 961, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/tim/code/django/django/db/models/query.py", line 265, in iterator
    for row in compiler.results_iter():
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 694, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1150, in cursor_iter
    sentinel):
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1149, in <lambda>
    for rows in iter((lambda: cursor.fetchmany(GET_ITERATOR_CHUNK_SIZE)),
  File "/home/tim/code/django/django/db/utils.py", line 101, in inner
    return func(*args, **kwargs)
  File "/home/tim/code/django/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/tim/code/django/django/db/utils.py", line 101, in inner
    return func(*args, **kwargs)
  File "/home/tim/code/django/django/db/backends/oracle/base.py", line 905, in fetchmany
    return tuple(_rowfactory(r, self.cursor) for r in self.cursor.fetchmany(size))
DatabaseError: ORA-01002: fetch out of sequence


----------------------------------------------------------------------
Ran 7 tests in 3.987s

FAILED (errors=2, skipped=1)

But the problem is not really in the Oracle backend -- it is in allowing select_for_update() queries to run outside of transactions.

[Shai Berger]

Indeed. It seems like, in autocommit mode, the query execution is done in a (sub-)transaction separate from the fetches, which triggers this error when the selection is for update. I'm looking into it.

[Shai Berger]

Patch ​here, reviews welcome (I think the code is simple enough, but documentation may need improvements).

I am not making this a PR, lest it be merged -- this needs backporting, and it's not trivial w.r.t release notes.

See also ​discussion on the developers mailing list.