Day 3 - Issue of "database locked" with SQLite

To See All Articles About Technology: Index of Lessons in Technology

How to resolve: sqlite3.OperationalError: database is locked

The "database is locked" error in SQLite occurs when a database file is accessed by multiple processes or connections simultaneously, causing contention. Here’s how to resolve it:

---

🔍 Possible Causes & Fixes

1. Ensure Proper Connection Closing

If you're using Python, ensure that the database connection is closed properly after executing queries.

python
import sqlite3

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

# Execute your queries
cursor.execute("SELECT * FROM my_table;")

# Commit and close the connection
conn.commit()
conn.close()  # ✅ Always close the connection

👉 Fix: Always call conn.close() after executing queries.

---

2. Check for Unclosed Transactions

If a transaction (like INSERT, UPDATE, or DELETE) is left open without COMMIT, SQLite keeps the database locked.

sql
COMMIT;  -- Ensures any open transaction is completed

👉 Fix: Run COMMIT; in your SQL tool or application.

---

3. Close Other Processes Using the Database

Run the following SQL command in your SQLite session:

sql
PRAGMA busy_timeout = 5000;  -- Waits up to 5 seconds before failing

👉 Fix:

• If another process is using the DB file, close it (e.g., close DB Browser for SQLite, Python scripts, or any other app accessing the file).
• Restart your SQLite application.

---

4. Delete the .db-journal or .db-wal File

If SQLite crashed or was improperly closed, a journal file may still be locking the database.

1. Navigate to your database folder
2. Delete files ending in:
   • my_database.db-journal
   • my_database.db-wal

👉 Fix: Remove these files safely if no other process is using the database.

---

5. Open SQLite in Read-Write Mode

If another process is holding a write lock, try opening the DB in read-only mode:

python
conn = sqlite3.connect("file:my_database.db?mode=ro", uri=True)

👉 Fix: Use mode=ro if you don’t need to write to the database.

---

6. Kill Hanging Processes (Windows/Linux)

On Windows, find processes locking the file:

sh
tasklist | findstr sqlite

Kill a process using:

sh
taskkill /F /PID <process_id>

On Linux/macOS, find processes:

sh
lsof my_database.db

Kill the process:

sh
kill -9 <process_id>

👉 Fix: Restart any process holding the database.

---

🚀 Best Practices to Avoid "Database Locked" Issues

✅ Use PRAGMA busy_timeout:

sql
PRAGMA busy_timeout = 5000;  -- Waits 5 sec before failing

✅ Use a Single Database Connection
Avoid opening multiple connections in the same script.

✅ Close the Connection Properly
Always call conn.close() after database operations.

✅ Use WAL Mode for Better Concurrency
Enable Write-Ahead Logging (WAL) for better handling of multiple connections:

sql
PRAGMA journal_mode = WAL;

---

Try these fixes, and let me know if the issue persists! 🚀

... ... ... ... ... ... ... ... ... ... ... ...

print the pid for "ps aux | grep sqlite"

To print only the PID of processes related to SQLite using ps aux | grep sqlite, use this command:

sh
ps aux | grep sqlite | awk '{print $2}'

Explanation:

• ps aux → Lists all running processes.
• grep sqlite → Filters processes containing "sqlite".
• awk '{print $2}' → Extracts and prints only the PID (which is in the second column).

Alternative (More Precise)

If you want to exclude the grep command itself from the results:

sh
ps aux | grep '[s]qlite' | awk '{print $2}'

Example Output:

yaml
1234
5678

These are the PIDs of processes running SQLite.

👉 To kill a process, use:

sh
kill -9 <PID>

Let me know if you need more help! 🚀

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

ps -ef what does it do?

The ps -ef command in Linux is used to display a detailed list of all running processes on the system.

---

🔍 Breakdown of ps -ef

• ps → Shows active processes.
• -e → Displays all processes (not just those owned by the current user).
• -f → Provides full format output (more details).

---

📌 Example Output

sh
UID       PID   PPID  C STIME TTY          TIME CMD
root       1      0   0 Feb24 ?        00:01:23 /sbin/init
user1   1050     1   0 10:30 ?        00:00:00 /usr/bin/gnome-session
user1   2080  1050   0 10:31 ?        00:00:01 /usr/bin/firefox

---

📌 Column Explanation

Column	Meaning
UID	User who started the process
PID	Process ID
PPID	Parent Process ID (which process started this one)
C	CPU usage percentage
STIME	Process start time
TTY	Terminal associated with the process (if any)
TIME	CPU time used
CMD	Command that started the process

---

🔍 Useful Variations

✅ Show only processes of a specific user:

sh
ps -ef | grep username

✅ Find a specific process (e.g., SQLite):

sh
ps -ef | grep sqlite

✅ Show hierarchical (parent-child) relationships:

sh
ps -ef --forest

---

Let me know if you need more details! 🚀😊