SQLite Forum

DB Access
Login

DB Access

(1) By anonymous on 2020-12-17 09:34:39 [link] [source]

Can an SQLite3 session lock a database i.e. make it inaccessible to, say, another SQLite3 session?

(2) By Gunter Hick (gunter_hick) on 2020-12-17 09:46:57 in reply to 1 [link] [source]

Why should it want to do that? Restricting access to files is a task for the operating system, not a database engine.

(5) By anonymous on 2020-12-17 10:27:07 in reply to 2 [link] [source]

Having opened a database with SQLite3, the operating system (Windows) does not see the file as being locked or in use, probably because SQLite allows concurrent access.

IS there any way I can determine if SQLite3 has a particular database open? That is, do do independently of SQLite3?

(6) By Gunter Hick (gunter_hick) on 2020-12-17 10:42:25 in reply to 5 [link] [source]

On a linux style system there would be the lsof (list open files) command and the /proc (process info) filesystem. I don't know the equivalent for windows, but since the resource monitor shows that information it must be accessible somehow.

But that is not an SQLIte but rather a Windows question.

(7) By Keith Medcalf (kmedcalf) on 2020-12-17 10:43:24 in reply to 5 [link] [source]

Windows knows perfectly well that the file is open and in-use. Why on earth do you think that Windows does not know that the file is open by some process?

Perhaps your tools are inadequate.

(9) By anonymous on 2020-12-17 10:52:37 in reply to 7 [link] [source]

See sessions described

The tool is adequate - it provides the expected status with other applications.

(3) By anonymous on 2020-12-17 09:54:03 in reply to 1 [link] [source]

Have a look at BEGIN EXCLUSIVE.

  • Donald Shepherd

(4) By Keith Medcalf (kmedcalf) on 2020-12-17 10:26:13 in reply to 1 [link] [source]

See perhaps pragma locking_mode=exclusive

(8) By anonymous on 2020-12-17 10:49:02 in reply to 4 [link] [source]

Thanks. In one session, I've got the following:

sqlite> .open ./db/chinook.db
sqlite> .databases
main: D:\SQLite32\db\chinook.db r/w
sqlite> PRAGMA main.locking_mode=EXCLUSIVE
   ...> ;
exclusive
sqlite> .databases
main: D:\SQLite32\db\chinook.db r/w
sqlite>

I opened a second session:

sqlite> .open ./db/chinook.db
sqlite> drop table if exists xyz;
sqlite> create table xyz(lock text);
sqlite> insert into xyz(lock) values('Is exclusive');
sqlite> .mode columns
sqlite> .headers on
sqlite> select * from xyz;
lock
------------
Is exclusive
sqlite>

The second session illustrates that the first session's exclusive mode did not stick. Am I mis-interpreting?

(10) By Gunter Hick (gunter_hick) on 2020-12-17 11:02:17 in reply to 8 [source]

"When the locking-mode is set to EXCLUSIVE, the database connection never releases file-locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held."

You need to read or write the database for SQLite to actually acquire (and never release) the locks.

(12) By anonymous on 2020-12-17 11:23:52 in reply to 10 [link] [source]

Thank you.

Session 1

sqlite> .open ./db/chinook.db
sqlite> pragma main.locking_mode=exclusive;
exclusive
sqlite> drop table if exists xyz;
sqlite>

Session 2

sqlite> .open ./db/chinook.db
sqlite> .databases
Error: database is locked
sqlite>

I got the error message NOT on .open(ing the database) but on querying .databases i.e. the list of open databases.

  1. Is there an API for exclusive lock?
  2. Any way of trapping the error during .open?

(14) By anonymous on 2020-12-17 12:03:08 in reply to 12 [link] [source]

I've resolved question 1.

(16.2) By Keith Medcalf (kmedcalf) on 2020-12-17 12:40:50 edited from 16.1 in reply to 12 [link] [source]

  1. No
  2. No

.open in the CLI application (or the underlying sqlite3_open* APIs) do not actually open the database -- they only prepare the connection data structure for use. The database file is only opened when you try and access it. Even then the lock is not detected because you do not actually try to acquire a lock at that time.

The message "database is locked" is because the SQL command "running behind" the .databases command attempted to access the database and therefore needed to acquire a read lock, and that lock acquisition was denied. It is not different in any way from any other time the "database is locked" (SQLITE_BUSY) message is presented to you when a lock acquisition fails.

Note that as far as the underlying Operating System is concerned the file is still merely opened for shared read/write access.

(11) By Keith Medcalf (kmedcalf) on 2020-12-17 11:06:24 in reply to 8 [link] [source]

Yes. The documentation for pragma locking_mode=exclusive says that when that mode is in effect that locks which are acquired are not released.

You did not acquire any locks, you merely set the "don't release any locks" flag but did not acquire any locks to be not released.

(13) By anonymous on 2020-12-17 11:43:35 in reply to 11 [link] [source]

:memory: databases are automatically exclusive. Correct?

(15.1) By Keith Medcalf (kmedcalf) on 2020-12-17 12:36:53 edited from 15.0 in reply to 13 [link] [source]

Since they can only be accessed by the single connection on which they are created the question is nonsensical.

However, according to the documentation, yes; according to testing, no. (When a temporary or :memory: database is the "main" database, that is, for the greater certainty).

The temp database is exclusive according to both documentation and testing, as are attached :memory: and temporary databases.

>sqlite :memory:
SQLite version 3.35.0 2020-12-12 19:04:51
Enter ".help" for usage hints.
sqlite> create table x(x);
sqlite> pragma locking_mode;
┌──────────────┐
│ locking_mode │
├──────────────┤
│ normal       │
└──────────────┘
sqlite> ^Z

>sqlite ""
SQLite version 3.35.0 2020-12-12 19:04:51
Enter ".help" for usage hints.
sqlite> create table x(x);
sqlite> pragma locking_mode;
┌──────────────┐
│ locking_mode │
├──────────────┤
│ normal       │
└──────────────┘
sqlite> ^Z

>sqlite
SQLite version 3.35.0 2020-12-12 19:04:51
Enter ".help" for usage hints.
sqlite> pragma temp.locking_mode;
┌──────────────┐
│ locking_mode │
├──────────────┤
│ exclusive    │
└──────────────┘
sqlite> ^Z

>sqlite
SQLite version 3.35.0 2020-12-12 19:04:51
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach ':memory:' as a;
sqlite> attach '' as b;
sqlite> pragma a.locking_mode;
┌──────────────┐
│ locking_mode │
├──────────────┤
│ exclusive    │
└──────────────┘
sqlite> pragma b.locking_mode;
┌──────────────┐
│ locking_mode │
├──────────────┤
│ exclusive    │
└──────────────┘
sqlite>