SQLite 错误:无法提交事务 - 使用 Java 代码正在进行 SQL 语句

发布于 2024-07-25 08:37:26 字数 130 浏览 18 评论 0原文

尽管我没有使用任何显式的 AutoCommit true 或 false,但我遇到了 SQLite 错误。 任何人都可以提供有关此错误的任何信息。 什么情况下会出现此错误。

提前致谢。 问候, 玛纳西保存

I am facing an SQLite error though I am not using any explicit AutoCommit true or false.
can anyone provide any input on this error. What are the situation where you will get this error.

Thanks in advance.
Regards,
Manasi Save

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

风柔一江水 2024-08-01 08:37:26

当未使用 RETURNING 子句返回的值时,可能会发生该错误。 我可以提供复制代码,尽管它是用 Python 编写的。

这有效:

# Connect to an in-memory database
import sqlite3
conn = sqlite3.connect(":memory:")

# Create a table
conn.execute("CREATE TABLE t(c)")
conn.commit()

# INSERT ... RETURNING with fetchall()
cursor = conn.execute("INSERT INTO t VALUES (1) RETURNING rowid")

cursor.fetchall()  # returns [(1,)]
conn.commit()  # no error

这不起作用:

# Connect to an in-memory database
import sqlite3
conn = sqlite3.connect(":memory:")

# Create a table
conn.execute("CREATE TABLE t(c)")
conn.commit()

# INSERT ... RETURNING without fetchall()
cursor = conn.execute("INSERT INTO t VALUES (1) RETURNING rowid")

# Traceback (most recent call last):
#   File "<stdin>", line 1, in <module>
# sqlite3.OperationalError: cannot commit transaction - SQL statements in progress
conn.commit()

That error can happen when the values returned by a RETURNING clause are not used. I can provide a reproduction code, although it is in Python.

This works:

# Connect to an in-memory database
import sqlite3
conn = sqlite3.connect(":memory:")

# Create a table
conn.execute("CREATE TABLE t(c)")
conn.commit()

# INSERT ... RETURNING with fetchall()
cursor = conn.execute("INSERT INTO t VALUES (1) RETURNING rowid")

cursor.fetchall()  # returns [(1,)]
conn.commit()  # no error

This does not work:

# Connect to an in-memory database
import sqlite3
conn = sqlite3.connect(":memory:")

# Create a table
conn.execute("CREATE TABLE t(c)")
conn.commit()

# INSERT ... RETURNING without fetchall()
cursor = conn.execute("INSERT INTO t VALUES (1) RETURNING rowid")

# Traceback (most recent call last):
#   File "<stdin>", line 1, in <module>
# sqlite3.OperationalError: cannot commit transaction - SQL statements in progress
conn.commit()
蓝眸 2024-08-01 08:37:26

当我处于循环表条目的循环中时,我反复遇到类似的问题(在我的情况下,这是不可能的回滚)。 只要游标正在处理条目,SQL 语句就“正在进行”。 我不确定这是否也禁止提交,但有可能。

当您尝试处理表条目并在相同或不同的表中插入条目时,您可能需要尝试收集内存中的数据,并在循环后执行插入或更新。

附加信息:在 SQLite 中,“自动提交”通常默认为“True”(当然,它也可能取决于您使用的访问层——我使用的是 Python 和 apsw,所以我无法在 Java 中告诉您更多相关信息)。 这意味着每个插入都会立即自动提交。

==> 这可能是另一种解决方案。 您可以尝试显式打开事务并在循环后提交它,而不是将数据存储在内存中——这样,问题就会消失。

I faced a similar problem repeatedly (in my case it was a rollback that was not possible) when I was inside a loop looping over table entries. As long as the cursor is processing the entries, an SQL statement is "in progress". I don't know exactly, if this also prohibits commits, but it could be.

When you try to process table entries and insert entries in the same or a different table, you might want to try to collect the data in memory and after the loop do the inserts or updates.

Addtitional info: "Autocommit" normally defaults to "True" in SQLite (it of course could also depend on the access layer you use -- I am using Python and apsw, so I can't tell you more about this in Java). This means, that every insert is autocommited immediatly.

==> this could be an other solution. Instead of storing the data in memory, you could try to explicitly open a transaction and commit it after the loop -- this way, the problem should go also away.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文