Sqldelight: Question about concurrency of writes / transactions

[ursusursus]

Hi, Id like to better know how is concurrency of writes implemented. From my testing it seems that there is some internal synchronization going on, as Thread.sleep(..) within transaction seems to block all other concurrent transactions.

Also, maybe pointers if concurrent writes are good idea, and/or if I should use my own single threaded rxjava scheduler which would do sequential database writes, rather than relying on this internal mechanism?

Thanks

[AlecStrong]

transactions shouldnt block another transaction from starting, but once you perform a write in a transaction, that transaction must complete before any other transaction can write. Thats a limitatioin of SQLite and not SQLDelight

if possible do you have a sample project or test I can use to verify things are working as expected?

[ursusursus]

Im not saying there is anything wrong, just doing my research on concurrent sql, as the last time ive done it I have had simple blocking queues of tasks.

From what you are saying it works like that, but I dont understand why, since it basically seems that Thread.sleep is blocking other unrelated thread

And, given from rx point of view theres same amout of code making it sequential to parallel, if it is even I thing I should attempt to do

[ursusursus]

Single.fromCallable {
    Log.d("Default", "calling 1=${Thread.currentThread().name}")
}.flatMap {
    Single.fromCallable {
        Log.d("Default", "pre tran 1=${Thread.currentThread().name}")
        channelQueries.transaction {
            Thread.sleep(5000)
            Log.d("Default", "pre insert 1=${Thread.currentThread().name}")
            repo.insertChannel(
                Channel.Impl(
                    "abc",
                    "cba",
                    "name",
                    1L,
                    "asd",
                    false,
                    true,
                    "foo",
                    "bar",
                    true,
                    5L,
                    1,
                    4,
                    5,
                    true,
                    false
                )
                , ""
            )
            Log.d("Default", "post insert 1")
        }
        Log.d("Default", "allChannels 1=${repo.channelQueries.foo().executeAsList()}")
    }
        .subscribeOn(Schedulers.io())
}
    .subscribe({ Log.d("Default", "insert 1 complete") }, { LOG.e(it) })

Single.fromCallable {
    Log.d("Default", "calling 2 =${Thread.currentThread().name}")
}.flatMap {
    Single.fromCallable {
        Log.d("Default", "pre tran 2=${Thread.currentThread().name}")
        channelQueries.transaction {
            Log.d("Default", "pre insert 2=${Thread.currentThread().name}")
            repo.insertChannel(
                Channel.Impl(
                    "abc2",
                    "cba",
                    "name",
                    1L,
                    "asd",
                    false,
                    true,
                    "foo",
                    "bar",
                    true,
                    5L,
                    1,
                    4,
                    5,
                    true,
                    false
                )
                , ""
            )
            Log.d("Default", "post insert 2")
        }
        Log.d("Default", "allChannels 2=${repo.channelQueries.foo().executeAsList()}")
    }
        .subscribeOn(Schedulers.io())
}
    .subscribe({ Log.d("Default", "insert 2 complete") }, { LOG.e(it) })

02-23 02:42:42.883 24702-24702/? D/Default: calling 1=main
02-23 02:42:42.885 24702-24702/? D/Default: calling 2 =main
02-23 02:42:42.885 24702-24718/? D/Default: pre tran 1=RxCachedThreadScheduler-2
02-23 02:42:42.892 24702-24719/? D/Default: pre tran 2=RxCachedThreadScheduler-3
02-23 02:42:47.891 24702-24718/foo D/Default: pre insert 1=RxCachedThreadScheduler-2
02-23 02:42:47.913 24702-24718/foo D/Default: post insert 1
02-23 02:42:47.953 24702-24719/foo D/Default: pre insert 2=RxCachedThreadScheduler-3
02-23 02:42:47.959 24702-24719/foo D/Default: post insert 2
02-23 02:42:48.010 24702-24719/foo D/Default: allChannels 2=[...]
02-23 02:42:48.010 24702-24719/foo D/Default: insert 2 complete
02-23 02:42:48.013 24702-24718/foo D/Default: allChannels 1=[...]
02-23 02:42:48.013 24702-24718/foo D/Default: insert 1 complete

[ursusursus]

tldr; pre insert 2... log is delayed by sleep of 5000ms even when on different thread, how?

[AlecStrong]

interesting, that does seem wrong i think. Will investigate

[ursusursus]

-- do you guys use single thread for writes anyways?

[AlecStrong]

not purposely but its rare that concurrent writes are happening. Concurrent reads we do all the time

[ursusursus]

concurrent reads sure, but im wondering if its worth it flatmapping onto singlethreaded scheduler for writes or keep it on io scheduler / thread used by upstream api request single/observable

[AlecStrong]

we use the IO scheduler

[kpgalligan]

If I'm missing some context, apologies, but here's what I think you're trying to figure out.

One thread starts a transaction, then does some non-db related things (sleep, for example)

Thread 2 tries to start a transaction and do things, but it doesn't run till thread 1's transaction completes.

Yes?

Assuming that's the issue, it's the Android sqlite driver. Internally it maintains a "pool" of connections, but it's not like what you'd be used to from using a server type of database. While you can have many reads concurrently, you can have only one write happening. The "pool" has a single connection that will be returned if it's asked for a writable connection. Starting a transaction causes the session that grabbed the connection to hold onto it until it's released. Other threads asking for a write connection, for example, looking to start a transaction, are blocked by the pool until the initial connection is returned.

beginTransaction calls this, with readonly = false

https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/database/sqlite/SQLiteDatabase.java#429

Which adds 'SQLiteConnectionPool.CONNECTION_FLAG_PRIMARY_CONNECTION_AFFINITY' to the flags.

That ultimately lands here...

https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/database/sqlite/SQLiteConnectionPool.java#675

It's hard to follow, but the summary is starting a transaction locks the primary (writing) connection to the caller session, and other threads trying to start a connection are held up.

How this might work in a coroutines world where threads don't necessarily process the way you expect is an interesting thought experiment I'm going to avoid for now, but that's what's happening in this case.

[kpgalligan]

TL;DR trying to speed up writes with multiple threads won't help.

[ursusursus]

Thanks for deep info. I just was surprised that it blocks, since I do remember faintly from back in the day, that it used to throw exception on this (something about database locked), and people end up using blocking queue to serialize writes.

Btw, if its the pool, would WAL allow concurrent writes?

[AlecStrong]

one thing to point out is that concurrent writes and concurrent transactions are two different things with different behavior. Enabling WAL does enable concurrent transactions I believe but as soon as a transaction performs a write it has the write lock and the transaction must finish before anyone else can acquire the write lock.

SQLite has no support for concurrent writes.

[kpgalligan]

I have been looking at that same code for some years. You would previously only get "locked" errors if you opened multiple connections. SQLiteOpenHelper until 2.3 (ish) maintained a single connection and serialized it for you. "locked" errors were race conditions, and only happened once in a while. Usually not when you were debugging. I am 100% sure I could avoid using a blocking queue back then by using a single SQLiteOpenHelper, because it would force you to one connection for everything, including reads.

But I digress. WAL.

Both WAL and the default (Delete) transaction mode only allow a single write concurrently. The difference between WAL and Delete is with reads. WAL lets other threads read the pre-transaction data while the write operation is happening. Delete forces reads to wait till the write is done. Essentially a write blocks everything.

https://www.sqlite.org/lockingv3.html

[ursusursus]

@kpgalligan Sorry to summon you like this, but Im rethinking if sqlite is a good fit for my app. I am working on instant messenger, where you send messages by the usual technique = insert message into local db with flag sending, make the api request, update the message with flag sent. This is obviously for UX reasons, so user does not wait for the request to succeed in order to have the ui refreshed.

However, since you said only single transaction is possible, that means if there is some big synchronization in the app going on and the transaction takes lets say 50ms, and during this time user sends a message, that means that "insert message into local db with flag sending" wont be inserted until that sync completes?

And there is 0 ways around this on Android? Am I Right?

[kpgalligan]

You cannot "get around" single threaded writes with sqlite. That's sqlite, not Android specifically. I'm not working on your project, so I can't really comment, but I'd be amazed to see sqlite be the bottleneck in that scenario.

[ursusursus]

Well technically it is, since I need to do diffing of database sometimes against server data, i.e. figure out to insert new data, update current, and delete extras. Very common to be 50+ms.

Anyways, I see your point, thanks, guess Ill have to fake it with appending to the list I already have, to get instant UI update, and then when the blocked insert finished, the query observable emit will be
basically a no-op

[kpgalligan]

Not that i want to get into this debate, but how many rows are you talking
about, and assuming you're doing a crazy amount of logical merging, do you
need to hold the transaction that whole time?

On Thu, Feb 28, 2019 at 10:45 PM ursusursus [e-mail]
wrote:

Well technically it is, since I need to do diffing of database sometimes
against server data, i.e. figure out to insert new data, update current,
and delete extras.

Anyways, I see your point, thanks, guess Ill have to fake it with
appending to the list I already have, to get instance UI update, and then
when the block insert finished, its basically a no-op

—
You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub
https://github.com/square/sqldelight/issues/1227#issuecomment-468531836,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAELIOkem0ohnmWYRqILR-sbJkbllTRrks5vSKI_gaJpZM4bNf7r
.

>

Kevin Galligan
https://twitter.com/kpgalligan

[ursusursus]

Well not huge amounts of data at all, basically most of the time its maybe 50 rows that gets updated, benchmarking it naively now, querying is 3ms, actual kotlin diffing logic is 0ms and writes (updates) around 50-60ms on Nexus 5, does it seem atypical?

And since its a for loop of updates, it makes sense to hold a transaction right? without it it would be way longer + my local message insert thingy would only be faster on the off chance it will win a race for the database lock, right?

Regarding to the UI thing I mentioned, I know 50ms is not that huge, but youll definetely notice it when your ui action is delayed by 4frames + it will grow as database grows

[kpgalligan]

Well, just opinions here. It sounds like the updates are slow. Even if
they're not, assuming you're not doing this on main thread, then you're not
dropping frames. 1/20th of a second between clicking "send" and seeing your
list update, in an edge case, is not a big problem. However, i'm not
selling sqlite. You should do what you need to do

On Thu, Feb 28, 2019 at 11:07 PM ursusursus [e-mail]
wrote:

Well not huge amounts of data at all, basically most of the time its maybe
50 rows that gets updated, benchmarking it to naively, querying is 3ms,
actual kotlin diffing logic is 0ms and writes (updates) around 50-60ms on
Nexus 5, does it seem atypical?

Regarding to the UI thing I mentioned, I know 50ms is not that huge, but
youll definetely notice it when your ui action is delayed by 4frames

—
You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub
https://github.com/square/sqldelight/issues/1227#issuecomment-468535188,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAELIHhPwZRezJ_CoUZCf9iPT-JCCVPEks5vSKdkgaJpZM4bNf7r
.

>

Kevin Galligan
https://twitter.com/kpgalligan

[ursusursus]

Still it depends on what else is queued up etc.

No worries, im thankful for your time, thanks!

[ursusursus]

I forgot to ask, do you by chance know if this is a thing that the new no-sql databases like realm / objectbox are trying to solve by MVCC?

[kpgalligan]

You'd need to research Realm in more detail, but https://stackoverflow.com/a/47337915/227313 (and other not-super-official sources) suggest single concurrent write, which is effectively the same as WAL in sqlite. I haven't used objectbox.