View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0014036||FPC||Database||public||2009-06-23 21:45||2015-09-02 05:49|
|Reporter||Boguslaw Brandys||Assigned To||Joost van der Sluis|
|Status||resolved||Resolution||no change required|
|Platform||windows||OS||windows xp home|
|Summary||0014036: Tsqlite3connection set permanently active transaction|
|Description||When Tsqlquery connected to Tsqlite2connection is open and data is displayed on tdbgrid transaction is left open.I don't know if that's by purpose but for sqlite it means that no other external tool can change data in locked database when such application is running.This is not good.|
|Tags||No tags attached.|
|Fixed in Revision|
The problem might be related to used sql statement when multiword alias was used like :
select name as "Name of product" from table1
||Please provide a full compilable example|
||Last call before closing. Files being locked when the connection is open don't sound so strange in my ears anyway.|
This is common problem when somebody uses SQLite in these two cases:
1. sqlite database is accessed at same time by two processes on same computer
2. sqlite database is used at same time by two computers over network (it is not recommended, but IMO it is useful in some situations, when 2-3 computers need share same database, where only one writes, other reads)
SQLite releases file locks when active transaction commits and statements are unprepared.
See: http://www.sqlite.org/lang_transaction.html and http://www.sqlite.org/lockingv3.html
"An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized."
I have some plans to look at it, when I will have more free time.
My intention is provide some switch, which will enable finalization all statements when transactin will be commited.
I looked again at this and as far as defered (default) transactions are used, no lock are placed when transaction is started with BEGIN TRANSACTION:
"Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed."
( See: http://www.sqlite.org/lang_transaction.html )
Above mentioned problem can be solved by using "CommitRetaining" after read (SELECT) operation (it releases SHARED locks, so other connections can read/write into same database file).
(So I think, that it is by design how SQLite works and we can close this bug report.)
||Changed to resolved. Please check if the above advise works for you, and reopen otherwise|
|2009-06-23 21:45||Boguslaw Brandys||New Issue|
|2009-06-23 21:45||Boguslaw Brandys||Status||new => assigned|
|2009-06-23 21:45||Boguslaw Brandys||Assigned To||=> Joost van der Sluis|
|2009-06-27 15:55||Boguslaw Brandys||Note Added: 0028805|
|2010-07-21 09:19||Felipe Monteiro de Carvalho||Note Added: 0039534|
|2010-07-21 09:19||Felipe Monteiro de Carvalho||Status||assigned => feedback|
|2011-07-28 20:54||Marco van de Voort||Note Added: 0050243|
|2011-07-30 17:34||LacaK||Note Added: 0050292|
|2012-02-10 12:03||LacaK||Note Added: 0056715|
|2012-02-10 20:44||Marco van de Voort||Status||feedback => resolved|
|2012-02-10 20:44||Marco van de Voort||Resolution||open => no change required|
|2012-02-10 20:44||Marco van de Voort||Note Added: 0056743|