Delphi database is locked sqlite
|procedure TForm1.Button1Click(Sender: TObject); |
FDConnection1.ExecSQL(‘insert into bazatable values(«ROM», «Read Only Memory»)’);
но к сожалению программа выдаёт ошибку типа Database is Locked
Скажите пожалуйста, что мне с этим сделать?
А что за база то хоть ? Как только узнаешь, спроси у яндекса «delphi [название движка бд] Database is Locked»
Это сообщение отредактировал(а) _zorn_ — 20.2.2017, 14:47
|Цитата(SASFM @ 19.2.2017, 11:23 )|
|‘insert into bazatable values(«ROM», «Read Only Memory»)’|
|INSERT INTO bazatable (Value, Description) VALUES (‘ROM’, ‘Read Only Memory’)|
[ Время генерации скрипта: 0.0977 ] [ Использовано запросов: 21 ] [ GZIP включён ]
Delphi: Database is locked (SQLite)
I am facing two problems.
(1) When I try to write to a Database (SQLite) by using Delphi XE6, I always get the Database is locked error message. I am certain that I close the database everytime I access it by using the command FDConnection1.Close;
(2) How do I INSERT INTO a table from incoming parameters? I have the following incoming parameters
and tried to write into the table with the following SQL command:
but it does not seem to work.
The following is the complete procedure I am having trouble with
Any hints will be very appreciated. Thanks in advance.
2 Answers 2
There are two techniques to executing a dynamic SQL statement. But I’ll use a shorter SQL, to concentrate on the logic:
The pure way (using parameters)
The dirty way (building SQL)
The differences are significant. The dirty way exposes you to SQL injection problems (as in most other languages, when you build SQL dinamically but without parameters). This could be or not be a problem for you. If you know that the procedure is only called privately by your own code, and that those procedure parameter values can only contain good values. or if you do some good parameter checking before building and executing your SQL. then you are safe.
But if you do it with parameters (the pure way) you are automatically protected from SQL injection, as the SQL statement is validated by the engine, without knowing the parameter values. So the SQL statement structure is known by the engine and cannot be altered by the actual values.
Another consideration is how frequently you will execute that INSERT statement. The pure way allows you to prepare the query ONCE, and execute it MANY TIMES with different parameter values (you must not destroy the query object, nor change the SQL property, and you must call the Prepare method once). If you run it frequently within a loop then it can be more efficient than building the SQL many times the dirty way. OTOH if you just need to insert one single row, it may pose a bit more overhead.
As an aside. CL is right. those values should not be strings. Keep in mind that the Parameter object has many properties to handle different data types:
If you don’t use parameters, then things get difficult. The QuoteStr function is good for strings, but if you want to burn dates and currencies and other value types directly in your SQL you have to know what you are doing. You may encounter many different problems. locale specific or format settings that are not good for comunicating with your server, which may be on the opposite end of the world, or may just not be able to read values formatted that way. You may have to handle engine specific formatting and conversion problems.
If you do use parameters, then FireDAC should take care of all this for you 😉
How do I unlock a SQLite database?
How do I unlock the database so this will work?
38 Answers 38
In windows you can try this program http://www.nirsoft.net/utils/opened_files_view.html to find out the process is handling db file. Try closed that program for unlock database
In Linux and macOS you can do something similar, for example, if your locked file is development.db:
This command will show what process is locking the file:
Just kill the process.
. And your database will be unlocked.
I caused my sqlite db to become locked by crashing an app during a write. Here is how i fixed it:
The SQLite wiki DatabaseIsLocked page offers an explanation of this error message. It states, in part, that the source of contention is internal (to the process emitting the error). What this page doesn’t explain is how SQLite decides that something in your process holds a lock and what conditions could lead to a false positive.
Changes related to file locking introduced in v3 and may be useful for future readers and can be found here: File Locking And Concurrency In SQLite Version 3
If you want to remove a «database is locked» error then follow these steps:
Deleting the -journal file sounds like a terrible idea. It’s there to allow sqlite to roll back the database to a consistent state after a crash. If you delete it while the database is in an inconsistent state, then you’re left with a corrupted database. Citing a page from the sqlite site:
We suspect that a common failure mode for SQLite recovery happens like this: A power failure occurs. After power is restored, a well-meaning user or system administrator begins looking around on the disk for damage. They see their database file named «important.data». This file is perhaps familiar to them. But after the crash, there is also a hot journal named «important.data-journal». The user then deletes the hot journal, thinking that they are helping to cleanup the system. We know of no way to prevent this other than user education.
The rollback is supposed to happen automatically the next time the database is opened, but it will fail if the process can’t lock the database. As others have said, one possible reason for this is that another process currently has it open. Another possibility is a stale NFS lock, if the database is on an NFS volume. In that case, a workaround is to replace the database file with a fresh copy that isn’t locked on the NFS server (mv database.db original.db; cp original.db database.db). Note that the sqlite FAQ recommends caution regarding concurrent access to databases on NFS volumes, because of buggy implementations of NFS file locking.
I can’t explain why deleting a -journal file would let you lock a database that you couldn’t before. Is that reproducible?
By the way, the presence of a -journal file doesn’t necessarily mean that there was a crash or that there are changes to be rolled back. Sqlite has a few different journal modes, and in PERSIST or TRUNCATE modes it leaves the -journal file in place always, and changes the contents to indicate whether or not there are partial transactions to roll back.
If a process has a lock on an SQLite DB and crashes, the DB stays locked permanently. That’s the problem. It’s not that some other process has a lock.
the SQLite db files are just files, so the first step would be to make sure it isn’t read-only. The other thing to do is to make sure that you don’t have some sort of GUI SQLite DB viewer with the DB open. You could have the DB open in another shell, or your code may have the DB open. Typically you would see this if a different thread, or application such as SQLite Database Browser has the DB open for writing.
My lock was caused by the system crashing and not by a hanging process. To resolve this, I simply renamed the file then copied it back to its original name and location.
Using a Linux shell that would be:
I had this problem just now, using an SQLite database on a remote server, stored on an NFS mount. SQLite was unable to obtain a lock after the remote shell session I used had crashed while the database was open.
The recipes for recovery suggested above did not work for me (including the idea to first move and then copy the database back). But after copying it to a non-NFS system, the database became usable and not data appears to have been lost.
I added » Pooling=true » to connection string and it worked.
I found the documentation of the various states of locking in SQLite to be very helpful. Michael, if you can perform reads but can’t perform writes to the database, that means that a process has gotten a RESERVED lock on your database but hasn’t executed the write yet. If you’re using SQLite3, there’s a new lock called PENDING where no more processes are allowed to connect but existing connections can sill perform reads, so if this is the issue you should look at that instead.
Some functions, like INDEX’ing, can take a very long time — and it locks the whole database while it runs. In instances like that, it might not even use the journal file!
So the best/only way to check if your database is locked because a process is ACTIVELY writing to it (and thus you should leave it the hell alone until its completed its operation) is to md5 (or md5sum on some systems) the file twice. If you get a different checksum, the database is being written, and you really really REALLY don’t want to kill -9 that process because you can easily end up with a corrupt table/database if you do.
I’ll reiterate, because it’s important — the solution is NOT to find the locking program and kill it — it’s to find if the database has a write lock for a good reason, and go from there. Sometimes the correct solution is just a coffee break.
The only way to create this locked-but-not-being-written-to situation is if your program runs BEGIN EXCLUSIVE , because it wanted to do some table alterations or something, then for whatever reason never sends an END afterwards, and the process never terminates. All three conditions being met is highly unlikely in any properly-written code, and as such 99 times out of 100 when someone wants to kill -9 their locking process, the locking process is actually locking your database for a good reason. Programmers don’t typically add the BEGIN EXCLUSIVE condition unless they really need to, because it prevents concurrency and increases user complaints. SQLite itself only adds it when it really needs to (like when indexing).