Hacker News new | ask | show | jobs
by code-scope 1913 days ago
I use a different pattern. A lot sqlite files for diff purposes (UserSession, User Files, etc each store in separate files) This way diff threads of webserver can open/query/read/write a lot of files concurrently without any issue.
2 comments
saalweachter 1913 days ago
I, too, enjoy creating deadlocks.

[I assume if you are using this pattern successfully in production you are already aware of and taking proper steps to avoid them, but the pedant in me takes issue with "without any issue", since once you have multiple resources being locked in multiple threads, you need to be careful to acquire and release locks in such a way that deadlocks do not occur, either never acquiring more than one lock at a time, acquiring locks in specific orders, or acquiring batches of locks at a time.]

link
snicker7 1913 days ago
> without any issue

It's easier than you think to corrupt SQLite if you access from multiple threads and especially from multiple processes (yup, I've done this before).

Also, there are no concurrent transactions in SQLite. The entire db file gets locked (using POSIX locking, which is known to be broken [0]). Better to queue/batch transactions on a single connection. If your web server consists of multiple processes, then this requires a separate daemon.

[0]: http://0pointer.de/blog/projects/locking.html

link