0
# Transactions
1
2
Transaction control, isolation levels, and connection state management. Provides comprehensive transaction handling including commit, rollback operations, isolation level control, and transaction state monitoring.
3
4
## Capabilities
5
6
### Transaction Control
7
8
Core transaction management methods for controlling database transaction lifecycle.
9
10
```python { .api }
11
async def commit(self) -> None:
12
"""
13
Commit the current transaction.
14
15
Saves all changes made since the last commit or rollback.
16
If not in a transaction, this method has no effect.
17
After commit, a new transaction may be started automatically
18
depending on the isolation_level setting.
19
"""
20
21
async def rollback(self) -> None:
22
"""
23
Roll back the current transaction.
24
25
Discards all changes made since the last commit or rollback.
26
If not in a transaction, this method has no effect.
27
After rollback, a new transaction may be started automatically
28
depending on the isolation_level setting.
29
"""
30
```
31
32
Usage example:
33
34
```python
35
import aiosqlite
36
37
async def transfer_funds(from_account: int, to_account: int, amount: float):
38
async with aiosqlite.connect("bank.db") as db:
39
try:
40
# Start transaction (automatic with first statement)
41
await db.execute(
42
"UPDATE accounts SET balance = balance - ? WHERE id = ?",
43
(amount, from_account)
44
)
45
46
await db.execute(
47
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
48
(amount, to_account)
49
)
50
51
# Check if first account has sufficient funds
52
async with db.execute(
53
"SELECT balance FROM accounts WHERE id = ?",
54
(from_account,)
55
) as cursor:
56
row = await cursor.fetchone()
57
if row and row[0] < 0:
58
raise ValueError("Insufficient funds")
59
60
# Commit the transaction
61
await db.commit()
62
print("Transfer completed successfully")
63
64
except Exception as e:
65
# Rollback on any error
66
await db.rollback()
67
print(f"Transfer failed: {e}")
68
raise
69
```
70
71
### Transaction State
72
73
Properties for monitoring and controlling transaction state and behavior.
74
75
```python { .api }
76
@property
77
def in_transaction(self) -> bool:
78
"""
79
True if connection has an uncommitted transaction.
80
81
Indicates whether there are pending changes that have not been
82
committed or rolled back. Useful for checking transaction state
83
before performing operations.
84
85
Returns:
86
bool: True if in transaction, False otherwise
87
"""
88
```
89
90
Usage example:
91
92
```python
93
async with aiosqlite.connect("database.db") as db:
94
print(f"In transaction: {db.in_transaction}") # False initially
95
96
# Start a transaction by executing a statement
97
await db.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))
98
print(f"In transaction: {db.in_transaction}") # True after INSERT
99
100
# Commit to end transaction
101
await db.commit()
102
print(f"In transaction: {db.in_transaction}") # False after commit
103
```
104
105
### Isolation Levels
106
107
Control transaction isolation behavior and concurrency handling.
108
109
```python { .api }
110
@property
111
def isolation_level(self) -> Optional[str]:
112
"""
113
Current isolation level setting.
114
115
Controls how transactions interact with concurrent operations:
116
- None: Autocommit mode (no transactions)
117
- "DEFERRED": Transaction starts on first read/write
118
- "IMMEDIATE": Transaction starts immediately with shared lock
119
- "EXCLUSIVE": Transaction starts immediately with exclusive lock
120
121
Returns:
122
Optional[str]: Current isolation level or None for autocommit
123
"""
124
125
@isolation_level.setter
126
def isolation_level(self, value: IsolationLevel) -> None:
127
"""
128
Set isolation level for transactions.
129
130
Parameters:
131
- value: Isolation level - "DEFERRED", "IMMEDIATE", "EXCLUSIVE", or None
132
"""
133
```
134
135
Usage example:
136
137
```python
138
async with aiosqlite.connect("database.db") as db:
139
# Check default isolation level
140
print(f"Default isolation: {db.isolation_level}") # Usually "DEFERRED"
141
142
# Set autocommit mode (no transactions)
143
db.isolation_level = None
144
await db.execute("INSERT INTO users (name) VALUES (?)", ("Bob",))
145
# No need to commit in autocommit mode
146
147
# Set immediate locking for critical operations
148
db.isolation_level = "IMMEDIATE"
149
await db.execute("UPDATE accounts SET balance = balance + 100")
150
await db.commit()
151
152
# Set exclusive locking for maintenance operations
153
db.isolation_level = "EXCLUSIVE"
154
await db.execute("VACUUM")
155
await db.commit()
156
```
157
158
### Row and Text Factories
159
160
Configure how query results are processed and returned.
161
162
```python { .api }
163
@property
164
def row_factory(self) -> Optional[type]:
165
"""
166
Current row factory for query results.
167
168
Controls how result rows are constructed:
169
- None: Return rows as tuples (default)
170
- sqlite3.Row: Return rows as dict-like objects with column access
171
- Custom factory: User-defined row processing function
172
173
Returns:
174
Optional[type]: Current row factory class or None
175
"""
176
177
@row_factory.setter
178
def row_factory(self, factory: Optional[type]) -> None:
179
"""
180
Set row factory for query results.
181
182
Parameters:
183
- factory: Row factory class (e.g., sqlite3.Row) or None for tuples
184
"""
185
186
@property
187
def text_factory(self) -> Callable[[bytes], Any]:
188
"""
189
Current text factory for handling TEXT columns.
190
191
Controls how TEXT column values are decoded from bytes:
192
- str (default): Decode as UTF-8 strings
193
- bytes: Return raw bytes
194
- Custom factory: User-defined text processing function
195
196
Returns:
197
Callable: Function to convert bytes to text
198
"""
199
200
@text_factory.setter
201
def text_factory(self, factory: Callable[[bytes], Any]) -> None:
202
"""
203
Set text factory for TEXT column handling.
204
205
Parameters:
206
- factory: Function to convert bytes to desired text type
207
"""
208
```
209
210
Usage example:
211
212
```python
213
import sqlite3
214
import aiosqlite
215
216
async with aiosqlite.connect("database.db") as db:
217
# Use sqlite3.Row for dict-like access to columns
218
db.row_factory = sqlite3.Row
219
220
async with db.execute("SELECT id, name, email FROM users LIMIT 1") as cursor:
221
row = await cursor.fetchone()
222
if row:
223
print(f"ID: {row['id']}") # Access by column name
224
print(f"Name: {row['name']}") # Dict-like access
225
print(f"Email: {row[2]}") # Still supports index access
226
227
# Custom row factory for specific processing
228
def dict_factory(cursor, row):
229
return {description[0]: value
230
for description, value in zip(cursor.description, row)}
231
232
db.row_factory = dict_factory
233
234
# Configure text handling (usually not needed)
235
db.text_factory = str # Default UTF-8 decoding
236
```
237
238
### Change Tracking
239
240
Monitor database modification statistics.
241
242
```python { .api }
243
@property
244
def total_changes(self) -> int:
245
"""
246
Total number of database changes since connection opened.
247
248
Includes all INSERT, UPDATE, and DELETE operations.
249
Useful for monitoring database activity and change volume.
250
251
Returns:
252
int: Total change count since connection creation
253
"""
254
```
255
256
Usage example:
257
258
```python
259
async with aiosqlite.connect("database.db") as db:
260
initial_changes = db.total_changes
261
print(f"Initial changes: {initial_changes}")
262
263
# Perform some operations
264
await db.execute("INSERT INTO users (name) VALUES (?)", ("Charlie",))
265
await db.execute("UPDATE users SET name = ? WHERE name = ?", ("Charles", "Charlie"))
266
await db.execute("DELETE FROM users WHERE name = ?", ("OldUser",))
267
await db.commit()
268
269
final_changes = db.total_changes
270
print(f"Changes made: {final_changes - initial_changes}")
271
print(f"Total changes: {final_changes}")
272
```
273
274
275
## Error Handling
276
277
Transaction-related errors and their common causes:
278
279
```python
280
try:
281
async with aiosqlite.connect("database.db") as db:
282
await db.execute("BEGIN IMMEDIATE") # Explicit transaction start
283
# ... transaction operations
284
await db.commit()
285
286
except aiosqlite.OperationalError as e:
287
if "database is locked" in str(e):
288
# Handle database lock contention
289
await asyncio.sleep(0.1) # Brief backoff
290
# Retry logic here
291
else:
292
raise
293
294
except aiosqlite.IntegrityError as e:
295
# Handle constraint violations
296
await db.rollback() # Explicit rollback
297
print(f"Data integrity error: {e}")
298
```