SQLite Vs VistaDB - Comparison ???

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite Vs VistaDB - Comparison ???

RohitPatel9999
Hello

I was just comparing embedded SQL database engines (SQLite Vs VistaDB) for my knowledge. Itmight be of help for someone.

In case, someone might be interested to know and/or add more feature comparisons.

SQLite
HomePage : www.sqlite.org
More Features : www.sqlite.org, www.sqlite.org/docs.html, www.sqlite.org/faq.html
SQLite is a free open-source embedded SQL database engine (for multiple platforms)
Cost? : Free Version (with no encryption feature, No password protection)
Encryption?: Encryption extension is Commercial, need to purchase license
Platforms? : For multiple platforms
Source Code Available? : Yes, for free version (No Encryption)
Size? : Small 250 KB footprint
Single-file database format, database files can be freely shared between machines with different byte orders.
Multiple threads/processes can have the same database open at the same time
Supports concurrancy
Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures
Zero-configuration - no setup or administration needed.
SQL? : SQLite Implements most of SQL92 (standard SQL language). But does omit some features and added a few features of its own.
CSV and text file import and export
UTF-8, UTF-16 Support
Supports C/C++, PHP, Pearl, tcl, Python and many other languages through C or through own wrappers, .NET port available, WindowsCE port available.
More Features : www.sqlite.org, www.sqlite.org/docs.html, www.sqlite.org/faq.html

VistaDB  
HomePage : www.vistadb.com
More Features : www.vistadb.com/features.asp
VistaDB is a commercial embedded SQL database engine (only for .NET and Win32)
Cost? : Need to purchase license, then Royalty free distribution
Encryption?: Secure Blowfish encryption and password protection
Platforms? : For Different Window Versions
Source Code Available? : No
Size? : Small 500KB footprint for Embedded Editions
Single-file database format
Single and Multi-User support
Supports concurrancy
SQL? : Easy-to-use full featured RDBMS, provides industry support for SQL-92 and powerful Direct Data Access
Fast performance
No deadlocks - Snapshot Isolation level Transaction Processing
SureCommit
Row and Table-level locking
Automatic Storage Recycling
XML Import and Export
Automatic data synchronization
In-memory databases and tables
Write-behind data caching
International support
Supports C#, VB.NET, Delphi, C++Builder, VB and classic ASP
More Features : www.vistadb.com/features.asp


Please put forward your views, ideas, thoughts, comparisons (if any) ??? I might have missed many points of comparison/similarity.


Rohit
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

René Tegel
RohitPatel9999 schreef:
> Please put forward your views, ideas, thoughts, comparisons (if any) ??? I
> might have missed many points of comparison/similarity.
>  
At the risk of playing the devils advocate, if your target is ms windows
(seen your interest for vistadb), i found MS-Access a very reasonable
flat-file database. It may lack fancy features like encrytion, but has
it advantages as well
Pro's: any windows client has the driver installed (no need to install
office), accessable by odbc, reasonable sql (very much like mssql
server), reasonable fast, able to be used as website-backend (!), allows
simultanious users to certain amount (max. 5 recommended by MS), and
flat-file which easifies back-ups and distribution. License not needed
since it is licensed when using windows. Good indexes.
Cons: platform dependant. Sometimes bit weird SQL dialect (as anything
from MS i guess). When not properly designed may take some time porting
an database and/or application. Size of datafile may unproportionally
grows to the amount of data. Unknown behaviour (to me) on hard crashes
but probably repairable.

You may also want to investigate embedded MySQL.
Pros: full-blown mysql engine. Very configurable. Excellent
multi-threading support. Cross-platform.
Cons: poorly documented (the embedded part (does and don'ts)), probably
still buggy (4.1 was), may need license when shipped with
commercial/closed source software. Possible crashed tables if the main
application crashes. Probably you are better off seperating client and
server (=traditional setup).

To be honest, for what sqlite was designed i think there is no serious
alternative, sqlite is the best imho for embedded usage, and does not
suffer platform or language dependancy. Small con: query parser may have
trouble optimizing indices.

regards,

Rene




 
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Mikey C
MS Access (MDB files) use the Jet engine.  Not every PC has the correct drivers, since jet has changed many times as Access evolved from version 2.0 thru 95, XP and 2003.

Access is NOT ACID compliant, is limited in maximum database size, is limited to 255 connections.

http://www.somacon.com/p369.php

However, JET's biggest gain over SQLite is it supports table and row level locking.  If D. Hipp were to implement a fine grained locking mechanism in SQLite, we'd be onto a winner.

Please implement table and row level locking. :-)
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Jay Sprenkle
In reply to this post by René Tegel
On 6/16/06, René Tegel <[hidden email]> wrote:
> i found MS-Access a very reasonable
> flat-file database. It may lack fancy features like encrytion, but has
> it advantages as well
> Pro's: any windows client has the driver installed (no need to install
> office), accessable by odbc, reasonable sql (very much like mssql
> server), reasonable fast, able to be used as website-backend (!),

I can't recommend Access files.

I've repeatedly seen file corruption in multiuser applications
using Microsoft's Access. No user written code ever touched
the database but we still suffered corruption problems.

Having program data accessable, and thus changable, by
the user without my application to control that access has
proven to be bad in many of my installations. If the user messes
with it I end up fixing the mess. In general I've found almost none
of them able or willing to use a database or report
writer.

It's not portable to anything else but windows.

It costs money to buy the development tools.


I've found more drawbacks than advantages.


--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

D. Richard Hipp
In reply to this post by Mikey C
Mikey C <[hidden email]> wrote:
>
> Please implement table and row level locking. :-)

People commonly believe that doing so must be easy.  I
certainly get a lot of requests for it from people who
think they know how.  But in fact, row-level locking
is extraordinarily difficult.  To my knowledge, nobody
has yet come up with a way to do it unless you:

  *  Add a server process to coordinate access.

  *  Accept that the database might be corrupted if
     an application crashes while writing.

  *  Make writing to the database very, very slow.

  *  Double the size of the database file.

If you think you know a way to implement row-level
locking that does not impose one of the above
limitations, then please tell me and I will look
into the matter.

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Bogusław Brandys
[hidden email] wrote:

> Mikey C <[hidden email]> wrote:
>> Please implement table and row level locking. :-)
>
> People commonly believe that doing so must be easy.  I
> certainly get a lot of requests for it from people who
> think they know how.  But in fact, row-level locking
> is extraordinarily difficult.  To my knowledge, nobody
> has yet come up with a way to do it unless you:
>
>   *  Add a server process to coordinate access.
>
>   *  Accept that the database might be corrupted if
>      an application crashes while writing.
>
>   *  Make writing to the database very, very slow.
>
>   *  Double the size of the database file.
>
> If you think you know a way to implement row-level
> locking that does not impose one of the above
> limitations, then please tell me and I will look
> into the matter.


I'm not an expert but let me propose such solution:
Multi- Generational Architecture like Interbase/Firebird can cope with
all except the last point (double size of database file) - however in
the last case periodical vaccum could shrink database.
There is also no need to have server process - each instance of sqlite
library could be a server.

Shared lock manager could be required or simply each instance of sqlite
library could have separate one.
Shared lock manager is tricky idea (maybe it could be for example dumb
manager in each sqlite library with shared memory pool and if one
instance terminate another one could detect it and play that role?)

In fact that is as I fairy know how it's implemented in Firebird Classic
Server (where each server process has separate lock manager I suppose)
This classic server processes  are spawn by xinetd deamon.
I see sqlite in very similar manner : sqlite library is attached to each
spawned process which uses it.


Regards
Boguslaw Brandys
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Clay Dowling

Bogus³aw Brandys said:

> In fact that is as I fairy know how it's implemented in Firebird Classic
> Server (where each server process has separate lock manager I suppose)
> This classic server processes  are spawn by xinetd deamon.
> I see sqlite in very similar manner : sqlite library is attached to each
> spawned process which uses it.

You've just proposed changing SQLite from an embedded database to a server
database.  The fact that it would be a self-launching server doesn't
really change that.  It completely kills its value to me.  If I wanted a
server process running I'd use PostgreSQL and get the associated benefits
to boot.

Clay
--
Simple Content Management
http://www.ceamus.com

Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Christian Smith-4
In reply to this post by Bogusław Brandys
Bogus�aw Brandys uttered:

> [hidden email] wrote:
>> Mikey C <[hidden email]> wrote:
>>> Please implement table and row level locking. :-)
>>
>> People commonly believe that doing so must be easy.  I
>> certainly get a lot of requests for it from people who
>> think they know how.  But in fact, row-level locking
>> is extraordinarily difficult.  To my knowledge, nobody has yet come up with
>> a way to do it unless you:
>>
>>   *  Add a server process to coordinate access.
>>
>>   *  Accept that the database might be corrupted if
>>      an application crashes while writing.
>>
>>   *  Make writing to the database very, very slow.
>>
>>   *  Double the size of the database file.
>>
>> If you think you know a way to implement row-level
>> locking that does not impose one of the above
>> limitations, then please tell me and I will look
>> into the matter.
>
>
> I'm not an expert but let me propose such solution:
> Multi- Generational Architecture like Interbase/Firebird can cope with all
> except the last point (double size of database file) - however in the last
> case periodical vaccum could shrink database.
> There is also no need to have server process - each instance of sqlite
> library could be a server.
>
> Shared lock manager could be required or simply each instance of sqlite
> library could have separate one.
> Shared lock manager is tricky idea (maybe it could be for example dumb
> manager in each sqlite library with shared memory pool and if one instance
> terminate another one could detect it and play that role?)
>
> In fact that is as I fairy know how it's implemented in Firebird Classic
> Server (where each server process has separate lock manager I suppose)
> This classic server processes  are spawn by xinetd deamon.
> I see sqlite in very similar manner : sqlite library is attached to each
> spawned process which uses it.

In order to communicate with the other lock managers, all instances of the
SQLite library would have to be on the same box.

If you want MVCC without process communication (as not all processes would
be on the same box) you'd need each row update to be synchronous and
synced, which would be slower than what we have now.

The locking protocol could maybe be changed to allow locking at the table
level, but such a change would be incompatible with the current locking
protocol. And how do you manage multiple rollback journals for multiple
writers? A sort of table level locking is already possible anyway using
attached databases.

I can't see this being a feasible project.

>
> Regards
> Boguslaw Brandys
>


Christian

--
     /"\
     \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
      X                           - AGAINST MS ATTACHMENTS
     / \
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Mikey C
In reply to this post by Clay Dowling
Okay I know very little about these things, but the fact that Access/JET MDB files are serverless (it's just a bunch of Windows dll's) in the same way as SQLite, and that JET implements row and table level locking means I guess it is possible.

If it meant losing ACID compliance, then no, forget about it, but if it meant much bigger database files, then no problem, as long as the row level locking could be turned on or off at compile time (i.e. those who don't care about row level locking, but do care about file size can compile without it).

So if it can be implemented by storing a lock record for every row that is about to be updated in a new system table, then why not?

Of course row level locking will make updates slower, but you can't have fine grained locking and ultimate performance.

As I say, if it could be implemented knowing that:

1. Performance will be slower.
2. Database size will be bigger.
3. Row level locking can be compiled in or out.

Then I think the majority of users would want the benefit of increased write concurrency, even at the expense of speed or database file size.

Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Jay Sprenkle
In reply to this post by D. Richard Hipp
On 6/16/06, [hidden email] <[hidden email]> wrote:
> Mikey C <[hidden email]> wrote:
> >
> > Please implement table and row level locking. :-)
>
>
> If you think you know a way to implement row-level
> locking that does not impose one of the above
> limitations, then please tell me and I will look
> into the matter.

Out of curiosity why won't flock() work?
flock() allows locking an area within a file.
I know there are problems with locking on files accessed on a
network. I also recall when we used locking across an NFS
network with Sun workstations it was very slow. Aquiring locks
to a LONG time.
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

D. Richard Hipp
"Jay Sprenkle" <[hidden email]> wrote:

> On 6/16/06, [hidden email] <[hidden email]> wrote:
> > Mikey C <[hidden email]> wrote:
> > >
> > > Please implement table and row level locking. :-)
> >
> >
> > If you think you know a way to implement row-level
> > locking that does not impose one of the above
> > limitations, then please tell me and I will look
> > into the matter.
>
> Out of curiosity why won't flock() work?

Process A wants to modify the database, so it flock()s
the rows it needs to changes and starts changing them.
But half way in the middle of the change, somebody sends
process A a SIGKILL and it dies.  The OS automatically
releases the flocks as process A dies, leaving the
database half-way updated and in an inconsistent state,
with no locks.

Process B comes along and opens the database, see the
inconsistent state, and reports database corruption.

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Bogusław Brandys
In reply to this post by Christian Smith-4
Christian Smith wrote:

> Bogus�aw Brandys uttered:
>
>> [hidden email] wrote:
>>> Mikey C <[hidden email]> wrote:
>>>> Please implement table and row level locking. :-)
>>>
>>> People commonly believe that doing so must be easy.  I
>>> certainly get a lot of requests for it from people who
>>> think they know how.  But in fact, row-level locking
>>> is extraordinarily difficult.  To my knowledge, nobody has yet come
>>> up with a way to do it unless you:
>>>
>>>   *  Add a server process to coordinate access.
>>>
>>>   *  Accept that the database might be corrupted if
>>>      an application crashes while writing.
>>>
>>>   *  Make writing to the database very, very slow.
>>>
>>>   *  Double the size of the database file.
>>>
>>> If you think you know a way to implement row-level
>>> locking that does not impose one of the above
>>> limitations, then please tell me and I will look
>>> into the matter.
>>
>>
>> I'm not an expert but let me propose such solution:
>> Multi- Generational Architecture like Interbase/Firebird can cope with
>> all except the last point (double size of database file) - however in
>> the last case periodical vaccum could shrink database.
>> There is also no need to have server process - each instance of sqlite
>> library could be a server.
>>
>> Shared lock manager could be required or simply each instance of
>> sqlite library could have separate one.
>> Shared lock manager is tricky idea (maybe it could be for example dumb
>> manager in each sqlite library with shared memory pool and if one
>> instance terminate another one could detect it and play that role?)
>>
>> In fact that is as I fairy know how it's implemented in Firebird
>> Classic Server (where each server process has separate lock manager I
>> suppose)
>> This classic server processes  are spawn by xinetd deamon.
>> I see sqlite in very similar manner : sqlite library is attached to
>> each spawned process which uses it.
>
>
> In order to communicate with the other lock managers, all instances of
> the SQLite library would have to be on the same box.

Or share the same lock data for example within sqlite database special
table (internal like sqlite_master) In that case problem is to serialize
access to lock data ,but we are talking about MG architecture where
pessimistic locks are rare.



> If you want MVCC without process communication (as not all processes
> would be on the same box) you'd need each row update to be synchronous
> and synced, which would be slower than what we have now.

Here I don't quite understand.I thought that MG architecture use
transaction manager to manage transactions. There is not need to sync
row update because each row has many record versions (and old committed
are not removed until vacuum for example) each one with transaction ID
and stamp
Problem: need to serialize transaction manager if working from
concurrent computers on the same database
Problem: without vacuum there is more and more garbage inside database


> The locking protocol could maybe be changed to allow locking at the
> table level, but such a change would be incompatible with the current
> locking protocol. And how do you manage multiple rollback journals for
> multiple writers? A sort of table level locking is already possible
> anyway using attached databases.

This is all about locking (pessimistic) not about MG architecture.

> I can't see this being a feasible project.

Hey! As I stated I'm not an expert. ;-)

Regards
Boguslaw Brandys
Reply | Threaded
Open this post in threaded view
|

RE: SQLite Vs VistaDB - Comparison ???

Fred Williams
In reply to this post by Mikey C
I suggest we don't pick Access/Jet MDB as our shining example of
SQLite's future

Borland's old, dead, and gone Paradox was Access' main reason to come
into existence.  Inspire of outliving Paradox, only because of marketing
reasons, Access has never been able to leapfrog or even measure up to
old Paradox's last few gasps, IMHO.

This periodic "need" for vast "improvements" always seems to end up
proposing breaking the whole reason SQLite exists and why it addresses
its chosen segment of the market so wonderfully.

If someone needs table or row level locking in a multi-user environment,
selecting SQLite is like trying to take that old '57 fuel injected,
ultra lite Corvette and use it for a fully tricked out hearse.  Sounds
to me like a job for "Monster Garage" rather than CVS SQLite :-)

Fred

> -----Original Message-----
> From: Mikey C [mailto:[hidden email]]
> Sent: Friday, June 16, 2006 10:39 AM
> To: [hidden email]
> Subject: Re: [sqlite] SQLite Vs VistaDB - Comparison ???
>
>
>
> Okay I know very little about these things, but the fact that
> Access/JET MDB
> files are serverless (it's just a bunch of Windows dll's) in
> the same way as
> SQLite, and that JET implements row and table level locking
> means I guess it
> is possible.
...

Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Andrew Piskorski
In reply to this post by D. Richard Hipp
On Fri, Jun 16, 2006 at 12:35:33PM -0400, [hidden email] wrote:

> > Out of curiosity why won't flock() work?
>
> Process A wants to modify the database, so it flock()s
> the rows it needs to changes and starts changing them.
> But half way in the middle of the change, somebody sends
> process A a SIGKILL and it dies.  The OS automatically
> releases the flocks as process A dies, leaving the
> database half-way updated and in an inconsistent state,
> with no locks.
>
> Process B comes along and opens the database, see the
> inconsistent state, and reports database corruption.

Would it, at least in principle, be feasible to have Process B then
take a lock (hm, which lock?), notice somehow that A's transaction
failed without either committing or rolling back, read the rollback
journal written earlier by Process A, and rollback A's half-done work?
What in practice makes that not a good idea?

Would using a non-overwriting MVCC storage layer a la PostgreSQL (but
still using client SQLite processes only, no client/server
arrangement) make any of the above easier or better?

Note, I'm not suggesting that you should implement anything like this
in SQLite, I'm just curious in general...

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Doug Currie-2
Friday, June 16, 2006, 5:32:32 PM, Andrew Piskorski wrote:

> Would using a non-overwriting MVCC storage layer a la PostgreSQL (but
> still using client SQLite processes only, no client/server
> arrangement) make any of the above easier or better?

See http://www.sqlite.org/cvstrac/wiki?p=BlueSky the shadow pager.

> Note, I'm not suggesting that you should implement anything like this
> in SQLite...

Me neither. ;-)

e

--
Doug Currie
Londonderry, NH

Reply | Threaded
Open this post in threaded view
|

RE: SQLite Vs VistaDB - Comparison ???

Robert Simpson
In reply to this post by RohitPatel9999
> -----Original Message-----
> From: RohitPatel9999 [mailto:[hidden email]]
> Sent: Friday, June 16, 2006 1:58 AM
> To: [hidden email]
> Subject: [sqlite] SQLite Vs VistaDB - Comparison ???

Getting back on track ...

> VistaDB  
> HomePage : www.vistadb.com
> More Features : www.vistadb.com/features.asp
> VistaDB is a commercial embedded SQL database engine (only
> for .NET and
> Win32)

Only supports Win32 desktop.  No 64-bit available, no CE support either.
OleDB support is missing some features, and the .NET 2.0 provider is a
recompile of their 1.1 provider and has almost no support for any of the
ADO.NET 2.0 features.

> Encryption?: Secure Blowfish encryption and password protection

There are several of us providing free SQLite implementations with built-in
encryption.

> Platforms? : For Different Window Versions

Again, only 32-bit desktops.  There is a CE provider that lets you connect
to a desktop database over the wire, but there's no embedded version of
VistaDB for CE.

> Fast performance

Not.  VistaDb is slower than Access/JET in nearly every test I ran, from
bulk inserts to simple indexed joins to multi-table joins.  Access trounced
VistaDb in every category -- and SQLite trounced Access in every category.

> International support
> Supports C#, VB.NET, Delphi, C++Builder, VB and classic ASP

There've been lots of complaints about VistaDb's international support.  It
has no unicode or UTF8/16 support.

> More Features : www.vistadb.com/features.asp
>
>
> Please put forward your views, ideas, thoughts, comparisons
> (if any) ??? I
> might have missed many points of comparison/similarity.

VistaDB's database size is also massive, but its one area that it beat Jet
in my tests:

http://sqlite.phxsoftware.com/forums/622/ShowPost.aspx

Robert


Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

Eduardo Morras
In reply to this post by D. Richard Hipp

> > > into the matter.
> >
> > Out of curiosity why won't flock() work?
>
>Process A wants to modify the database, so it flock()s
>the rows it needs to changes and starts changing them.
>But half way in the middle of the change, somebody sends
>process A a SIGKILL and it dies.  The OS automatically
>releases the flocks as process A dies, leaving the
>database half-way updated and in an inconsistent state,
>with no locks.
>
>Process B comes along and opens the database, see the
>inconsistent state, and reports database corruption.
>
>--
>D. Richard Hipp   <[hidden email]>

Perhaps the way do it is journaling files. I have not study SQLite
source codes in detail but i think that before an update or a create
or a delete, it must do a search (so only a read) to know which pages
and which bytes must be modified. So, Process A wants to modify the
database and put on a master table which pages want to modify and
where (from byte M to N), Process B checks the master table and if it
want access the same info, it gets a lock or busy error, if not,
update master table with it own pages and bytes. Process A writes the
changes to pages in a journal file and if Process B was able to
modify do the same in another journal. Both A and B deletes theirs
rows from master table. Then mix both journal files and modify database file.

HTH


------------------------------------------------------------------------------------------------------------------------------------------------------------
#The Unix Guru's View of Sex unzip ; strip ; touch ; grep ; finger ;
mount ; fsck ; more ; yes ; umount ; sleep

Reply | Threaded
Open this post in threaded view
|

Re: SQLite Vs VistaDB - Comparison ???

RohitPatel9999
In reply to this post by D. Richard Hipp
drh@... wrote:
>
> Process A wants to modify the database, so it flock()s
> the rows it needs to changes and starts changing them.
> But half way in the middle of the change, somebody sends
> process A a SIGKILL and it dies.  The OS automatically
> releases the flocks as process A dies, leaving the
> database half-way updated and in an inconsistent state,
> with no locks.

When Process A is being killed and OS automatically
releases the flocks, will rollback journal be present or not ?

I think, in this situation, hot journal will be left when
Process A is killed.

> Process B comes along and opens the database, see the
> inconsistent state, and reports database corruption.
>

If Process B finds a rollback journal...then...it can use
that to rollback database and bring it to consistent state.

-------


Curiously thinking on this...

What if a new SYSTEM-LOCK-MGMT-TABLE is added in SQLite database
managed by itself for locking management ? (Surely, it is not an easy
task to manage SYSTEM-LOCK-MGMT-TABLE..!!! )

Assuming that, if there is one an added SYSTEM-LOCK-TABLE in
SQLite database managed by itself for locking management.

Then when Process A wants to modify the database and it flock()s
the rows it needs to changes, it should add corresponding
info as record in SYSTEM-LOCK-TABLE for that...

When somebody sends process A a SIGKILL and it dies, OS
will automatically release flocks as process A dies. Process
B (any other process accessing database) can find necessary
locking related info from SYSTEM-LOCK-TABLE, as well as hot journal
and so Process B can bring database back to consistent state.

Rohit