SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

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

SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

Prajeesh Prakash
Hi Members,

Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and SQLITE_OPEN_NOMUTEX because i am totally confused with the concept. If we enable FULLMUTEX what will happen if two thread trying to update the same table (Both thread are from same DB connection) in case of NOMUTEX what is the flow


Thank you
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

Keith Medcalf

The difference is that if both threads call the library on the same connection at the same time (that is, two calls are into the library are active at the same time) then all hell will break loose.  You application will fail.  Memory will be corrupted.  You database will be corrupted.  Hell may freeze over.  Under no circumstances whatsoever must you *ever* allow this to happen.

SQLITE_OPEN_FULLMUTEX (the default) means that Sqlite3 will, each time you make a call into the library, spend a few CPU cycles to ENSURE that you do not break the rules and that the consequences described above will not happen.

SQLITE_OPEN_NOMUTEX (not the default) means that Sqlite3 WILL NOT, each time you make a call into the library, spend those few CPU cycles ensuring that you have not broken the rules because YOU have explicitly taken responsibility upon yourself to ensure that you do no break the rules.  However, if you do break the rules, then you have obviously intended that the described consequences should come to pass and when they do, they should have been expected.

You can also compile the code so the default is SQLITE_OPEN_NOMUTEX and turn on a special idiot mode that will simply crash (fail an assert) if you break the rules, rather than letting the aforesaid consequences happen.  This takes more CPU cycles that having "pure" SQLITE_OPEN_NOMUTEX and less that SQLITE_OPEN_FULLMUTEX.

So you get to choose the level of consequence and risk that you are willing to tolerate.

So, if you have no mutex and two threads use the same connection to update the same table, then either both will work or you will suffer the consequences described above.  If you use the default serialized (full mutex) then both will work and there will be no consequence as described above.  In both cases neither operation will be isolated in any way from the other (that requires using separate connections).

---
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-
>[hidden email]] On Behalf Of Prajeesh Prakash
>Sent: Wednesday, 28 November, 2018 07:16
>To: SQLite mailing list
>Subject: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX
>
>Hi Members,
>
>Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and
>SQLITE_OPEN_NOMUTEX because i am totally confused with the concept.
>If we enable FULLMUTEX what will happen if two thread trying to
>update the same table (Both thread are from same DB connection) in
>case of NOMUTEX what is the flow
>
>
>Thank you
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

Prajeesh Prakash
Thank you very much for the detailed explanation. Now i understood the concept.

> On November 28, 2018 at 8:16 PM Keith Medcalf <[hidden email]> wrote:
>
>
>
> The difference is that if both threads call the library on the same connection at the same time (that is, two calls are into the library are active at the same time) then all hell will break loose. You application will fail. Memory will be corrupted. You database will be corrupted. Hell may freeze over. Under no circumstances whatsoever must you *ever* allow this to happen.
>
> SQLITE_OPEN_FULLMUTEX (the default) means that Sqlite3 will, each time you make a call into the library, spend a few CPU cycles to ENSURE that you do not break the rules and that the consequences described above will not happen.
>
> SQLITE_OPEN_NOMUTEX (not the default) means that Sqlite3 WILL NOT, each time you make a call into the library, spend those few CPU cycles ensuring that you have not broken the rules because YOU have explicitly taken responsibility upon yourself to ensure that you do no break the rules. However, if you do break the rules, then you have obviously intended that the described consequences should come to pass and when they do, they should have been expected.
>
> You can also compile the code so the default is SQLITE_OPEN_NOMUTEX and turn on a special idiot mode that will simply crash (fail an assert) if you break the rules, rather than letting the aforesaid consequences happen. This takes more CPU cycles that having "pure" SQLITE_OPEN_NOMUTEX and less that SQLITE_OPEN_FULLMUTEX.
>
> So you get to choose the level of consequence and risk that you are willing to tolerate.
>
> So, if you have no mutex and two threads use the same connection to update the same table, then either both will work or you will suffer the consequences described above. If you use the default serialized (full mutex) then both will work and there will be no consequence as described above. In both cases neither operation will be isolated in any way from the other (that requires using separate connections).
>
> ---
> 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-
> >[hidden email]] On Behalf Of Prajeesh Prakash
> >Sent: Wednesday, 28 November, 2018 07:16
> >To: SQLite mailing list
> >Subject: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX
> >
> >Hi Members,
> >
> >Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and
> >SQLITE_OPEN_NOMUTEX because i am totally confused with the concept.
> >If we enable FULLMUTEX what will happen if two thread trying to
> >update the same table (Both thread are from same DB connection) in
> >case of NOMUTEX what is the flow
> >
> >
> >Thank you
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

Prajeesh Prakash
In reply to this post by Keith Medcalf
Then is that SQLITE_LOCKED error will happen because of a conflict within the same database connection. Or in case of two connection two separate thread trying to do operation?

>
>     On November 28, 2018 at 8:16 PM Keith Medcalf <[hidden email]> wrote:
>
>     The difference is that if both threads call the library on the same connection at the same time (that is, two calls are into the library are active at the same time) then all hell will break loose. You application will fail. Memory will be corrupted. You database will be corrupted. Hell may freeze over. Under no circumstances whatsoever must you *ever* allow this to happen.
>
>     SQLITE_OPEN_FULLMUTEX (the default) means that Sqlite3 will, each time you make a call into the library, spend a few CPU cycles to ENSURE that you do not break the rules and that the consequences described above will not happen.
>
>     SQLITE_OPEN_NOMUTEX (not the default) means that Sqlite3 WILL NOT, each time you make a call into the library, spend those few CPU cycles ensuring that you have not broken the rules because YOU have explicitly taken responsibility upon yourself to ensure that you do no break the rules. However, if you do break the rules, then you have obviously intended that the described consequences should come to pass and when they do, they should have been expected.
>
>     You can also compile the code so the default is SQLITE_OPEN_NOMUTEX and turn on a special idiot mode that will simply crash (fail an assert) if you break the rules, rather than letting the aforesaid consequences happen. This takes more CPU cycles that having "pure" SQLITE_OPEN_NOMUTEX and less that SQLITE_OPEN_FULLMUTEX.
>
>     So you get to choose the level of consequence and risk that you are willing to tolerate.
>
>     So, if you have no mutex and two threads use the same connection to update the same table, then either both will work or you will suffer the consequences described above. If you use the default serialized (full mutex) then both will work and there will be no consequence as described above. In both cases neither operation will be isolated in any way from the other (that requires using separate connections).
>
>     ---
>     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-
> >         [hidden email]] On Behalf Of Prajeesh Prakash
> >         Sent: Wednesday, 28 November, 2018 07:16
> >         To: SQLite mailing list
> >         Subject: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX
> >
> >         Hi Members,
> >
> >         Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and
> >         SQLITE_OPEN_NOMUTEX because i am totally confused with the concept.
> >         If we enable FULLMUTEX what will happen if two thread trying to
> >         update the same table (Both thread are from same DB connection) in
> >         case of NOMUTEX what is the flow
> >
> >         Thank you
> >
> >         _______________________________________________
> >         sqlite-users mailing list
> >         [hidden email]
> >         http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >     >
>     _______________________________________________
>     sqlite-users mailing list
>     [hidden email]
>     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

Simon Slavin-3
On 28 Nov 2018, at 3:26pm, Prajeesh Prakash <[hidden email]> wrote:

> Then is that SQLITE_LOCKED error will happen because of a conflict within the same database connection. Or in case of two connection two separate thread trying to do operation?

Your software should never make two simultaneous API calls with the same connection.  It is the programmer's responsibility to make sure this never happens.  One of the ways you can avoid it happening is to use SQLITE_OPEN_FULLMUTEX , which is the default setting.

SQLITE_LOCKED happens when two different connections conflict.  But it is normal to set a timeout of a minute or two to allow each connection to back off and retry a few times before it gives up and reports SQLITE_LOCKED.  See

<https://sqlite.org/c3ref/busy_timeout.html>

The timeout must be set separately for each connection.  You can also set timeout using a PRAGMA.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users