View Issue Details

IDProjectCategoryView StatusLast Update
0014036FPCDatabasepublic2015-09-02 05:49
ReporterBoguslaw Brandys Assigned ToJoost van der Sluis  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionno change required 
PlatformwindowsOSwindows xp home 
Product Version2.3.1 
Summary0014036: Tsqlite3connection set permanently active transaction
DescriptionWhen 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.
TagsNo tags attached.
Fixed in Revision
FPCOldBugId
FPCTarget
Attached Files

Activities

Boguslaw Brandys

2009-06-27 15:55

reporter   ~0028805

The problem might be related to used sql statement when multiword alias was used like :

select name as "Name of product" from table1

Felipe Monteiro de Carvalho

2010-07-21 09:19

developer   ~0039534

Please provide a full compilable example

Marco van de Voort

2011-07-28 20:54

manager   ~0050243

Last call before closing. Files being locked when the connection is open don't sound so strange in my ears anyway.

LacaK

2011-07-30 17:34

developer   ~0050292

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.

LacaK

2012-02-10 12:03

developer   ~0056715

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.)

Marco van de Voort

2012-02-10 20:44

manager   ~0056743

Changed to resolved. Please check if the above advise works for you, and reopen otherwise

Issue History

Date Modified Username Field Change
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