3 ms·

> Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId You

by lrossi 6y ago

> Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId You mean for generating unique primary keys? Why would last insert row id be slow? > and in cases where transactional scopes are required Could you elaborate on what you mean by this?

bob1029 6y ago
LastInsertRowId is not slow, but if you are inserting on the same connection from multiple threads, you will require a mutex or you will be getting other threads' row ids. Transactional scopes meaning scenarios like debiting one account and crediting another. This is something you can also manage with locking with application-level primitives.
- Aeolun 6y ago
  So the mutex in sqlite (for multiple connections) is worse than the one you implement in your own application? I’d assume the DB would be most efficient at handling it’s own. At least to the extend that it wouldn’t garner a 100x speedup to do it in app.
  - bob1029 6y ago
    Yes it is substantially worse to use multiple connections vs a single connection. This is fairly easy to test in a few lines of code. We need to remember that opening a connection to SQLite is like opening a file on disk. Creating/destroying file handles requires far more resources and ceremony than taking out a mutex on a file that is never closed.
    - liuliu 6y ago
      That doesn't sound right. SQLite's lock for writes is not the best, but it is still pthread mutex under the hood. Are you sure your compilation options for SQLite is right? One common pitfall is compiling without `-DHAVE_USLEEP`. In absence of that flag, SQLite will use sleep in case of conflict, and that will have time resolution of 1 second, causing 1s delay on every lock contention. That flag tells SQLite to use usleep instead, and it is substantially faster on busy timeout. Here is my SQLite compilation flags: https://github.com/liuliu/dflat/blob/unstable/external/sqlite3.BUILD#L14 https://github.com/liuliu/dflat/blob/unstable/external/sqlit... Here is where the flag used: https://github.com/sqlite/sqlite/blob/d46beb06aab941bf165a9d216d08207ab6c1440d/src/os_unix.c#L6653 https://github.com/sqlite/sqlite/blob/d46beb06aab941bf165a9d...

by lrossi 6y ago

bob1029 6y ago

LastInsertRowId is not slow, but if you are inserting on the same connection from multiple threads, you will require a mutex or you will be getting other threads' row ids. Transactional scopes meaning scenarios like debiting one account and crediting another. This is something you can also manage with locking with application-level primitives.

Aeolun 6y ago
So the mutex in sqlite (for multiple connections) is worse than the one you implement in your own application? I’d assume the DB would be most efficient at handling it’s own. At least to the extend that it wouldn’t garner a 100x speedup to do it in app.
- bob1029 6y ago
  Yes it is substantially worse to use multiple connections vs a single connection. This is fairly easy to test in a few lines of code. We need to remember that opening a connection to SQLite is like opening a file on disk. Creating/destroying file handles requires far more resources and ceremony than taking out a mutex on a file that is never closed.
  - liuliu 6y ago
    That doesn't sound right. SQLite's lock for writes is not the best, but it is still pthread mutex under the hood. Are you sure your compilation options for SQLite is right? One common pitfall is compiling without `-DHAVE_USLEEP`. In absence of that flag, SQLite will use sleep in case of conflict, and that will have time resolution of 1 second, causing 1s delay on every lock contention. That flag tells SQLite to use usleep instead, and it is substantially faster on busy timeout. Here is my SQLite compilation flags: https://github.com/liuliu/dflat/blob/unstable/external/sqlite3.BUILD#L14 https://github.com/liuliu/dflat/blob/unstable/external/sqlit... Here is where the flag used: https://github.com/sqlite/sqlite/blob/d46beb06aab941bf165a9d216d08207ab6c1440d/src/os_unix.c#L6653 https://github.com/sqlite/sqlite/blob/d46beb06aab941bf165a9d...