sqlite unexpected access violations

[Чебурашка]

After in another post I got no immediate suggestion, I created a test program in order to isolate the problem and I now post it here.

original post: https://forum.lazarus.freepascal.org/index.php/topic,66360.0.html


So, consider the attached zip with software and db.

I see random access violations like this:

Code: Text  [Select][+][-]

1. [10:47:02] mep@mep-development:~/tmp/2024-02-26-threads-and-db$ ./project1
2. Internal error unhandled 1 cycle 0: Access violation
3. Internal error unhandled 1 cycle 1: Access violation
4. Internal error unhandled 1 cycle 2: Access violation
5. "SQLite3","3034001","3.34.1","libsqlite3.so","3034001"
6. Heap dump by heaptrc unit of /home/mep/tmp/2024-02-26-threads-and-db/project1
7. 192262 memory blocks allocated : 16057951/16278136
8. 192262 memory blocks freed     : 16057951/16278136
9. 0 unfreed memory blocks : 0
10. True heap size : 131072
11. True free heap : 131072
12. [10:53:01] mep@mep-development:~/tmp/2024-02-26-threads-and-db$ ./project1
13. Internal error unhandled 1 cycle 0: Access violation
14. Internal error unhandled 1 cycle 1: Access violation
15. Internal error unhandled 1 cycle 2: Access violation
16. Internal error unhandled 1 cycle 3: Access violation
17. "SQLite3","3034001","3.34.1","libsqlite3.so","3034001"
18. Heap dump by heaptrc unit of /home/mep/tmp/2024-02-26-threads-and-db/project1
19. 192192 memory blocks allocated : 16052122/16272224
20. 192192 memory blocks freed     : 16052122/16272224
21. 0 unfreed memory blocks : 0
22. True heap size : 131072
23. True free heap : 131072
24.  

I tried also with latest sqlite 3.45.1.

I don't get what is going on, because despite the existence of multiple threads, they all work on separate databases, and connection/transaction/query objects are always created/destroyed.

Thanks for help.

[TRon]

In which mode is your sqlite library compiled ? see also here

edit: btw you can set the OpenFlags property of TSQLite3Connection.

[Чебурашка]

In which mode is your sqlite library compiled ? see also here

The version shown in the post is 3.34.1, which is the one shipped with debian 11.5. I don't know exactly how they compile it but I would say that they compile with defaults from sqlite source code.
I also downloaded the latest sqlite 3.45.1 and I compiled default, only adding the debug messages.

I get same result in both cases of usage.

Code: Bash  [Select][+][-]

1. [11:21:04] mep@mep-development:~/dev/src/c/y_3rd-party/SQLite$ cat sqlite-configure.sh
2. #!/bin/sh
3.  
4. rm -rf sqlite-build;
5. mkdir sqlite-build;
6. cd sqlite-build;
7. ../sqlite/configure --enable-debug
8. cd ..;
9. [11:21:08] mep@mep-development:~/dev/src/c/y_3rd-party/SQLite$ cat sqlite-build.sh
10. #!/bin/sh
11.  
12. cd sqlite-build;
13. make all;
14. cd ..;
15. [11:21:12] mep@mep-development:~/dev/src/c/y_3rd-party/SQLite$ ./sqlite-configure.sh; ./sqlite-build.sh
16. checking build system type... x86_64-pc-linux-gnu
17. checking host system type... x86_64-pc-linux-gnu
18. checking for gcc... gcc
19. checking whether the C compiler works... yes
20. checking for C compiler default output file name... a.out
21. checking for suffix of executables...
22. checking whether we are cross compiling... no
23. checking for suffix of object files... o
24. checking whether we are using the GNU C compiler... yes
25. checking whether gcc accepts -g... yes
26. checking for gcc option to accept ISO C89... none needed
27. checking for a sed that does not truncate output... /usr/bin/sed
28. checking for grep that handles long lines and -e... /usr/bin/grep
29. checking for egrep... /usr/bin/grep -E
30. checking for fgrep... /usr/bin/grep -F
31. checking for ld used by gcc... /usr/bin/ld
32. checking if the linker (/usr/bin/ld) is GNU ld... yes
33. checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B
34. checking the name lister (/usr/bin/nm -B) interface... BSD nm
35. checking whether ln -s works... yes
36. checking the maximum length of command line arguments... 1572864
37. checking whether the shell understands some XSI constructs... yes
38. checking whether the shell understands "+="... yes
39. checking for /usr/bin/ld option to reload object files... -r
40. checking for objdump... objdump
41. checking how to recognize dependent libraries... pass_all
42. checking for ar... ar
43. checking for strip... strip
44. checking for ranlib... ranlib
45. checking command to parse /usr/bin/nm -B output from gcc object... ok
46. checking how to run the C preprocessor... gcc -E
47. checking for ANSI C header files... yes
48. checking for sys/types.h... yes
49. checking for sys/stat.h... yes
50. checking for stdlib.h... yes
51. checking for string.h... yes
52. checking for memory.h... yes
53. checking for strings.h... yes
54. checking for inttypes.h... yes
55. checking for stdint.h... yes
56. checking for unistd.h... yes
57. checking for dlfcn.h... yes
58. checking for objdir... .libs
59. checking if gcc supports -fno-rtti -fno-exceptions... no
60. checking for gcc option to produce PIC... -fPIC -DPIC
61. checking if gcc PIC flag -fPIC -DPIC works... yes
62. checking if gcc static flag -static works... yes
63. checking if gcc supports -c -o file.o... yes
64. checking if gcc supports -c -o file.o... (cached) yes
65. checking whether the gcc linker (/usr/bin/ld -m elf_x86_64) supports shared libraries... yes
66. checking whether -lc should be explicitly linked in... no
67. checking dynamic linker characteristics... GNU/Linux ld.so
68. checking how to hardcode library paths into programs... immediate
69. checking whether stripping libraries is possible... yes
70. checking if libtool supports shared libraries... yes
71. checking whether to build shared libraries... yes
72. checking whether to build static libraries... yes
73. checking for a BSD-compatible install... /usr/bin/install -c
74. checking for special C compiler options needed for large files... no
75. checking for _FILE_OFFSET_BITS value needed for large files... no
76. checking for int8_t... yes
77. checking for int16_t... yes
78. checking for int32_t... yes
79. checking for int64_t... yes
80. checking for intptr_t... yes
81. checking for uint8_t... yes
82. checking for uint16_t... yes
83. checking for uint32_t... yes
84. checking for uint64_t... yes
85. checking for uintptr_t... yes
86. checking for sys/types.h... (cached) yes
87. checking for stdlib.h... (cached) yes
88. checking for stdint.h... (cached) yes
89. checking for inttypes.h... (cached) yes
90. checking malloc.h usability... yes
91. checking malloc.h presence... yes
92. checking for malloc.h... yes
93. checking for fdatasync... yes
94. checking for gmtime_r... yes
95. checking for isnan... yes
96. checking for localtime_r... yes
97. checking for localtime_s... no
98. checking for malloc_usable_size... yes
99. checking for strchrnul... yes
100. checking for usleep... yes
101. checking for utime... yes
102. checking for pread... yes
103. checking for pread64... yes
104. checking for pwrite... yes
105. checking for pwrite64... yes
106. checking for tclsh8.7... no
107. checking for tclsh8.6... tclsh8.6
108. configure: Version set to 3.45
109. configure: Release set to 3.45.1
110. checking for WASI SDK directory... no
111. checking whether to support threadsafe operation... yes
112. checking for library containing pthread_create... -lpthread
113. checking for library containing pthread_mutexattr_init... none required
114. checking whether to support shared library linked as release mode or not... no
115. checking whether to use an in-ram database for temporary tables... no
116. checking if executables have the .exe suffix... unknown
117. checking for Tcl configuration... configure: WARNING: Can't find Tcl configuration definitions
118. configure: WARNING: *** Without Tcl the regression tests cannot be executed ***
119. configure: WARNING: *** Consider using --with-tcl=... to define location of Tcl ***
120. checking for library containing readline... no
121. checking for library containing tgetent... no
122. checking for readline in -lreadline... no
123. checking readline.h usability... no
124. checking readline.h presence... no
125. checking for readline.h... no
126. checking for /usr/include/readline.h... no
127. checking for /usr/include/readline/readline.h... no
128. checking for /usr/local/include/readline.h... no
129. checking for /usr/local/include/readline/readline.h... no
130. checking for /usr/local/readline/include/readline.h... no
131. checking for /usr/local/readline/include/readline/readline.h... no
132. checking for /usr/contrib/include/readline.h... no
133. checking for /usr/contrib/include/readline/readline.h... no
134. checking for /mingw/include/readline.h... no
135. checking for /mingw/include/readline/readline.h... no
136. not using linenoise
137. checking for library containing fdatasync... none required
138. checking build type... debug
139. checking zlib.h usability... yes
140. checking zlib.h presence... yes
141. checking for zlib.h... yes
142. checking for library containing deflate... -lz
143. checking for library containing dlopen... -ldl
144. checking whether to support math functions... yes
145. checking for library containing ceil... -lm
146. checking whether to support JSON functions... yes
147. checking whether to support MEMSYS5... no
148. checking whether to support MEMSYS3... no
149. checking whether to support FTS3... no
150. checking whether to support FTS4... no
151. checking whether to support FTS5... no
152. checking whether to support LIMIT on UPDATE and DELETE statements... no
153. checking whether to support GEOPOLY... no
154. checking whether to support RTREE... no
155. checking whether to support SESSION... no
156. configure: creating ./config.status
157. config.status: creating Makefile
158. config.status: creating sqlite3.pc
159. config.status: creating sqlite_cfg.h
160. config.status: executing libtool commands
161.  
162. [...]
163.  
164. gcc  [...]
165. -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DSQLITE_DEBUG=1 -DSQLITE_ENABLE_SELECTTRACE -DSQLITE_ENABLE_WHERETRACE -O0 -Wall -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS  -DSQLITE_HAVE_ZLIB=1  -DSQLITE_TEMP_STORE=1 -c sqlite3.c
166. [11:24:21] mep@mep-development:~/dev/src/c/y_3rd-party/SQLite$
167.  

[TRon]

I also downloaded the latest sqlite 3.45.1 and I compiled default, only adding the debug messages.

then it seems to be on by default:

cc  [...]
-D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DSQLITE_DEBUG=1 -DSQLITE_ENABLE_SELECTTRACE -DSQLITE_ENABLE_WHERETRACE -O0 -Wall -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS  -DSQLITE_HAVE_ZLIB=1  -DSQLITE_TEMP_STORE=1 -c sqlite3.c

Forget the above, the option -DSQLITE_THREADSAFE=1 is for serialized. It needs to have the number 2. see also https://www.sqlite.org/compile.html#threadsafe

I get same result in both cases of usage.

I am testing with default form my distro: 3.40.1 64 bit and have similar results. I am fiddling with openflags now.

[Чебурашка]

edit: btw you can set the OpenFlags property of TSQLite3Connection.

As I understand this is necessary when two threads use the same connection. In my case each thread uses it's own db file, connection, transaction, query.

Thanks for helping, I am going mad on this thing.

[TRon]

Please re-read my previous post Чебурашка as I have edited it.

[Чебурашка]

Please re-read my previous post Чебурашка as I have edited it.

I saw, but as I told, unless I missed something, this would be a problem when multiple threads concurrently access the same connection (then therefore the same database).

And this is absolutely not my case.

Maybe I am missing something then? I will try recompiling with 2.

[TRon]

I saw, but as I told, unless I missed something, this would be a problem when multiple threads concurrently access the same connection (then therefore the same database).

That is not clear for me from reading the documentation. Nevertheless the library itself is just a single library and that is only opened once in your application (actually sqlitedyn does this behind the scenes).

Note that having configured everything as correct for sqlite itself it does not mean that the code used in FPC is threadsafe.

[Чебурашка]

That is not clear for me from reading the documentation. Nevertheless the library itself is just a single library and that is only opened once in your application (actually sqlitedyn does this behind the scenes).

I checked the sqlite docs and for what I can get, it seems that "-DSQLITE_THREADSAFE=1" is the right gcc option. From thier website:

Use the SQLITE_THREADSAFE compile-time parameter to select the threading mode. If no SQLITE_THREADSAFE compile-time parameter is present, then serialized mode is used. This can be made explicit with -DSQLITE_THREADSAFE=1. With -DSQLITE_THREADSAFE=0 the threading mode is single-thread. With -DSQLITE_THREADSAFE=2 the threading mode is multi-thread.

Note that having configured everything as correct for sqlite itself it does not mean that the code used in FPC is threadsafe.

Ops...

[Чебурашка]

I will try recompiling with 2.

I added to sqlite/configure script the option

--enable-threadsafe

and I get the same

"-DSQLITE_THREADSAFE=1"

so this is the right option for threadsafeness.

[Zvoni]

Forget the above, the option -DSQLITE_THREADSAFE=1 is for serialized. It needs to have the number 2. see also https://www.sqlite.org/compile.html#threadsafe

Eh? Would have to disagree.
From the same page you cited:

When SQLite has been compiled with SQLITE_THREADSAFE=1 or SQLITE_THREADSAFE=2 then the threading mode can be altered at run-time using the sqlite3_config() interface together with one of these verbs:

    SQLITE_CONFIG_SINGLETHREAD
    SQLITE_CONFIG_MULTITHREAD
    SQLITE_CONFIG_SERIALIZED
The SQLITE_OPEN_NOMUTEX and SQLITE_OPEN_FULLMUTEX flags to sqlite3_open_v2() can also be used to adjust the threading mode of individual database connections at run-time.

sqlite3_config() is defined in sqlite3.inc, but not exposed in TSQlite3Conn

[TRon]

Note that having configured everything as correct for sqlite itself it does not mean that the code used in FPC is threadsafe.

Ops...

For the record, not saying sqlite code in the package wasn't written threadsafe just that it does not mean that because the code exists it is threadsafe by default 

[Чебурашка]

Note that having configured everything as correct for sqlite itself it does not mean that the code used in FPC is threadsafe.

Ops...

For the record, not saying sqlite code in the package wasn't written threadsafe just that it does not mean that because the code exists it is threadsafe by default 

I confirm that all this started to happen to me when I started having 3 databases, accessed by 2 threads like this: thread 1 goes on db 1 and db 2, thread 2 goes on db 3.
Before I had only one thread accessing db 1 and db 2, and no error was occurring.

[TRon]

Forget the above, the option -DSQLITE_THREADSAFE=1 is for serialized. It needs to have the number 2. see also https://www.sqlite.org/compile.html#threadsafe

Eh? Would have to disagree.

You are correct Zvoni. Thank you for the correction.

I completely missed "Note that when SQLite is compiled with SQLITE_THREADSAFE=0, the code to make SQLite threadsafe is omitted from the build. When this occurs, it is impossible to change the threading mode at start-time or run-time." 

Ok, but do you agree that when our sqlite implementation would use open_v2 that then there is no need to use config but can use the flags instead ?

[TRon]

Ok, call me crazy....

I just added:

Code: Pascal  [Select][+][-]

1.   InitialiseSQLite;
2.   writeln('is sqlite threadsafe : ', sqlite3_threadsafe);
3.  

As first two lines in the RunTheTest procedure and that seems to have done the trick ?

Is that really because the main program thread does not have an instance of the library ?