Re: [sqlite] database locked on select

[Keith Medcalf]

or 1 thread...or 6000 threads...makes no difference whatever.  Unless you 
changed the default from SERIALIZED to something that does not apply.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Deon Brewis
>Sent: Sunday, 27 May, 2018 16:50
>To: SQLite mailing list
>Subject: Re: [sqlite] database locked on select
>
>By one connection doing SELECT and UPDATE, do you mean multi-threaded
>mode and using the connection from 2 threads?
>
>- Deon
>
>-Original Message-
>From: sqlite-users  On
>Behalf Of Simon Slavin
>Sent: Sunday, May 27, 2018 3:39 PM
>To: SQLite mailing list 
>Subject: Re: [sqlite] database locked on select
>
>On 27 May 2018, at 7:30pm, Torsten Curdt  wrote:
>
>> I am doing a select, then iterate through the resultset and on each
>> row call update on that row.
>> I am using the golang driver and ran into the issue that on the
>update
>> the database is still locked from the select.
>
>Are you usihg one connection to do both the SELECT and the UPDATE ?
>Or are you using two different connections, one for the SELECT and
>one for the UPDATE ?
>
>The second method will not work.  The SELECT connection has the
>database locked until you tell your interface you have finished with
>the SELECT.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Abroży Nieprzełoży]

BTW why not to update all rows by single update query?

2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> I am doing a select, then iterate through the resultset and on each row
> call update on that row.
> I am using the golang driver and ran into the issue that on the update the
> database is still locked from the select.
>
>   https://github.com/mattn/go-sqlite3/issues/569
>
> I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and IIUC
> these types of updates should be possible since version 3.3.7 though - and
> I am using 3.19.3.
>
> Any suggestion on how to track down why the updates fail?
>
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[Abroży Nieprzełoży]

Alternatively you can alloc physical memory using AWE mechanism.
AWE requires SeLockMemoryPrivilege, so you may have to run the
application on an administator account (or other account with
sufficient rigths).
When using AWE there is no need to increase working set size because
AWE pages are not included in the working set.

---
int enableLockMemoryPrivilege()
{
int rc;
DWORD err;
HANDLE hToken;
TOKEN_PRIVILEGES privilege;
memset(, 0, sizeof(privilege));
privilege.PrivilegeCount = 1;
privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
rc = LookupPrivilegeValue(NULL, SE_LOCK_MEMORY_NAME,
[0].Luid);
if (!rc)
return -1;
rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, 
);
if (!rc)
return -2;
rc = AdjustTokenPrivileges(hToken, 0, , sizeof(privilege),
NULL, NULL);
err = GetLastError();
CloseHandle(hToken);
if (!rc || err)
return -3;
return 0;
}

struct PhysMem
{
void* pMem;
SIZE_T sMem;
ULONG_PTR nPages;
ULONG_PTR* aPages;
};

void unmapAndFreePhysicalPages(struct PhysMem* mem)
{
if(mem->pMem)
VirtualFree(mem->pMem, 0, MEM_RELEASE);
if (mem->aPages)
{
FreeUserPhysicalPages(GetCurrentProcess(), >nPages, 
mem->aPages);
free(mem->aPages);
}
memset(mem, 0, sizeof(*mem));
}

int allocAndMapPhysicalPages(struct PhysMem* mem, SIZE_T size)
{
int rc;
SYSTEM_INFO sysInfo;
memset(mem, 0, sizeof(*mem));
GetNativeSystemInfo();
mem->nPages = size / sysInfo.dwPageSize;
if (size % sysInfo.dwPageSize > 0)
mem->nPages++;
size = mem->nPages * sysInfo.dwPageSize;
mem->aPages = (ULONG_PTR*)calloc(mem->nPages, sizeof(ULONG_PTR));
if (!mem->aPages)
return -1;
rc = AllocateUserPhysicalPages(GetCurrentProcess(), >nPages, 
mem->aPages);
if (!rc)
{
free(mem->aPages);
memset(mem, 0, sizeof(*mem));
return -2;
}
mem->sMem = size = mem->nPages * sysInfo.dwPageSize;
mem->pMem = VirtualAlloc(NULL, mem->sMem, MEM_RESERVE | MEM_PHYSICAL,
PAGE_READWRITE);
if (!mem->pMem)
{
unmapAndFreePhysicalPages(mem);
return -3;
}
rc = MapUserPhysicalPages(mem->pMem, mem->nPages, mem->aPages);
if (!rc)
{
unmapAndFreePhysicalPages(mem);
return -4;
}
return 0;
}

int configSqlitePhysMemory()
{
int rc;
struct PhysMem mem;
rc = allocAndMapPhysicalPages(, 64 * 1024 * 1024);
if (rc < 0)
return -1;
rc = sqlite3_config(SQLITE_CONFIG_HEAP, mem.pMem, (int)mem.sMem, 64);
if (rc != SQLITE_OK)
{
unmapAndFreePhysicalPages();
return -2;
}
return 0;
}

int enableLockMemPrivilegeAndConfigSqliteMemory()
{
int rc;
rc = enableLockMemoryPrivilege();
if (rc < 0)
return -1;
rc = configSqlitePhysMemory();
if (rc < 0)
return -2;
return 0;
}
---



2018-05-28 0:23 GMT+02:00, Abroży Nieprzełoży
:
> Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
> you may use the function enableIncreaseWorkingSetPrivilege listed
> below, it should return 0 on success - then you can use
> SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
> Working set sizes must be multiple of page size (typically 4096 bytes).
> https://msdn.microsoft.com/en-us/library/ms686234
>
> -
> int enableIncreaseWorkingSetPrivilege()
> {
>   int rc;
>   DWORD err;
>   HANDLE hToken;
>   TOKEN_PRIVILEGES privilege;
>   memset(, 0, sizeof(privilege));
>   privilege.PrivilegeCount = 1;
>   privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
>   rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
> [0].Luid);
>   if (!rc)
>   return -1;
>   rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES,
> );
>   if (!rc)
>   return -2;
>   rc = AdjustTokenPrivileges(hToken, 0, , sizeof(privilege),
> NULL, NULL);
>   err = GetLastError();
>   CloseHandle(hToken);
>   if (!rc || err)
>   return -3;
>   return 0;
> }
> -
>
>
>
>
> You can also compile SQLite with -DSQLITE_ZERO_MALLOC=1 and
> -DSQLITE_ENABLE_MEMSYS5=1 and configure it to use buffer of locked
> memory.
> Locked 

Re: [sqlite] database locked on select

[Torsten Curdt]

There is no multi threading. Just a single thread and only one
connection/handle.
While iterating through the resultset I am also trying to execute an update
for every row. Along the lines of:

  resultset = db.exec(`select`)
  foreach row in resultset {
db.exec(`update`)
  }

I don't want to read the whole resultset into memory.

After a lot of searching and trial and error I found that

  PRAGMA journal_mode=WAL

seems to do the trick.
I don't fully understand why though.

cheers,
Torsten
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Simon Slavin]

On 27 May 2018, at 11:49pm, Deon Brewis  wrote:

> By one connection doing SELECT and UPDATE, do you mean multi-threaded mode 
> and using the connection from 2 threads?

A connection cannot lock the database against itself.  If you are doing two 
operations with one connection, there can be no SQLite lock.

However, there may be another mechanism which is doing another kind of locking. 
 And it may be something that the golang driver is doing, rather than something 
built into SQLite.  I hope someone else knows.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Deon Brewis]

By one connection doing SELECT and UPDATE, do you mean multi-threaded mode and 
using the connection from 2 threads?

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Sunday, May 27, 2018 3:39 PM
To: SQLite mailing list 
Subject: Re: [sqlite] database locked on select

On 27 May 2018, at 7:30pm, Torsten Curdt  wrote:

> I am doing a select, then iterate through the resultset and on each 
> row call update on that row.
> I am using the golang driver and ran into the issue that on the update 
> the database is still locked from the select.

Are you usihg one connection to do both the SELECT and the UPDATE ?  Or are you 
using two different connections, one for the SELECT and one for the UPDATE ?

The second method will not work.  The SELECT connection has the database locked 
until you tell your interface you have finished with the SELECT.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Simon Slavin]

On 27 May 2018, at 7:30pm, Torsten Curdt  wrote:

> I am doing a select, then iterate through the resultset and on each row
> call update on that row.
> I am using the golang driver and ran into the issue that on the update the
> database is still locked from the select.

Are you usihg one connection to do both the SELECT and the UPDATE ?  Or are you 
using two different connections, one for the SELECT and one for the UPDATE ?

The second method will not work.  The SELECT connection has the database locked 
until you tell your interface you have finished with the SELECT.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[Abroży Nieprzełoży]

Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
you may use the function enableIncreaseWorkingSetPrivilege listed
below, it should return 0 on success - then you can use
SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
Working set sizes must be multiple of page size (typically 4096 bytes).
https://msdn.microsoft.com/en-us/library/ms686234

-
int enableIncreaseWorkingSetPrivilege()
{
int rc;
DWORD err;
HANDLE hToken;
TOKEN_PRIVILEGES privilege;
memset(, 0, sizeof(privilege));
privilege.PrivilegeCount = 1;
privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
[0].Luid);
if (!rc)
return -1;
rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, 
);
if (!rc)
return -2;
rc = AdjustTokenPrivileges(hToken, 0, , sizeof(privilege),
NULL, NULL);
err = GetLastError();
CloseHandle(hToken);
if (!rc || err)
return -3;
return 0;
}
-




You can also compile SQLite with -DSQLITE_ZERO_MALLOC=1 and
-DSQLITE_ENABLE_MEMSYS5=1 and configure it to use buffer of locked
memory.
Locked memory won't get swapped so SQLite won't encounter page faults.

Function configSqliteMemory listed below allocs few megabytes of
memory, locks it and configures SQLite. It also increases working set
size because maximum quantity of memory that process can lock is equal
to minimum working set size (locked memory is included in the working
set).

-
int configSqliteMemory()
{
int rc;
void* memPtr;
SIZE_T memSize = 64 * 1024 * 1024;
SIZE_T wsMinSize, wsMaxSize;
rc = GetProcessWorkingSetSize(GetCurrentProcess(), , 
);
if (!rc)
return -1;
wsMinSize += memSize;
wsMaxSize += memSize;
rc = SetProcessWorkingSetSize(GetCurrentProcess(), wsMinSize, 
wsMaxSize);
if (!rc)
return -2;
memPtr = VirtualAlloc(NULL, memSize, MEM_RESERVE | MEM_COMMIT, 
PAGE_READWRITE);
if (!memPtr)
return -3;
rc = VirtualLock(memPtr, memSize);
if (!rc)
{
VirtualFree(memPtr, 0, MEM_RELEASE);
return -4;
}
rc = sqlite3_config(SQLITE_CONFIG_HEAP, memPtr, (int)memSize, 64);
if (rc != SQLITE_OK)
{
VirtualFree(memPtr, 0, MEM_RELEASE);
return -5;
}
return 0;
}
-




2018-05-27 20:03 GMT+02:00, x :
> Starting to mess about with windows handles and pages I’ve never heard of is
> beyond my pain threshold Abrozy. Thanks anyway.
>
>
>
> 
> From: sqlite-users  on behalf
> of Abroży Nieprzełoży 
> Sent: Sunday, May 27, 2018 5:23:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] This is driving me nuts
>
> I think you can experiment with changing the the working set size
> limits and see what the effect will be.
> https://msdn.microsoft.com/en-us/library/cc441804
>
> 2018-05-27 17:09 GMT+02:00, curmudgeon :
>> It seems the array was being optimised away. I had to initialise every
>> value
>> to get the OS to claim the RAM. Once I did that the timings for the array
>> were on a par with the vector with the second pass being slower than the
>> first.
>>
>> While that clears up that part of the mystery I'm no closer to a solution.
>> Going back to the latest set of results why is the assignments in the
>> second
>> pass taking so much longer when there's still 5+ GB of memory free?
>>
>>
>>
>> --
>> Sent from: http://sqlite.1065341.n5.nabble.com/
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database locked on select

[Torsten Curdt]

I am doing a select, then iterate through the resultset and on each row
call update on that row.
I am using the golang driver and ran into the issue that on the update the
database is still locked from the select.

  https://github.com/mattn/go-sqlite3/issues/569

I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and IIUC
these types of updates should be possible since version 3.3.7 though - and
I am using 3.19.3.

Any suggestion on how to track down why the updates fail?

cheers,
Torsten
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite creating disk file for memory db

[Abhishek]

I tried to create a shareable in-memory database as per the documentation
provided on SQLite  Site. But I end up finding the solution to the problem. 

*Here is my code in C#*:

 var connectionString = "Data Source=sharedmemdb;Mode=Memory;Cache=Shared";

using (var connection1 = new SQLiteConnection(connectionString))
{
connection1.Open();

var command1 = connection1.CreateCommand();
command1.CommandText =
"CREATE TABLE Message ( Text TEXT );" +
"INSERT INTO Message ( Text ) VALUES ( 'Is there anybody
out there?' );";
command1.ExecuteNonQuery();

using (var connection2 = new
SQLiteConnection(connectionString))
{
connection2.Open();

var command2 = connection2.CreateCommand();
command2.CommandText = "SELECT Text FROM Message;";

   
var message = command2.ExecuteScalar() as string;
}
}

If I execute this code, it will create in-memory DB named as **sharedmemdb** 
and shared cache is enabled while making the connection, so this connection
accessible to other connections also. If I run this first time this works
pretty fine but if I close the application and run again it throws error
"Table Message already exists" and this looks very strange as I created the
table in-memory and this should not be available if application restarts. 

After getting this error, I looked into the application directory and found
the file "sharedmemdb" which means SQLite is not creating the shareable
in-memory DB.

Any clue why this is happening?

Thanks
Abhishek




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

Starting to mess about with windows handles and pages I’ve never heard of is 
beyond my pain threshold Abrozy. Thanks anyway.




From: sqlite-users  on behalf of 
Abroży Nieprzełoży 
Sent: Sunday, May 27, 2018 5:23:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

I think you can experiment with changing the the working set size
limits and see what the effect will be.
https://msdn.microsoft.com/en-us/library/cc441804

2018-05-27 17:09 GMT+02:00, curmudgeon :
> It seems the array was being optimised away. I had to initialise every value
> to get the OS to claim the RAM. Once I did that the timings for the array
> were on a par with the vector with the second pass being slower than the
> first.
>
> While that clears up that part of the mystery I'm no closer to a solution.
> Going back to the latest set of results why is the assignments in the second
> pass taking so much longer when there's still 5+ GB of memory free?
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[Abroży Nieprzełoży]

I think you can experiment with changing the the working set size
limits and see what the effect will be.
https://msdn.microsoft.com/en-us/library/cc441804

2018-05-27 17:09 GMT+02:00, curmudgeon :
> It seems the array was being optimised away. I had to initialise every value
> to get the OS to claim the RAM. Once I did that the timings for the array
> were on a par with the vector with the second pass being slower than the
> first.
>
> While that clears up that part of the mystery I'm no closer to a solution.
> Going back to the latest set of results why is the assignments in the second
> pass taking so much longer when there's still 5+ GB of memory free?
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[curmudgeon]

It seems the array was being optimised away. I had to initialise every value
to get the OS to claim the RAM. Once I did that the timings for the array
were on a par with the vector with the second pass being slower than the
first.

While that clears up that part of the mystery I'm no closer to a solution.
Going back to the latest set of results why is the assignments in the second
pass taking so much longer when there's still 5+ GB of memory free?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

Strange. I repeated the test with v an int64 array (see below) however the 
FreeMB() doesn’t change. Both Memi’s return pretty much the value of Mem 
???

for (int i=0; i<2; i++)
{
v=new int64_t[Size[i]];
int64_t Memi=FreeMBs();

clock_t Start=clock();
for (int i=0; i

Re: [sqlite] This is driving me nuts

[x]

I’ve changed the app to populate v with the query result and noted the free ram 
before and after each resize. I’m hoping that addresses some of the concerns re 
compiler optimisations even if it doesn’t supply any real answers? Results were 
similar to before.

#include 
#include 
#pragma hdrstop
#pragma argsused
#include 
#include 
#include 
#include 
#include 
#include "sqlite.h"

std::vector v;
const int Size[]={11200,10}; // 112 million, 1 billion

uint64_t FreeMBs()
{
MEMORYSTATUSEX status;
status.dwLength = sizeof(status);
GlobalMemoryStatusEx();
return status.ullAvailPhys / (1024 * 1024);
}

int _tmain(int argc, _TCHAR* argv[])
{
sqlite3 *DB;
sqlite3_open("c:/SQLiteData/MyTemp.db",);
sqlite3_stmt *stmt;
sqlite3_prepare_v2(DB,"select RowID from Big order by 
RowID",-1,,NULL);

std::cout << FreeMBs() << " MB" << std::endl;

for (int i=0; i<2; i++)
{
v.resize(Size[i]);
v.shrink_to_fit();
int64_t Memi=FreeMBs();

clock_t Start=clock();
for (int i=0; i