An Analysis of Current Issues and Challenges in SQLite Web Databases

This report delves into the current issues and challenges associated with using SQLite databases, particularly in web-based database systems. It begins with an overview of SQLite, its architecture, and its serverless nature, contrasting it with traditional client-server database models. The report then explores various challenges, including data security, performance limitations, and data safety concerns in web environments. It highlights the problems associated with SQLite's file locking mechanisms, especially in scenarios involving network file systems and multi-threaded applications. The discussion covers how file locking issues can lead to database corruption and performance degradation. Furthermore, the report addresses the limitations of SQLite in supporting client-server architectures and handling high concurrency. It also examines the impact of POSIX advisory locks and the potential for database corruption when multiple threads access the same database file. The report concludes by emphasizing the suitability of SQLite for small applications like mobile apps while highlighting its shortcomings when used for large-volume websites, focusing on its inability to handle high concurrency and maintain data integrity in complex web database scenarios.

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
Aims of the Project
The goal is to evaluate the SQLite database issues on web based database challenges
Objectives
This paper provides the details about the SQLite and the challenges using the SQLite in the
web based database. Here we will investigate the risk associated with the SQLite. The
requirement of web based database.
Abstract
Today’s dynamic and rapidly evolving nature of systems enforces the special requirements on
the technology, the approach to solve the system, architecture and design of the
computational structure which include the database and Web application both. Now we have
multiple solutions to suggest meeting the prospects and innovative functionality have been
established to discourse the persisting problems of web based database challenges. It is
beneficial for us to comprehend multiple technologies and multiple approaches. To habituate
with the profit and loss of the existing technologies, the investigators can check its abilities.
Here In this review we are going to discuss the current issues with the SQLite database in
terms of web based database challenges (Ashenfelter, 1999).
We will discuss about the SQLite (what is SQLite, How it work, and the architecture of
SQLite) why it is not appropriate with the web based database, what is the difficulties we
handle when we use SQLite as web based database. What are the challenges in web based
database. The problem of using the server less architecture using in SQLite, the benefits of
using the client server architecture, how the file licking system affects the performance of the
SQLite database, Why it is suitable for the small application like mobile app, iPhone, and
other android apps, What types of problem we faces while we use SQLite in a large volume
website database, Why the SQLite failed in high volume concurrency data handling, why the
file of database is failed in sync Describing the file over writing problem. But before
discussing all first we need to understand what is SQLite, how it work, and what are the
challenges of today’s web base database and how we can overcome from this type of
challenges (Ungaro et al., 2017).
1 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
Keywords: multi-writer, multi-reader, client/server, self-contained, server less, transactional,
zero configurations, light weight, TCP/IP, Data security, Data safety, thread, multi thread,
architecture, High availability, File locking, memory, database corruption, Error,
Introduction of SQLite:
First we need to understand what is SQLite then we discuss about the current issues with
SQLite. It is a software collection of archive file or library which offers a RDBMS. The Lite
in the SQLite means lightweight, SQLite is very light weight when we are going to setup,
DBA etc. SQLite is a server less database, a self-contained, has zero configuration, and
transactional (Ahmed, 2015).
Server less
In general every relational database (for example MYSQL, SQLSERVER, PostgreSQL, etc.)
requires the server for operations. Because the database application needs to contact the
database server to use TCP/IP protocol, using this database can receive and send requests. It
is known as client/server architecture. But in the SQLite there is no need of server and it is
called server less database (Nwachukwu, 2012).
RDBMS client/server architecture:
SQLite database is associated with the user application software which contacts the database.
Application software will interrelate with the SQLite database and access the database file
from the disk directly (Kreibich, 2010).
2 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
SQLite server-less architecture:
Self-Contained
Self-contained like independent, SQLite is self-contained, it require marginal or minimum
maintenance from the external library or the operating system. That’s why it’s useful for the
embedded devices like iPhones, android phones, and handheld media players etc (Alvaro,
2016).
Zero-configuration
SQLite has no server it is server less architecture so it does not requires server, and also no
need to be configured, started, and stopped (Pereira, 2009).
Transactional
SQLite is totally ACID compliant, the requests (query) and variations are Atomic, Consistent,
Isolated, and Durable (Min,Kang, Kim, Lee, & Eom, 2015).
Overview of web based database challenges:
Today we have a number of databases available, all database have own advantage and
drawbacks, The perfect technique to decide the correct database for our desired requirement
is to decide our self , the challenges we have, and what we need in our database to
meet? (Mitrovic, Suraweera, Martin, & Weerasinghe, 2004).
Followings are some common database challenges and the solution to overcome this type of
problem:
3 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
1. Data security
In the recent years we have multiple examples of website or system hacked due to the
database is totally unprotected on the internet (publically).The irony is that the databases
weren’t stranded due to the safety issues. Now the databases come with the security which is
informal to set up, fast to inform the users if anything is going incorrect (Stallings, Brow,
Bauer, & Bhattacharjee, 2012).
2. Performance
In terms of performance we can see in the recent year the database continuously challenged
tough working standards. The quantity of data we are taking in may be increasing, but to
gratify our customer or user we require its processing faster, without creating them to hurdle
over hoops to get there. A good database designed be made for good concert, irrespective of
the hardware. If it will perform well on machines, it has good “native” performance
(Florescu, & Kossmann, 2009).
3. Data safety
An information is always require to be treated in a technique that guarantees zero loss. Even
in a non-relational database, they must have ACID assurances both the database and cluster.
The main test for any database is in the working criteria. A noble database is always gives us
the good performance as well as the transactional guarantees (Cheung, Cheung, & Suen,
2004).
4. Resource utilization
In terms of web based challenges we always assumed that database run on a best available
hardware and behave accordingly. To preserve the performance in the restricted calculating
capacity and they will tackle vast quantities of composite data, a database needed 100 percent
out of the capitals it is occupied on for each nanosecond it is running (Kavadas et al., 2013).
5. High availability
4 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
Suppose we use a database which has only one server and our server drives down, our
business or organisation also goes down with the server. A data cluster which containing of
multiple servers functioning well gives us multiple levels of backup. For organisation, it is
like to connecting additional records to holder the increasing line of customer. A distributed
database can have the situation where the task will be failover so once the node working on a
particular task it drives low, that work or job is routinely moved to a functioning node.
Writing a operation to a local disk is a much quicker than requiring to scuffle it through a
distributed network. Noble database takings this into attention and makes advanced
resolutions to preserve the performance beside with ensuring continually on obtainability
(Pokorny, 2013).
Current Issues with SQLITE
1. Not supported Client/Server Architecture
Client/server application architecture is like a producer and consumer architecture in this
architecture the server act like the producer and client act like the consumer, the server
provides the service to the client on demand. The services can include the access of
application, sharing the file, accesses the printer, or it may be other services. It works when
the client send the appeal to the server, it progress the client request and response send back
to the client, at a time the server can handle the multiple clients simultaneously, the same
time the client is connected with multiple server. When we use the client server architecture
many clients sending the SQL to the same database over the network (TCP/IP), but in case of
SQLite database they work over a network file system, but because of the inactivity related to
the most network filesystems it will affect the performance of the system. File locking
problem is stroller for many applications which uses the network file system implementation.
If in the network filesystem the file locking does not work properly and multiple client access
or try to update the same things (operation) at same time, the result will be corrupted Because
it is the bug of network file system. So it is the current issues with the SQLite when we work
with the client server architecture (Nield, 2016). .
5 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
2. File locking problems
SQLite practices the file locks on the file of database and on the write-ahead log or WAL file.
File lock system organise the contact among the synchronized methods. Because if the
system not coordinate the two or multiple process at a time it will make the incompatible
changes in the database file at the similar or identical period, and we faces the database file
exploitation issues (Owens, 2006).
Followings are some issues with file locking system:
I. Filesystems with broken or missing lock implementations
In the SQLite the lock in the database filesystem is like documentation says it will. But it is
not always the best way to lock the database filesystem, because some time we found the
filesystem contained the bugs in the locking system logic and the lock is not working
properly. It is most common when do not use the client server architecture and go with
network filesystem. When SQLite database file contain any bugs in their locking logic the
multiple thread or process wants to retrieve the same database file at a same time at it leads
the database corruption (Haldar, 2007).
II. Posix advisory locks canceled by a separate thread doing close()
The SQLite uses the defaulting locking process on the unix platform known as POSIX
advisory locking. Some process in the similar method with a file descriptor which is carrying
a POSIX advisory lock will dominate that lock by using a altered file descriptor. Single
predominantly malicious difficult is that the close() system call will stop all the POSIX
advisory locks on the similar file for entire threads and all file descriptors in the method.
Therefore, for example, assume a multi-thread procedure has more than two threads with
distinct SQLite database networks to the identical database file. Then another thread comes
laterally and needs to recite somewhat out of that identical database file on its own, without
using the SQLite library. The another thread prepares an open(), a read() and then a close().
One may be consider this will be inoffensive. But the close() system call produced the locks
thought the database by all the other threads to be released. Other Individuals threads must
have no approach of knowing that their locks have just been disorganized. Remember this is
totally harmless for multiple process to contact the SQLite database file consuming the
6 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
SQLite library. The drivers of unix for SQLite aware of the POSIX advisory locking
coincidences and effort nearby. Difficulty first rises when a process wants to avoid the
SQLite archive and recited the database file straight (Sugiharto, 2010).
To overcome the POSIX advisory locking system on the database filesystem SQLite contains
possession a universal list (mutex protected) of open SQLite database files. But, if we have
various replicas of the SQLite are connected to the identical application, then we have
numerous occurrences of this universal list and the database connection which is unlocked
using the single duplicate of the SQLite library is totally uninformed of database contacts
unlocked by the using of additional copy, and the SQLite is not able to effort about the
POSIX advisory locking quirks. And when we apply the close () operation on one connection
to close that particular connection it will be innocently release the locks on a multiple
database contact, and it will lead to the major database corruption (Ganai, 2012).
The above given scenario is like sounds unbelievable but the people (developer) working
with SQLite are conscious of at minimum one profitable merchandise that was released with
accurately with this bug or issues. The seller came to the SQLite developers looking for
support in chasing down some uncommon database exploitation problems they were sighted
on Linux and Mac. The difficulties were ultimately outlined to the fact that the application
was connecting beside the two distinct duplicates of SQLite. The explanation was to
alteration the application build measures to link beside just one copy of SQLite in its place of
two (Gilbert, 2007).
III. Two processes using different locking protocols
POSIX advisory locking technique in SQLite for unix platform is a default locking
mechanism but we have supplementary choices also like sqlite3_vfs using
the sqlite3_open_v2() interface, using this method we can procedure other locking protocols
which will be most suitable for definite filesystems. For example, dot-file locking system
must be choose for procedure in an application which will be run on an NFS filesystem and it
does not care POSIX advisory locking. But the most significant that altogether the links to the
same database file use the same locking protocol. If one database file application is using
POSIX advisory locks and other database file application is using dot-file locking, then in
this scenario both the applications is not able to see separately other's locks and they will not
7 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
capable to organize database contact, and it leads to the major database exploitation (Long,
2011).
IV. Unlinking or renaming a database file while in use
If multiple threads have open networks to the identical database file and one thread closes its
network, unlinks the file, then it makes a fresh database file in its habitation with the similar
name and reopens the new file, then the two threads will have conversation to another
database files with the similar name. Meanwhile rollback journals and WAL files are
constructed on the name of the database file, the two dissimilar database files will parts the
same rollback journal or WAL file. A rollback or retrieval for one of the databases may use
contented as of the other database, resulting in corruption. The same type of difficulties arises
if the database file is retitled although it is unlocked and a new file is formed with the ancient
name. In other way we can say the not linking or change naming of an open database file
outcomes in performance that is indeterminate and perhaps unwanted (Pillai et al.2014).
V. Multiple links to the same file
Only database file has numerous associations then we can say they have several names. If
multiple threads create the database by means of multiple titles, it usage multiple rollback
journals and WAL files. Means one process smashes, the supplementary threads is not able to
get well the operation in improvement since it watching incorrect habitation for the suitable
journal. In different terms, foundational and consuming a database files which multiple title
outcomes in performance that is indefinite and perhaps unwanted (Leschke, & Nicholas,
2013).
3. Not supported by High-volume Websites
SQLite database performance is good when it is work with as the database backend to a
website. But if the website is write-intensive or is so busy that it requires multiple servers,
and then we choose the client server architecture instead of SQLite. Because concurrent or
multiple writes not allowed in SQLite, and for high volume website we need multiple
concurrent writer and in case of SQLite we have only single writer, multi reader. And it’s
beyond the SQLite design. For high volume website it is hallmark to use the large engine
database for example DB2, ORACLE, SQL Server, PostgreSQL etc. SQLite opted a very
different design philosophy from the most of the different design philosophy from the most
server centric DBMS which support the multiple reader with multiple writers. SQLite is
8 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
designed to take the power of SQL (Structured Query Language) and the relational model to
individual applications, and definitely to be embeddable within each application. It’s not
adding the important infrastructure and overhead needed to handle multiple concurrent
writers is one of those (Huang, Guo, Zhang, Qian, & Zhou, 2018).
4. Problems with Very large datasets
The size of SQLite database is restricted (140 terabytes) and even it can hold the big
databanks, SQLite stocks all records in a one disk file and multiple filesystems bound the
extreme size of files to somewhat fewer. we expecting records of this greatness, we need to
reflect prefer a client/server database that ranges its contented through numerous disk files,
and possibly through numerous sizes. If our data will cultivate to a dimension that we are not
comfortable or not able to fit into a one disk file, then we must choose a clarification which is
different to SQLite (Kreibich, 2010).
5. Problem with High Concurrency
SQLite cares limitless quantity of concurrent read only thread, it only permit single writer
thread at any specified time. For numerous conditions, it’s not problematic. writer thread line
up. Every request effort speedily and changes on, and no lock persists for extra than a
insufficient dozen milliseconds. But there are approximately applications that need additional
concurrency, and these individuals applications may require a dissimilar solution because
SQLite is not compatible with large volume of writers. If numerous processes require writing
the database at the identical prompt (they not line up and takings chances) at that point it is
greatest way to choose the other engine database which maintenances ability, continually for
example a client/server. SQLite simply maintaining single writers, However in several cases,
a writer operation simply takings smallest unit of time and so numerous writer just takings
opportunities. SQLite tackle multiple writers concurrencies several developer doubtful.
However, client/server architecture, since they procedure at finger to organise contact, can
typically maintain multiple writes concurrency than SQLite (Nell, Fawcett, Hoos, & Leyton-
Brown, 2011).
6. Security issues
9 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
Vulnerability in security is the vastly common in the SQLite database engine it keeps many
of the personal computer and the mobile applications at risk. Exposed by Tencent's Blade
safety squad, the susceptibility permits an invader to run hateful code on the casualty's
computer, and in fewer unsafe circumstances, escape program memory or cause program
smashes. Because SQLite is embedded in multiple apps, the vulnerability impacts a wide
range of software, from internet of things (IoT) devices to the desktop software, and from
web browsers, Android and iOS apps also. The compressed and competent environment of
SQLite has headed it to convert very public in mobile development. The library of the SQLite
can comprehend almost every regular SQL commands, with a few exceptions. It also has ties
with general programming languages and is encompassed by avoidance on maximum
smartphones. With a small impression with the server-less databases, clear source code, and
cross-platform ability, all also various advantages of SQLite, But also have disadvantages of
SQLite. In the SQLite database the data is kept as a particular file which can be situated
anyplace in the directory order and may be any reprobate method can open the data file and
can overwrite it. And we can see there is no way to prevent our file from this type of activity,
so security need to be achieved at file level. Extra safety concern is the journaling. When
fluctuations are complete, the SQLite database preserves distinct “journal” or “WAL” files to
assist roll backs. These files are usually transitory and get erased when the operation commits
or rolls back. Though, there are 3 situations that can inhibit with journal removal.
1. If a smash happens in the middle of operation, the -journal or -wal file is kept to disk till
the next use.
2. Application Makers have the choice to set journaling mode to PERSIST (which avoids
the journal from presence erased).
3. Makers also have the choice to put SQLite into limited locking mode (often complete for
act). With this choice, the rollback journal might be condensed or have its header zeroed
(dependent on which variety we are consuming) but the journal itself is not erased till SQLite
exoduses the limited lock mode.
These circumstances were extant severe safety fears for a database management delicate or
complex data ideally; it is potential to turn off journaling at the foundation level. Though, it
is not suggested, because if journal files are lost when the application smashes, the
organisation data in the database will likely be corrupted (Bhosale et al.,2015).
10 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
7. Failure to sync
Now direction toward assurance the records of database remain continuously reliable,
SQLite sporadically request operational system to flush all awaiting write to insistent
storing at that time delay. It consummate by the fsync() system under the unix
and under the window it is FlushFileBuffers() . if there is single apprehensive with the
nuclear and reliable write and they eager to sacrifice vigorous write, the sync process
does not require waiting till the contented is totally stored on persistent media. In its
place, the sync methods can be believed of as an I/O obstacle. As stretched as all writes
that happen earlier the sync are finished previously any write that occurs after the sync,
no database exploitation will happen. If sync is working as an I/O blockade and not as
a correct sync, then a control disaster or system smash may reason of multiple earlier
committed transactions to roll back (in violation of the "durable" property of "ACID")
but the database will at smallest endure to be reliable, and that is what maximum
people care almost (Dar, and J, 2016).
I. Disk drives that do not honour sync requests
Regrettably, the maximum of the consumer like bulk storing devices untruth about the
syncing. Disk drives tale that contented is securely on determined media as quickly as
it spreads the trajectory buffer and earlier essentially being written to oxide. This
creates the disk drives perform to function quicker (which is extremely significant to
the producer so that they can display good standard statistics in professional
magazines). And in equality, the propaganda usually effect no damage, as long as there
is no control damage or solid rearrange prior to the track buffer fundamentally being
written to oxide. But if a power loss or hard reset does occur, and if that consequences
in contented that was written after a sync reaching oxide while contented transcribed
earlier the sync is still in a track buffer, then database exploitation can happen.
The SQLite must accept as true at all the operating system and hardware say it about
the position of sync requirements. There is no approach for SQLite to notice that
whichever is dishonest and that writes might be occurring out-of-order. However,
SQLite in WAL mode is far more forbearing of out-of-order writes than in the
11 | P a g e

Current issues with SQLite: WEB BASED DATABASE CHALLENGES
defaulting rollback journal modes. In WAL mode, the only time that a failed sync
operation can cause database corruption is during a checkpoint operation. A sync let-
down during a COMMIT might outcome in damage of toughness but not in a
fraudulent database file. Hereafter, one line of protection in contradiction of database
exploitation due to unsuccessful sync processes is to use SQLite in WAL mode and to
barrier as infrequently as conceivable (Shi, Ji, & Li, 2018).
8. Non-power safe flash memory controllers
The SQLite database will may be corrupt if we try to modify the content of the file
because it is chance of failure of hard disk or flash memory. There will be chance to
flip a bit of the middle of sector so that it is getting fail.
If power is disturbed when we are writing into the database there can be cause of
random filesystem damage. It can obvious, for example: when unsystematic
modification happening at the mid of a file and the same time if we want to open the
file at the power loss that is very difficult to open. There is probability of damage of the
database even though we are not using at the time of load setting (Nakamura, Nagata,
Nomura, & Yamaguchi, 2014).
9. Memory corruption
SQLite is a C-library which will work on the similar address space whatever the web
application or system application that it serves. That means heap corruption; lost
cursors, buffer overruns or other malfunctions in the application can corrupt interior
SQLite data structure and eventually the outcome in a bad database file. This type of
issues is on top priority even though before database corruption. The memory issue will
be most serious when we are using the memory-mapped I/O. The application’s address
space which is mapped by database file then lost pointer may harm to the database file
(Szekeres, 2017).
10.Different problems with operating system
As we know that operating systems is a most important role to run any
application. The operating system is showing performance issues in the
application and sometime it is an error in the running application. Due to
12 | P a g e