Re: [sqlite] FTS5 MATCH using "string*" matches just the exact string
In Arabic you have a different direction of the text. Might be that the reason? Sandu Eric Minbiole schrieb am Di., 11. Sep. 2018, 00:23: > > SELECT rowid,text > > FROM table > > WHERE table MATCH 'أعلم*'; > > > > And I have to add this that my data is Arabic text. > > > > This method must find words that contains 'أعلم' but it doesn't. What > > should I do now. > > > > > I just tried this in SQLite version 3.24.0 on a Mac, and it seems to work. > Please see my example below. In particular, the first select doesn't have a > *, so it doesn't find the word. The second example includes a trailing *, > so it performs a "starts with" search, and does find the match. (Note that > I simply doubled the letters of your original arabic text to make a longer > word.) > > MacBook-II:Programs eric$ ./sqlite3 > > SQLite version 3.24.0 2018-06-04 19:24:41 > > Enter ".help" for usage hints. > > Connected to a transient in-memory database. > > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc); > > sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text'); > > sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم'); > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم'; > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*'; > > 2 > > sqlite> > > > > One possible explanation: You mentioned that you want a "contains" search. > However, the trailing asterisk in your example only results in a "begins > with" search. If you really want a "contains" search, you'll need to put > the * at both the beginning and end of the match word. E.g., "WHERE fts > MATCH '*asdf*'; > > Hope this is helpful. > > ~Eric > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CRUD Statistics
Hi, Is there a possibility to extract a statistic of how many CRUD operations do I have during an application life-cycle? I would like to find-out how many INSERTs do I have, how many SELECTs and so on. In particular I am focusing for multithreaded scenario, with multiple read/write operations simultaneously, using WAL. Thanks Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite lock granularity
Hi, For write operations the SQLite needs to lock the entire database file, using WAL the granularity is improved and the locks are at table level. Is it possible to get a better granularity, for example for a group of rows or even more to row level? I am asking this because I have scenario with one writer and 2 readers, and I am getting 'database table is locked' errors. The write statements: INSERT, INSERT OR UPDATE, DELETE are encapsulated in a transaction and I am using WAL mode. Regards Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In memory database - WAL
Hi, I am using WAL to read/write into a database from a single process with several threads. For performance reasons, along with the HDD database file, I am attaching an in memory db for storing temporary information. Each thread has it own handle. My question is: once attached the in memory db will be visible for all the handles? Regards Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database table is locked issue
> > I have a process with several threads working in the same time on a > > database file. I have 0 or 1 writers and 0 or N readers at a moment. All > > write accesses are isolated in transactions, I am using WAL and shared > > cache, but sometimes during the DELETE statemens I get "database table is > > locked" errors, either in the write or read thread. > Have you set a timeout value for all your connections ? If you haven't, SQLite never does any waiting for locks at all, it just returns an error. You were right the busy timeout was set only for one of the connections. But, anyway the table lock continued to be triggered immediately not waiting to pass 2500 ms. I thought that WAL should've solved this aspect about the concurrency, but it seems that for DELETE statements it still needs to lock the table somehow. Regards Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database table is locked issue
Hi, I have a process with several threads working in the same time on a database file. I have 0 or 1 writers and 0 or N readers at a moment. All write accesses are isolated in transactions, I am using WAL and shared cache, but sometimes during the DELETE statemens I get "database table is locked" errors, either in the write or read thread. Since the new content is not committed yet, why I am getting this errors? My expectations would've been that during the end transaction to have such errors, but not long before. Regards, Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Enable WAL on a QNX system
> If you want to have more than one connection to the database from within > the same process (but not multiple processes) and still use WAL without > shared-memory, using VFS "unix-excl" instead of the default might work. With the default VFS for QNX - "unix" the WAL did not worked, using "unix-excl" worked fine. All the connections are used within the same process (one connection per thread), the data structures used to synchronize the readers/writers will be placed in heap, instead of shared-memory. It should be better also for performance. Of course the database file will be locked, and cannot be used by other process. Maybe the WAL documentation should be extended with a note for QNX. Regards, Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enable WAL on a QNX system
> It's locked into EXCLUSIVE mode if it was in EXCLUSIVE mode when you switched to WAL. But > there's nothing requiring it. I was not referring to the locking_mode, I was writing about the lock itself over the database (iNode structure). On QNX the exclusive locking_mode it is required, because it seems that shared memory is not working properly. The entry point when enabling WAL: JOURNALMODE_WAL will be enabled (a trace added by me) LOCK11 SHARED was NONE(NONE,0) pid=229454 tid=20 (unix) fcntl pid=229454 tid=20 11 SETLK RDLCK 1073741824 1 125195676 0 fcntl pid=229454 tid=20 11 SETLK RDLCK 1073741826 510 125195676 0 fcntl pid=229454 tid=20 11 SETLK UNLCK 1073741824 1 125195676 0 LOCK11 SHARED ok (unix) READ11 16 24 0 LOCK11 RESERVED was SHARED(SHARED,1) pid=229454 tid=20 (unix) fcntl pid=229454 tid=20 11 SETLK WRLCK 1073741825 1 125195740 0 LOCK11 RESERVED ok (unix) LOCK11 EXCLUSIVE was RESERVED(RESERVED,1) pid=229454 tid=20 (unix) fcntl pid=229454 tid=20 11 SETLK WRLCK 1073741824 1 125195708 0 fcntl pid=229454 tid=20 11 SETLK WRLCK 1073741826 510 125195708 0 LOCK11 EXCLUSIVE ok (unix) TRANSACTION 139542152 OPENX 12 /mnt/share/testApp/myDB.nal 0402 fcntl unknown pid=229454 tid=20 12 1 0 fcntl unknown pid=229454 tid=20 12 2 0 OPEN12 /mnt/share/testApp/myDB.nal WRITE 12512 0 0 WRITE 12 4 512 0 WRITE 12 65536 516 0 WRITE 12 4 66052 0 JOURNAL 139542152 page 1 needSync=1 hash() Filename: /mnt/share/testApp/myDB.db State: WRITER_CACHEMOD errCode=0 Lock: EXCLUSIVE Locking mode: locking_mode=exclusive Journal mode: journal_mode=wal Backing store: tempFile=0 memDb=0 useJournal=1 Journal: journalOff=66056 journalHdr=0 Size: dbsize=35 dbOrigSize=35 dbFileSize=35 DATABASE SYNC: File=/mnt/share/testApp/myDB.db zMaster= nSize=35 READ12 0 66560 0 SYNC journal of 139542152 SYNC12 DIRSYNC /mnt/share/testApp/myDB.nal (have_fullfsync=0 fullsync=0) fcntl unknown pid=229454 tid=20 13 1 0 fcntl unknown pid=229454 tid=20 13 2 0 OPENDIR 13 /mnt/share/nav WRITE 12 12 0 0 SYNC journal of 139542152 SYNC12 READ11 4 24 0 WRITE 11 65536 0 0 STORE 139542152 page 1 hash() SYNC11 COMMIT 139542152 CLOSE -1 So far no UNLOCK... it continues opening four more connections to same db: OPENX 12 /mnt/share/testApp/myDB.db 02 fcntl unknown pid=229454 tid=20 12 1 0 fcntl unknown pid=229454 tid=20 12 2 0 OPEN12 /mnt/share/testApp/myDB.db OPEN 139532984 /mnt/share/testApp/myDB.db OPEN 139506232 /mnt/share/testApp/myDB.db OPENX 13 /mnt/share/testApp/myDB.db 02 fcntl unknown pid=229454 tid=20 13 1 0 fcntl unknown pid=229454 tid=20 13 2 0 OPEN13 /mnt/share/testApp/myDB.db OPEN 139543832 /mnt/share/testApp/myDB.db OPEN 139505000 /mnt/share/testApp/myDB.db OPENX 14 /mnt/share/testApp/myDB.db 02 fcntl unknown pid=229454 tid=20 14 1 0 fcntl unknown pid=229454 tid=20 14 2 0 OPEN14 /mnt/share/testApp/myDB.db OPEN 139531896 /mnt/share/testApp/myDB.db OPEN 139524696 /mnt/share/testApp/myDB.db OPENX 15 /mnt/share/testApp/myDB.db 02 fcntl unknown pid=229454 tid=20 15 1 0 fcntl unknown pid=229454 tid=20 15 2 0 OPEN15 /mnt/share/testApp/myDB.db OPEN 139501056 /mnt/share/testApp/myDB.db OPEN 139501688 /mnt/share/testApp/myDB.db Next read requests - SHARED locks will fail: LOCK12 SHARED was NONE(EXCLUSIVE,1) pid=229454 tid=16 (unix) LOCK12 SHARED failed (unix) UNLOCK 12 0 was 0(4,1) pid=229454 tid=16 (unix) ROLLBACK 139532984 LOCK12 SHARED was NONE(EXCLUSIVE,1) pid=229454 tid=20 (unix) LOCK12 SHARED failed (unix) UNLOCK 12 0 was 0(4,1) pid=229454 tid=20 (unix) ROLLBACK 139532984 LOCK12 SHARED was NONE(EXCLUSIVE,1) pid=229454 tid=20 (unix) LOCK12 SHARED failed (unix) UNLOCK 12 0 was 0(4,1) pid=229454 tid=20 (unix) ROLLBACK 139532984 LOCK12 SHARED was NONE(EXCLUSIVE,1) pid=229454 tid=20 (unix) LOCK12 SHARED failed (unix) UNLOCK 12 0 was 0(4,1) pid=229454 tid=20 (unix) ROLLBACK 139532984 LOCK12 SHARED was NONE(EXCLUSIVE,1) pid=229454 tid=20 (unix) LOCK12 SHARED failed (unix) UNLOCK 12 0 was 0(4,1) pid=229454 tid=20 (unix) ROLLBACK 139532984 So, the same process, it is clear that no other process/thread is accessing the database. Why the EXCLUSIVE lock acquired during the WAL enabling is not released? Regards, Sandu Buraga ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enable WAL on a QNX system
> > I am starting to believe that WAL is not a viable choice on a QNXsystem. > > > All Blackberry phones and tables use it. It may be so, all Blackberry devices may be using WAL, but on my QNX system I just could not make it work. I haven't made a absolute statement, about SQLite and QNX, I just wrote a personal thought. I noticed that when PRAGMA main.journal_mode=WAL; statement is executed, the database file is locked in EXCLUSIVE mode, most likely to prevent the access from other processes/threads while the journal_mode is persisted into the database file. Later this EXCLUSIVE lock is never released. The next locks requests will fail. Regards, Sandu Buraga ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enable WAL on a QNX system
For a QNX system Richard suggested to set locking mode EXCLUSIVE before trying to set the WAL journal mode. When PRAGMA main.journal_mode=WAL is executed, the sqlite code checks whether EXCLUSIVE lock is enabled, or if shared memory is supported. The shared-memory implementation from sqlite is based on POSIX API. The QNX OS is not 100% POSIX compliant even if the company behind says so. If it is not set locking-mode EXCLUSIVE, WAL will not be enabled on a QNX system, since there is not shared memory support. For the SQLITE_OPEN_WAL you are right, it makes no difference. I am starting to believe that WAL is not a viable choice on a QNX system. Regards, Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)
Hi Richard, Yes, only one thread is working on the database first time. So even if I am opening more handles, I work only with one of the handles to run some PRAGMA statements and to check the structural integrity of database. If tables, or columns are missing I will run some DROP TABLE IF EXISTS statements, and CREATE TABLE within a transaction. The pseudo-code would be: 1. open 1 handle 2. on handle 1 - perform one SQL SELECT statement 3. on handle 1 - start a transaction and create some tables 4.a. On handle 1 - PRAGMA temp_store=MEMORY 4.b. On handle 1 - PRAGMA locking_mode=EXCLUSIVE 4.c. On handle 1 - PRAGMA main.journal_mode=WAL 5. open another 4 db connections If step 4 would be executed before step 2, I have immediately after BEGIN TRANSACTION a database lock. But, after I enable the WAL mode the consequent transactions (in any other threads) will go in database lock. I can include the SQLite traces. The code used to open the the database is: int res = sqlite3_open_v2( m_FilePath.getBuffer(), , SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_WAL|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE, NULL ); and for the next four handles: res = sqlite3_open_v2( m_FilePath.getBuffer(), , SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE, NULL ); Regards, Sandu Buraga >> On Mon, Nov 11, 2013 at 12:33 PM, Sandu Buraga <sandu.bur...@gmail.com>wrote: >> >>> Hi, >>> >>> I took your advice and now I am setting only once the journal_mode=WAL, >>> also locking_mode=EXCLUSIVE and temp_store=MEMORY. But still it doesn't >>> work, and I get DB locks immediately. I enabled SQLite traces, and I also >>> added some supplementary traces when the WAL is set. Even if the >>> journal_mode=WAL pragma seems to be successful, I don't think that the WAL >>> works because I see no wal file on the disk, also I should have some WAL >>> related traces, which are not present. >>> >> >> There can only be a single connection open on the database file when you >> change it to WAL mode. Did you try to change to WAL mode while holding >> multiple connections open? >> >> >The above is not quite correct. >There can be multiple connections open, but none of the other connections >can have any kind of lock. Are you sure that you don't have another thread >trying to read the database file when you enter WAL mode? The easiest way >to ensure this is to put the database in WAL mode when the first thread >connects, and before any other threads have even opened. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)
Hi, It is strange because it seems that the WAL journal mode was enabled: Trace 1: before setting WAL mode = Filename: /mnt/share/testApp/myDB.db State: WRITER_CACHEMOD errCode=0 Lock: EXCLUSIVE Locking mode: locking_mode=exclusive Journal mode: journal_mode=delete Backing store: tempFile=0 memDb=0 useJournal=1 Journal: journalOff=66056 journalHdr=0 Size: dbsize=35 dbOrigSize=35 dbFileSize=35 Trace 2: after setting WAL mode = Filename: /mnt/share/testApp/myDB.db State: WRITER_CACHEMOD errCode=0 Lock: EXCLUSIVE Locking mode: locking_mode=exclusive Journal mode: journal_mode=wal Backing store: tempFile=0 memDb=0 useJournal=1 Journal: journalOff=66056 journalHdr=0 Size: dbsize=35 dbOrigSize=35 dbFileSize=35 So why do we have EXCLUSIVE LOCK requests on the database file? Regards, Sandu On Mon, Nov 11, 2013 at 6:33 PM, Sandu Buraga <sandu.bur...@gmail.com>wrote: > Hi, > > I took your advice and now I am setting only once the journal_mode=WAL, > also locking_mode=EXCLUSIVE and temp_store=MEMORY. But still it doesn't > work, and I get DB locks immediately. I enabled SQLite traces, and I also > added some supplementary traces when the WAL is set. Even if the > journal_mode=WAL pragma seems to be successful, I don't think that the WAL > works because I see no wal file on the disk, also I should have some WAL > related traces, which are not present. > > Below are the SQLite traces, and it can be seen that 3.x locking model it > is used, instead of WAL. Somewhere a thread tries to get a READ SHARED > lock, but on the database there is already an EXCLUSIVE lock. Normally this > should not occur, because the reader should be able to use the database and > WAL file. The auto-checkpoint for the WAL failed is 1000 pages, so it > should not be immediately. > > > OPENX 11 /mnt/share/testApp/myDB.db 0402 > fcntl unknown pid=233565 tid=20 11 1 0 > fcntl unknown pid=233565 tid=20 11 2 0 > OPEN11 /mnt/share/testApp/myDB.db > OPEN 139631224 /mnt/share/testApp/myDB.db > OPEN 139631856 /mnt/share/testApp/myDB.db > OPENX 12 /mnt/share/testApp/myDB.db 02 > fcntl unknown pid=233565 tid=20 12 1 0 > fcntl unknown pid=233565 tid=20 12 2 0 > OPEN12 /mnt/share/testApp/myDB.db > OPEN 139746432 /mnt/share/testApp/myDB.db > OPEN 139627904 /mnt/share/testApp/myDB.db > OPENX 13 /mnt/share/testApp/myDB.db 02 > fcntl unknown pid=233565 tid=20 13 1 0 > fcntl unknown pid=233565 tid=20 13 2 0 > OPEN13 /mnt/share/testApp/myDB.db > OPEN 139547936 /mnt/share/testApp/myDB.db > OPEN 139811888 /mnt/share/testApp/myDB.db > OPENX 14 /mnt/share/testApp/myDB.db 02 > fcntl unknown pid=233565 tid=20 14 1 0 > fcntl unknown pid=233565 tid=20 14 2 0 > OPEN14 /mnt/share/testApp/myDB.db > OPEN 139943040 /mnt/share/testApp/myDB.db > OPEN 139544616 /mnt/share/testApp/myDB.db > OPENX 15 /mnt/share/testApp/myDB.db 02 > fcntl unknown pid=233565 tid=20 15 1 0 > fcntl unknown pid=233565 tid=20 15 2 0 > OPEN15 /mnt/share/testApp/myDB.db > OPEN 139585952 /mnt/share/testApp/myDB.db > OPEN 139586584 /mnt/share/testApp/myDB.db > LOCK11 SHARED was NONE(NONE,0) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741824 1 125195948 0 > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741826 510 125195948 0 > fcntl pid=233565 tid=20 11 SETLK UNLCK 1073741824 1 125195948 0 > LOCK11 SHARED ok (unix) > UNLOCK 11 0 was 1(1,1) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK UNLCK 0 0 138882344 0 > CLOSE 139631224 > UNLOCK 11 0 was 0(0,0) pid=233565 tid=20 (unix) > CLOSE -1 > OPENX 11 /mnt/share/testApp/myDB.db 0402 > fcntl unknown pid=233565 tid=20 11 1 0 > fcntl unknown pid=233565 tid=20 11 2 0 > OPEN11 /mnt/share/testApp/myDB.db > LOCK11 SHARED was NONE(NONE,0) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741824 1 138848960 0 > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741826 510 138848960 0 > fcntl pid=233565 tid=20 11 SETLK UNLCK 1073741824 1 138848960 0 > LOCK11 SHARED ok (unix) > UNLOCK 11 0 was 1(1,1) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK UNLCK 0 0 1 0 > LOCK11 SHARED was NONE(NONE,0) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741824 1 125196700 0 > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741826 510 125196700 0 > fcntl pid=233565 tid=20 11 SETLK UNLCK 1073741824 1 125196700 0 > LOCK11 SHARED ok (unix) > UNLOCK 11 0 was 1(1,1) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK UNLCK 0 0 2 0 > LOCK11 SHARED was NONE(NONE,0) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741824 1 125196428 0 > fcntl pid=233565 t
Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)
tid=20 11 SETLK RDLCK 1073741826 510 125195932 0 fcntl pid=233565 tid=20 11 SETLK UNLCK 1073741824 1 125195932 0 LOCK11 SHARED ok (unix) READ11 16 24 0 LOCK11 RESERVED was SHARED(SHARED,1) pid=233565 tid=20 (unix) fcntl pid=233565 tid=20 11 SETLK WRLCK 1073741825 1 125195996 0 LOCK11 RESERVED ok (unix) LOCK11 EXCLUSIVE was RESERVED(RESERVED,1) pid=233565 tid=20 (unix) fcntl pid=233565 tid=20 11 SETLK WRLCK 1073741824 1 125195964 0 fcntl pid=233565 tid=20 11 SETLK WRLCK 1073741826 510 125195964 0 LOCK11 EXCLUSIVE ok (unix) TRANSACTION 139631856 OPENX 16 /mnt/share/testApp/myDB.nal 0402 fcntl unknown pid=233565 tid=20 16 1 0 fcntl unknown pid=233565 tid=20 16 2 0 OPEN16 /mnt/share/testApp/myDB.nal WRITE 16512 0 0 WRITE 16 4 512 0 WRITE 16 65536 516 0 WRITE 16 4 66052 0 JOURNAL 139631856 page 1 needSync=1 hash() DATABASE SYNC: File=/mnt/share/testApp/myDB.db zMaster= nSize=35 READ16 0 66560 0 SYNC journal of 139631856 SYNC16 DIRSYNC /mnt/share/testApp/myDB.nal (have_fullfsync=0 fullsync=0) fcntl unknown pid=233565 tid=20 17 1 0 fcntl unknown pid=233565 tid=20 17 2 0 OPENDIR 17 /mnt/share/testApp WRITE 16 12 0 0 SYNC journal of 139631856 SYNC16 READ11 4 24 0 WRITE 11 65536 0 0 STORE 139631856 page 1 hash() SYNC11 COMMIT 139631856 CLOSE -1 LOCK13 SHARED was NONE(EXCLUSIVE,1) pid=233565 tid=16 (unix) LOCK13 SHARED failed (unix) UNLOCK 13 0 was 0(4,1) pid=233565 tid=16 (unix) ROLLBACK 139547936 LOCK13 SHARED was NONE(EXCLUSIVE,1) pid=233565 tid=20 (unix) LOCK13 SHARED failed (unix) UNLOCK 13 0 was 0(4,1) pid=233565 tid=20 (unix) ROLLBACK 139547936 LOCK13 SHARED was NONE(EXCLUSIVE,1) pid=233565 tid=20 (unix) LOCK13 SHARED failed (unix) UNLOCK 13 0 was 0(4,1) pid=233565 tid=20 (unix) ROLLBACK 139547936 LOCK13 SHARED was NONE(EXCLUSIVE,1) pid=233565 tid=20 (unix) LOCK13 SHARED failed (unix) UNLOCK 13 0 was 0(4,1) pid=233565 tid=20 (unix) ROLLBACK 139547936 LOCK13 SHARED was NONE(EXCLUSIVE,1) pid=233565 tid=20 (unix) LOCK13 SHARED failed (unix) UNLOCK 13 0 was 0(4,1) pid=233565 tid=20 (unix) ROLLBACK 139547936 Regards, Sandu Buraga Date: Thu, 31 Oct 2013 14:05:17 -0400 From: Richard Hipp <d...@sqlite.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Enable WAL on a QNX system Message-ID:
[sqlite] Enable WAL on a QNX system
Hi, I am using SQLite 3.7.9, and QNX 6.5.0 running on Intel x86 machine. I am having a use-case with multiple readers threads and one writer thread, all running in the same process. No other process is using the SQLite database file. I tried to enable WAL feature, but with no success so far. Each thread has it's own handle: res = sqlite3_open_v2( m_FilePath.getBuffer(), , SQLITE_OPEN_READWRITE|SQLITE_OPEN_WAL|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE, NULL ); immediately after I am making these PRAGMA statements for the new opened handle "PRAGMA main.journal_mode=WAL;" "PRAGMA temp_store=MEMORY;" So if I have 5 threads, I am calling the above sequence 5 times for each distinct handle When the code is executed I am getting frequently "databed locked" messages. I tried to diagnose the issue, by enabling also the WAL traces in the SQLite amalgamation, but I noticed no message, so for me it looks like the WAL feature was not enabled. By analyzing the existed traces it looks like thread 2 - reader is getting a SHARED lock, while later process 3 - writer is trying to get an EXCLUSIVE lock. Any suggestion would be appreciated. Regards, Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
