Others have reported that since version 3, LR has locked the .lrcat catalog file, e.g. see: Lightroom SDK: Plugin API to unlock database temporarily. What others have done in the past is have their plugin exit LR, run a batch script that accesses the .lrcat file, then restart LR. That takes a little hackery with keyboard stuffing but can be made reasonably reliable.
I vaguley recall in past years that LR locked the .lrcat file at the OS level, so that no other program could even read or copy the file while LR had it open. But I can't find the relevant threads right now. However, a quick test with CC 2015.3 on Windows 10 and OS X 10.11 shows that it is possible to copy the .lrcat file while LR is running. So one approach is to have your plugin copy the .lrcat file (and the .lrcat-journal file, which will contain recent changes to the database) to a temporary location and then open the copy for SQL access.
Thinking about it, this hack may not always give you read-consistent access to the database. I think if you copy the .lrcat-journal file first and then .lrcat file, you're more likely to get a consistent point-in-time snapshot. But without some deeper thought and understanding of SQLite maintains its journal, it could be risky (give bad results).
Absent an authoritative examination of how SQLite maintains its journal, exiting LR before accessing the .lrcat file is certainly the conservative way to go.
thanks for your fast reply. I also read something that the lrcat was already locked for Lightroom 3. However we tested it with Lightroom 5 and 6 under OS X 10.9 and 10.10 successfully. Unfortunately it doesn't work under OS X 10.11 anymore :-(
We also thought about copying the lrcat, but this could be very inefficient if the catalog size is bigger than several 100MBs or GB.
Your second solution (exiting Lightroom before accessing the lrcat) is also not very performant, because we need to access the lrcat at many times. This will be result in an infinite loop of closing and opening Lightroom again :-(
Or maybe I don't understand whats done by the batch script exactly ... May it just stores another copy of the lrcat?!?
I agree with your assessment of both those approaches -- they both have significant drawbacks. I haven't seen any better suggestions, though, other than to try to figure out how to avoid accessing the catalog via SQL.
There is a workaround to have read access to .lrcat database.
In a terminal, with Lightroom not opened:
— $ sqlite3 your/lightroom/catalog.lrcat
— sqlite> pragma journal_mode=WAL;
sqlite may say "wal"
— keep this terminal opened and launch Lightroom with your catalog
— after Lightroom launched, sqlite can be exited
This bug seems to be provided by the version of libsqlite3 bundled in OSX. I've tried first to replace it (eg. /usr/lib/libsqlite3.dylib) with a newer version but the OS became unusable. I've also tried to set environments variables but Lightroom seems to be insensitive: it use invariably the OSX bundled version.