[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Ok, the problem is solved. Thanks for the support.

2016-02-03 13:13 GMT-02:00 Vin?cius da Silva :

> I think I have discovered the problem. SQLiteManager::init( const string&
> dbFileName, const bool deleteDbFileFlag = true ) was deleting and
> recreating the database file for each new connection. That was probably
> corrupting SQLite state and a SQLITE_IOERR_DELETE_NOENT was correctly being
> returned at transaction ending, since the original file to write was
> replaced by another one. Some multithreaded tests are passing already. I
> will finish the tests to put the final verdict here.
>
> 2016-02-03 12:56 GMT-02:00 Vin?cius da Silva :
>
>> With a single thread and two interleaved transactions, the tests hangs in
>> what seems a deadlock.
>>
>> 2016-02-03 12:21 GMT-02:00 Clemens Ladisch :
>>
>>> Vin?cius da Silva wrote:
>>> > Using BEGIN EXCLUSIVE TRANSACTION in place of BEGIN TRANSACTION does
>>> not
>>> > change the result
>>>
>>> I meant, try executing them so that they are guaranteed to conflict,
>>> like this:
>>>
>>>   db1.init("same_file");
>>>   db2.init("same_file");
>>>   db1.beginTransactionExclusive();
>>>   db2.beginTransactionExclusive();
>>>
>>>
>>> Regards,
>>> Clemens
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Best Regards,
>>
>> Vin?cius da Silva ,
>> Ph. D. Student.
>> PESC  - UFRJ 
>>
>
>
>
> --
> Best Regards,
>
> Vin?cius da Silva ,
> Ph. D. Student.
> PESC  - UFRJ 
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Vin?cius da Silva wrote:
> Using BEGIN EXCLUSIVE TRANSACTION in place of BEGIN TRANSACTION does not
> change the result

I meant, try executing them so that they are guaranteed to conflict, like this:

  db1.init("same_file");
  db2.init("same_file");
  db1.beginTransactionExclusive();
  db2.beginTransactionExclusive();


Regards,
Clemens

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

I think I have discovered the problem. SQLiteManager::init( const string&
dbFileName, const bool deleteDbFileFlag = true ) was deleting and
recreating the database file for each new connection. That was probably
corrupting SQLite state and a SQLITE_IOERR_DELETE_NOENT was correctly being
returned at transaction ending, since the original file to write was
replaced by another one. Some multithreaded tests are passing already. I
will finish the tests to put the final verdict here.

2016-02-03 12:56 GMT-02:00 Vin?cius da Silva :

> With a single thread and two interleaved transactions, the tests hangs in
> what seems a deadlock.
>
> 2016-02-03 12:21 GMT-02:00 Clemens Ladisch :
>
>> Vin?cius da Silva wrote:
>> > Using BEGIN EXCLUSIVE TRANSACTION in place of BEGIN TRANSACTION does not
>> > change the result
>>
>> I meant, try executing them so that they are guaranteed to conflict, like
>> this:
>>
>>   db1.init("same_file");
>>   db2.init("same_file");
>>   db1.beginTransactionExclusive();
>>   db2.beginTransactionExclusive();
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Best Regards,
>
> Vin?cius da Silva ,
> Ph. D. Student.
> PESC  - UFRJ 
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

With a single thread and two interleaved transactions, the tests hangs in
what seems a deadlock.

2016-02-03 12:21 GMT-02:00 Clemens Ladisch :

> Vin?cius da Silva wrote:
> > Using BEGIN EXCLUSIVE TRANSACTION in place of BEGIN TRANSACTION does not
> > change the result
>
> I meant, try executing them so that they are guaranteed to conflict, like
> this:
>
>   db1.init("same_file");
>   db2.init("same_file");
>   db1.beginTransactionExclusive();
>   db2.beginTransactionExclusive();
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Table entries disappearing for unknown reason.

[Hick Gunter]

Does the error still occur if you run with only one thread? Does each thread 
have ist own connection or are you sharing a single connection across multiple 
threads?

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von 
Vin?cius da Silva
Gesendet: Dienstag, 02. Februar 2016 22:08
An: SQLite mailing list
Betreff: Re: [sqlite] Table entries disappearing for unknown reason.

The error occur after commenting the SQLite setup pragmas.  All threads access 
the same database file and the same table. I'm using an ext4 file system with 
Ubuntu.

2016-02-02 17:58 GMT-02:00 Clemens Ladisch :

> Vin?cius da Silva wrote:
> > after commenting the pragmas,
>
> Which ones?  omp or SQLite?
>
> > SQLiteManager is throwing
> > an exception because stepping the statement in endTransaction() is
> > returning SQLITE_IOERR_DELETE_NOENT
> > <https://sqlite.org/rescode.html#ioerr_delete_noent> in the parallel
> loop.
>
> Do your threads access the same database file?
>
> What file system are you using?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Best Regards,

Vin?cius da Silva <http://www.lcg.ufrj.br/Members/viniciusdasilva>,
Ph. D. Student.
PESC <http://www.cos.ufrj.br> - UFRJ <http://www.ufrj.br> 
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Vin?cius da Silva wrote:
> after commenting the pragmas,

Which ones?  omp or SQLite?

> SQLiteManager is throwing
> an exception because stepping the statement in endTransaction() is
> returning SQLITE_IOERR_DELETE_NOENT
>  in the parallel loop.

Do your threads access the same database file?

What file system are you using?


Regards,
Clemens

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Using BEGIN EXCLUSIVE TRANSACTION in place of BEGIN TRANSACTION does not
change the result: SQLITE_IOERR_DELETE_NOENT
 is being returned when
stepping the END TRANSACTION statement. I'm using ext4 with Ubuntu.

2016-02-02 19:40 GMT-02:00 Clemens Ladisch :

> Vin?cius da Silva wrote:
> > Each thread access the SQLiteManager array m_dbs, indexed by its unique
> > index provided by openmp. Since SQLiteManager opens a connection in its
> > init() method each thread has a private connection. The tests are passing
> > when I set the parallel loops for one thread only.
>
> Try executing "BEGIN EXCLUSIVE" through two separate connections to the
> same DB file.  Does it work?
>
> If yes, which file system are you using?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Each thread access the SQLiteManager array m_dbs, indexed by its unique
index provided by openmp. Since SQLiteManager opens a connection in its
init() method each thread has a private connection. The tests are passing
when I set the parallel loops for one thread only.

2016-02-02 19:27 GMT-02:00 Hick Gunter :

> Does the error still occur if you run with only one thread? Does each
> thread have ist own connection or are you sharing a single connection
> across multiple threads?
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Vin?cius da
> Silva
> Gesendet: Dienstag, 02. Februar 2016 22:08
> An: SQLite mailing list
> Betreff: Re: [sqlite] Table entries disappearing for unknown reason.
>
> The error occur after commenting the SQLite setup pragmas.  All threads
> access the same database file and the same table. I'm using an ext4 file
> system with Ubuntu.
>
> 2016-02-02 17:58 GMT-02:00 Clemens Ladisch :
>
> > Vin?cius da Silva wrote:
> > > after commenting the pragmas,
> >
> > Which ones?  omp or SQLite?
> >
> > > SQLiteManager is throwing
> > > an exception because stepping the statement in endTransaction() is
> > > returning SQLITE_IOERR_DELETE_NOENT
> > > <https://sqlite.org/rescode.html#ioerr_delete_noent> in the parallel
> > loop.
> >
> > Do your threads access the same database file?
> >
> > What file system are you using?
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Best Regards,
>
> Vin?cius da Silva <http://www.lcg.ufrj.br/Members/viniciusdasilva>,
> Ph. D. Student.
> PESC <http://www.cos.ufrj.br> - UFRJ <http://www.ufrj.br>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva <http://www.lcg.ufrj.br/Members/viniciusdasilva>,
Ph. D. Student.
PESC <http://www.cos.ufrj.br> - UFRJ <http://www.ufrj.br>

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

The error occur after commenting the SQLite setup pragmas.  All threads
access the same database file and the same table. I'm using an ext4 file
system with Ubuntu.

2016-02-02 17:58 GMT-02:00 Clemens Ladisch :

> Vin?cius da Silva wrote:
> > after commenting the pragmas,
>
> Which ones?  omp or SQLite?
>
> > SQLiteManager is throwing
> > an exception because stepping the statement in endTransaction() is
> > returning SQLITE_IOERR_DELETE_NOENT
> >  in the parallel
> loop.
>
> Do your threads access the same database file?
>
> What file system are you using?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Using only omp_get_thread_num() in the parallel loop do not change the
results. However, after commenting the pragmas, SQLiteManager is throwing
an exception because stepping the statement in endTransaction() is
returning SQLITE_IOERR_DELETE_NOENT
 in the parallel loop.
This behavior is reproduced in every multithreaded code that uses
SQLiteManager (the automated tests for SQLiteManager that exercise database
connections in multithreaded code are returning the same error).

2016-02-02 8:46 GMT-02:00 Clemens Ladisch :

> Dominique Devienne wrote:
> > On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch 
> wrote:
> >
> >> Vin?cius da Silva wrote:
> >>> #pragma omp parallel for
> >>> for( int i = 0; i < dispatchedThreads; ++i )
> >>
> >> Does OMP guarantee that there is exactly one thread for each
> >> loop iteration?
> >
> > Yes, OpenMP will execute each *iteration* in a single thread.
> > The set of iterations will be spread on 1 or more thread, depending
> > on OMP_NUM_THREADS.
>
> If i and omp_get_thread_num() are not the same, the code breaks.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Dominique Devienne wrote:
> On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch  
> wrote:
>
>> Vin?cius da Silva wrote:
>>> #pragma omp parallel for
>>> for( int i = 0; i < dispatchedThreads; ++i )
>>
>> Does OMP guarantee that there is exactly one thread for each
>> loop iteration?
>
> Yes, OpenMP will execute each *iteration* in a single thread.
> The set of iterations will be spread on 1 or more thread, depending
> on OMP_NUM_THREADS.

If i and omp_get_thread_num() are not the same, the code breaks.


Regards,
Clemens

[sqlite] Table entries disappearing for unknown reason.

[Dominique Devienne]

On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch  wrote:

> Vin?cius da Silva wrote:
> > #pragma omp parallel for
> > for( int i = 0; i < dispatchedThreads; ++i )
>
> Does OMP guarantee that there is exactly one thread for each
> loop iteration?
>

Yes, OpenMP will execute each *iteration* in a single thread.
The set of iterations will be spread on 1 or more thread, depending
on OMP_NUM_THREADS.
Default is to evenly divide the [0, dispatchedThreads) range into
per-thread ranges, and run each iteration-range on a single thread. --DD

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Vin?cius da Silva wrote:
> #pragma omp parallel for
> for( int i = 0; i < dispatchedThreads; ++i )

Does OMP guarantee that there is exactly one thread for each loop
iteration?

> sqlite3_exec( m_db, "PRAGMA synchronous = OFF", NULL, NULL, NULL );
> sqlite3_exec( m_db, "PRAGMA journal_mode = OFF", NULL, NULL, NULL );

Does it work without those?


Regards,
Clemens

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Hello.

I'm developing a system for massive point cloud rendering and use SQLite in
order to store nodes of a spatial subdivision tree of the space where the
points lie in, aka Octree. For this, I have a very simple table with just a
key, which is an integer identifier of the node, aka Morton Code, and a
blob, which is the node itself. The system uses the table to store nodes
whenever a main memory budget is reached, in order to deal with point
clouds of arbitrary size.

My problem is that even if I can correctly insert the nodes into the table
(have checked that by querying the database after insertion), it seems that
they are deleted moments after, for an unknown reason. The database
requirements for the project are peak and correct performance in a
multithreaded environment, without any care about rollbacks, since all
transactions are done by the system itself and by now the database is
meaningless after system exit. To manage database connections I have the
SQLiteManager class and the database setup is the following one:

template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::init( const
string& dbFileName, const bool deleteDbFileFlag )
{
if( deleteDbFileFlag )
{
deleteFile( dbFileName );
}

checkReturnCode(
sqlite3_open_v2( dbFileName.c_str(), _db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX,
 NULL ),
SQLITE_OK
);
sqlite3_exec( m_db, "PRAGMA synchronous = OFF", NULL, NULL, NULL );
sqlite3_exec( m_db, "PRAGMA journal_mode = OFF", NULL, NULL, NULL );
sqlite3_busy_handler( m_db,
[]( void*, int )
{
// Always try again to make the database access
return 1;
},
NULL
);

createTables();
createStmts();
}

In addition to init(), there is an API to inserted nodes and to create
transactions. The serialization and statement step, reset and return code
checking are already proven correct by automated tests .

template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::insertNode( const
MortonCode& morton, const OctreeNode& node )
{
byte* serialization;
size_t blobSize = node.serialize(  );

checkReturnCode( sqlite3_bind_int64( m_nodeInsertion, 1,
morton.getBits() ), SQLITE_OK );
checkReturnCode( sqlite3_bind_blob( m_nodeInsertion, 2,
serialization, blobSize, SQLITE_STATIC ), SQLITE_OK );

safeStep( m_nodeInsertion );
safeReset( m_nodeInsertion );

delete[] serialization;
}

template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::beginTransaction()
{
safeStep( m_beginTransaction );
safeReset( m_beginTransaction );
}

template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::endTransaction()
{
safeStep( m_endTransaction );
safeReset( m_endTransaction );
}

The statements creation is done by this other method:

template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::createStmts()
{
safePrepare( "INSERT OR REPLACE INTO Nodes VALUES ( ?, ? );",
_nodeInsertion );
safePrepare( "SELECT Node FROM Nodes WHERE Morton = ?;",
_nodeQuery );
safePrepare( "SELECT Node FROM Nodes WHERE Morton BETWEEN ? AND
?;", _nodeIntervalQuery );
safePrepare( "SELECT * FROM Nodes;", _allIdNodesQuery );
safePrepare( "SELECT Morton, Node FROM Nodes WHERE Morton BETWEEN ?
AND ?;", _nodeIntervalIdQuery );
safePrepare( "DELETE FROM Nodes WHERE Morton BETWEEN ? AND ?;",
_nodeIntervalDeletion );
safePrepare( "BEGIN TRANSACTION", _beginTransaction );
safePrepare( "END TRANSACTION", _endTransaction );
}

This API is used by the HierarchyCreator class, which have an array of
SQLiteManager objects, each one used by a thread in the creation process.

// BEGIN PARALLEL WORKLIST PROCESSING.
#pragma omp parallel for
for( int i = 0; i < dispatchedThreads; ++i )
{
...

if( isReleasing )
{
m_dbs[ i ].beginTransaction();
}

   ... (Child nodes are processed,  creating its parent
inner as result) ...

releaseSiblings( inner.child(),
omp_get_thread_num(), m_octreeDim );

if( isReleasing )
{
m_dbs[ i ].endTransaction();
}
}