3

If I want to start a transaction in my database through python do I have to execute the sql command 'BEGIN TRANSACTION' explicitly like this:

import sqlite3

conn = sqlite3.connect(db)
c = conn.cursor()

c.execute('BEGIN TRANSACTION;')
##... some updates on the database ...
conn.commit() ## or c.execute('COMMIT'). Are these two expressions the same?

Is the database locked for change from other clients when I establish the connection or when I begin the transaction or neither?

jtht
  • 793
  • 2
  • 10
  • 19

3 Answers3

2

Only transactions lock the database.

However, Python tries to be clever and automatically begins transactions:

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

You can control which kind of BEGIN statements sqlite3 implicitly executes (or none at all) via the isolation_level parameter to the connect() call, or via the isolation_level property of connections.

If you want autocommit mode, then set isolation_level to None.

Otherwise leave it at its default, which will result in a plain “BEGIN” statement, or set it to one of SQLite’s supported isolation levels: “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • "So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command." From https://docs.python.org/3.6/library/sqlite3.html#controlling-transactions: "Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case." – Dale Z Apr 24 '17 at 15:35
  • @DaleZ It still automatically *opens* them, and now you don't know whether transactions implicitly commit or not … – CL. Apr 24 '17 at 15:43
1

From python docs:

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

conrad
  • 1,783
  • 14
  • 28
  • And no you don't need to execute BEGIN TRANSACTION – conrad Nov 06 '14 at 02:28
  • so the BEGIN TRANSACTION is implied by opening of the connection? – jtht Nov 06 '14 at 02:30
  • To my understanding, yes it is implied. I would encourage you to run some code on a sqlite3 shell as [here](https://docs.python.org/3.4/library/sqlite3.html#sqlite3.complete_statement) to just test it out quickly. Looking at it another way, if you go into python standard library source code and look at /sqlite3/dump.py, which is I think what the above code is using, you see that the BEGIN TRANSACTION statement is automatically done for you. – conrad Nov 06 '14 at 02:49
0

If I want to start a transaction in my database through python do I have to execute the sql command 'BEGIN TRANSACTION' explicitly like this:

It depends if you are in auto-commit mode (in which case yes) or in manual commit mode (in which case no before DML statements, but unfortunately yes before DDL or DQL statements, as the manual commit mode is incorrectly implemented in the current version of the SQLite3 database driver, see below).

conn.commit() ## or c.execute('COMMIT'). Are these two expressions the same?

Yes.

Is the database locked for change from other clients when I establish the connection or when I begin the transaction or neither?

When you begin the transaction (cf. SQLite3 documentation).

For a better understanding of auto-commit and manual commit modes, read this answer.

Géry Ogam
  • 6,336
  • 4
  • 38
  • 67