[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
On 11/20/2013 12:20 AM, Sandu Buraga wrote: 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. ... 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? Because "PRAGMA locking_mode=EXCLUSIVE" is set. That's what the PRAGMA does - causes the SQLite connection to hold on to an EXCLUSIVE lock on the database until it is closed. That it also makes it possible to use WAL without shared memory is a happy coincidence. 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. Dan. ___ 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
On 19 Nov 2013, at 4:24pm, Sandu Buraga wrote: > I noticed that when PRAGMA main.journal_mode=WAL; statement is executed, > the database file is locked in EXCLUSIVE mode, It's locked into EXCLUSIVE mode if it was in EXCLUSIVE mode when you switched to WAL. But there's nothing requiring it. Simon. ___ 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
On Thu, Nov 14, 2013 at 4:42 AM, Sandu Buraga wrote: > I am starting to believe that WAL is not a viable choice on a QNX system. > All Blackberry phones and tables use it. -- D. Richard Hipp d...@sqlite.org ___ 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)
On 13 Nov 2013, at 4:04pm, Sandu Buraga wrote: > But, after I enable the WAL mode the > consequent transactions (in any other threads) will go in database lock. WAL mode is saved with the database. Once you have enabled WAL mode, that setting is stored with the database for all time until you use another PRAGMA command to change it. Anything else opening the database will notice that it's already in WAL mode. > PRAGMA locking_mode=EXCLUSIVE Setting the journal mode to WAL while the locking-mode is EXCLUSIVE locks the current locking mode. You cannot change it again afterwards without switching back out of WAL mode. As a consequence of the above neither commands 4.b nor 4.c have any effect. I suspect that it's your use of EXCLUSIVE mode that's causing your problem. So please remove this setting, and make sure it's removed by checking PRAGMA locking_mode; and then try your test again and see if you're still getting unwanted locking. > The code used to open the the database is: > > int res = sqlite3_open_v2( m_FilePath.getBuffer(), &dbHandle, > SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_WAL|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE, > NULL ); According to the documentation the SQLITE_OPEN_WAL constant is for the VFS only. I don't know it'll do any harm but I don't know it does anything. Simon. ___ 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(), &dbHandle, 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(), &secondaryDbHandle, SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE, NULL ); Regards, Sandu Buraga >> On Mon, Nov 11, 2013 at 12:33 PM, Sandu Buraga 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)
ORE 139631856 page 18 hash() > WRITE 11 65536 1179648 0 > STORE 139631856 page 19 hash() > WRITE 11 65536 1245184 0 > STORE 139631856 page 20 hash() > WRITE 11 65536 1310720 0 > STORE 139631856 page 21 hash() > WRITE 11 65536 1376256 0 > STORE 139631856 page 22 hash() > WRITE 11 65536 1441792 0 > STORE 139631856 page 23 hash() > WRITE 11 65536 1507328 0 > STORE 139631856 page 24 hash() > WRITE 11 65536 1572864 0 > STORE 139631856 page 25 hash() > WRITE 11 65536 1638400 0 > STORE 139631856 page 26 hash() > WRITE 11 65536 1703936 0 > STORE 139631856 page 27 hash() > WRITE 11 65536 1769472 0 > STORE 139631856 page 28 hash() > WRITE 11 65536 1835008 0 > STORE 139631856 page 29 hash() > WRITE 11 65536 1900544 0 > STORE 139631856 page 30 hash() > WRITE 11 65536 1966080 0 > STORE 139631856 page 31 hash() > WRITE 11 65536 2031616 0 > STORE 139631856 page 32 hash() > WRITE 11 65536 2097152 0 > STORE 139631856 page 33 hash() > WRITE 11 65536 2162688 0 > STORE 139631856 page 34 hash() > WRITE 11 65536 2228224 0 > STORE 139631856 page 35 hash() > SYNC11 > COMMIT 139631856 > CLOSE -1 > UNLOCK 11 1 was 4(4,1) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741826 510 125195724 0 > fcntl pid=233565 tid=20 11 SETLK UNLCK 1073741824 2 125195724 0 > UNLOCK 11 0 was 1(1,1) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK UNLCK 0 0 125195676 0 > LOCK11 SHARED was NONE(NONE,0) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741824 1 125195868 0 > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741826 510 125195868 0 > fcntl pid=233565 tid=20 11 SETLK UNLCK 1073741824 1 125195868 0 > LOCK11 SHARED ok (unix) > READ11 16 24 0 > UNLOCK 11 0 was 1(1,1) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK UNLCK 0 0 125195980 0 > OPEN 139523248 > TRANSACTION 139523248 > APPEND 139523248 page 1 needSync=1 > APPEND 139523248 page 1 needSync=1 > APPEND 139523248 page 1 needSync=1 > APPEND 139523248 page 1 needSync=1 > APPEND 139523248 page 2 needSync=1 > APPEND 139523248 page 1 needSync=1 > APPEND 139523248 page 1 needSync=1 > APPEND 139523248 page 1 needSync=1 > APPEND 139523248 page 1 needSync=1 > DATABASE SYNC: File= zMaster= nSize=2 > COMMIT 139523248 > JOURNALMODE new : 5, old : 0 > JOURNALMODE_WAL will be enabled > 27 1 0 > Temp file /mnt/share/testApp/myDB.db Shared memory support 1 > LOCK11 SHARED was NONE(NONE,0) pid=233565 tid=20 (unix) > fcntl pid=233565 tid=20 11 SETLK RDLCK 1073741824 1 125195932 0 > fcntl pid=233565 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 SHAR
Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)
On Tue, Nov 12, 2013 at 8:12 AM, Richard Hipp wrote: > > > > On Mon, Nov 11, 2013 at 12:33 PM, Sandu Buraga 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. > > -- > D. Richard Hipp > d...@sqlite.org > -- D. Richard Hipp d...@sqlite.org ___ 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)
On Mon, Nov 11, 2013 at 12:33 PM, Sandu Buraga 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? -- D. Richard Hipp d...@sqlite.org ___ 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)
pid=233565 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 To: General Discussion of SQLite Database Subject: Re: [sqlite] Enable WAL on a QNX system Message-ID: Content-Type: text/plain; charset=ISO-8859-1 On Thu, Oct 31, 2013 at 9:56 AM, Sandu Buraga wrote: > 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(), &dbHandle, > > 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 > (1) WAL mode is a persistent property of the database file. Set it once from a single-threaded connection and it will stay set forever. You should not try to set WAL mode on each connection separately. (2) WAL mode uses mmap() to get access to a small piece of memory that is shared between all connections. But the mmap() is unreliable on many version so QNX. If you must use WAL mode, first run "PRAGMA locking_mode=EXCLUSIVE". That will prevent all processes but the first from connecting to the database (though many threads from within that one process can have their own individual connections). And since only a single process is accessing the database, heap memory instead of mmap() memory is used for the shared memory region. This trick enables WAL mode to work reliably on QNX. > > 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 > -- D. Richard Hipp d...@sqlite.org ___ 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
On Thu, Oct 31, 2013 at 9:56 AM, Sandu Buraga wrote: > 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(), &dbHandle, > > 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 > (1) WAL mode is a persistent property of the database file. Set it once from a single-threaded connection and it will stay set forever. You should not try to set WAL mode on each connection separately. (2) WAL mode uses mmap() to get access to a small piece of memory that is shared between all connections. But the mmap() is unreliable on many version so QNX. If you must use WAL mode, first run "PRAGMA locking_mode=EXCLUSIVE". That will prevent all processes but the first from connecting to the database (though many threads from within that one process can have their own individual connections). And since only a single process is accessing the database, heap memory instead of mmap() memory is used for the shared memory region. This trick enables WAL mode to work reliably on QNX. > > 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 > -- D. Richard Hipp d...@sqlite.org ___ 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
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(), &dbHandle, 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
