SQLite and sure last_insert_rowid

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite and sure last_insert_rowid

HilaireFernandes
Hi guys,

In a multi-thread context use of Sqlite, I try to find out how one will
fetch securely the automatically last row index of a Primary key.

The problem seems to be that between the insert of rows in a table and
the moment you request the last row id, you may have another insert from
another thread in the same database.

In the code bellow, I put the insert and last_insert_rowid() call in a
transaction. The returned rowid value is the right one as expected, and
now I am wondering if enclosing in a transaction is enought to make the
last_insert_rowid() safe.


Any trips how you handle this situation?

Thanks

Hilaire


The organisation table is defined as:
CREATE TABLE organisation (
        idOrg INTEGER PRIMARY KEY,
        name VARCHAR(30),
        address TEXT,
        zipcode INTEGER,
        city VARCHAR(30),
        phone VARCHAR(15),
        email VARCHAR(20) );'


The code is as follow:


| row res s id |
database execute: 'INSERT INTO organisation (name) values (''Pharo
consortium'')'.
res := database beginTransaction.
self assert: res = 0.
s := database prepare: 'INSERT INTO organisation (name, address,
zipcode, city, phone, email) values (?, ?, ?, ?, ?, ?)'.
s at: 1 putString: 'Dupont & co'.
s at: 2 putString: '12, rue du pont'.
s at: 3 putInteger: 75000.
s at: 4 putString: 'Paris'.
s at: 5 putString: '0105060708'.
s at: 6 putString: '[hidden email]'.
s step.
s finalize.
row := database execute: 'SELECT last_insert_rowid()'.
res := database commitTransaction.
id := row next at: 'last_insert_rowid()'.
row close.
self assert: res = 0.
self assert: id = 2.

--
Dr. Geo
http://drgeo.eu


Reply | Threaded
Open this post in threaded view
|

Re: SQLite and sure last_insert_rowid

Sven Van Caekenberghe-2
That are SQL 101 questions, this is a Pharo list ;-)

You should wrap your SQL statements in a transactions, https://sqlite.org/lang_transaction.html

> On 16 Jul 2016, at 08:19, Hilaire <[hidden email]> wrote:
>
> Hi guys,
>
> In a multi-thread context use of Sqlite, I try to find out how one will
> fetch securely the automatically last row index of a Primary key.
>
> The problem seems to be that between the insert of rows in a table and
> the moment you request the last row id, you may have another insert from
> another thread in the same database.
>
> In the code bellow, I put the insert and last_insert_rowid() call in a
> transaction. The returned rowid value is the right one as expected, and
> now I am wondering if enclosing in a transaction is enought to make the
> last_insert_rowid() safe.
>
>
> Any trips how you handle this situation?
>
> Thanks
>
> Hilaire
>
>
> The organisation table is defined as:
> CREATE TABLE organisation (
> idOrg INTEGER PRIMARY KEY,
> name VARCHAR(30),
> address TEXT,
> zipcode INTEGER,
> city VARCHAR(30),
> phone VARCHAR(15),
> email VARCHAR(20) );'
>
>
> The code is as follow:
>
>
> | row res s id |
> database execute: 'INSERT INTO organisation (name) values (''Pharo
> consortium'')'.
> res := database beginTransaction.
> self assert: res = 0.
> s := database prepare: 'INSERT INTO organisation (name, address,
> zipcode, city, phone, email) values (?, ?, ?, ?, ?, ?)'.
> s at: 1 putString: 'Dupont & co'.
> s at: 2 putString: '12, rue du pont'.
> s at: 3 putInteger: 75000.
> s at: 4 putString: 'Paris'.
> s at: 5 putString: '0105060708'.
> s at: 6 putString: '[hidden email]'.
> s step.
> s finalize.
> row := database execute: 'SELECT last_insert_rowid()'.
> res := database commitTransaction.
> id := row next at: 'last_insert_rowid()'.
> row close.
> self assert: res = 0.
> self assert: id = 2.
>
> --
> Dr. Geo
> http://drgeo.eu
>
>


Reply | Threaded
Open this post in threaded view
|

Re: SQLite and sure last_insert_rowid

HilaireFernandes
Hi Sven,

I know but my questioning is how you will do it properly with Pharo
driver for Sqlite.
In my previous email, I shared how I think doing it but I am not sure it
is the proper way.

For example, NBSqlite3ResultSet (specific to Pharo driver) produces rows
on demand, is such instance valid once you commit the transaction? Or
should you request it before or the transaction end?

Thanks

Hilaire

Le 16/07/2016 16:07, Sven Van Caekenberghe a écrit :
> That are SQL 101 questions, this is a Pharo list ;-)
>
> You should wrap your SQL statements in a transactions, https://sqlite.org/lang_transaction.html

--
Dr. Geo
http://drgeo.eu


Reply | Threaded
Open this post in threaded view
|

Re: SQLite and sure last_insert_rowid

Sven Van Caekenberghe-2

> On 16 Jul 2016, at 11:54, Hilaire <[hidden email]> wrote:
>
> Hi Sven,
>
> I know but my questioning is how you will do it properly with Pharo
> driver for Sqlite.
> In my previous email, I shared how I think doing it but I am not sure it
> is the proper way.

Ah yes, I didn't read all the way through.

> For example, NBSqlite3ResultSet (specific to Pharo driver) produces rows
> on demand, is such instance valid once you commit the transaction? Or
> should you request it before or the transaction end?

I think I would first try to get the result inside the transaction. If the commit fails, it would be invalid of course.

> Thanks
>
> Hilaire
>
> Le 16/07/2016 16:07, Sven Van Caekenberghe a écrit :
>> That are SQL 101 questions, this is a Pharo list ;-)
>>
>> You should wrap your SQL statements in a transactions, https://sqlite.org/lang_transaction.html
>
> --
> Dr. Geo
> http://drgeo.eu
>
>


Reply | Threaded
Open this post in threaded view
|

Re: SQLite and sure last_insert_rowid

Pierce Ng-3
In reply to this post by HilaireFernandes
On Sat, Jul 16, 2016 at 02:19:37PM +0800, Hilaire wrote:
> In a multi-thread context use of Sqlite, I try to find out how one will
> fetch securely the automatically last row index of a Primary key.
>
> The problem seems to be that between the insert of rows in a table and
> the moment you request the last row id, you may have another insert from
> another thread in the same database.

Hi Hilaire,

See http://www.sqlite.org/c3ref/last_insert_rowid.html, especially
last paragraph:

  If a separate thread performs a new INSERT on the same database connection
  while the sqlite3_last_insert_rowid() function is running and thus changes the
  last insert rowid, then the value returned by sqlite3_last_insert_rowid() is
  unpredictable and might not equal either the old or the new last insert rowid.

Pierce

Reply | Threaded
Open this post in threaded view
|

Re: SQLite and sure last_insert_rowid

HilaireFernandes
Hi Pierce,

Yes, I already knew about this problem and documentation. That's why I
do it in a transaction. My question was about be unsure my code example
do it correctly in Pharo.

Thanks

Hilaire

Le 20/07/2016 21:09, Pierce Ng a écrit :

> On Sat, Jul 16, 2016 at 02:19:37PM +0800, Hilaire wrote:
>> > In a multi-thread context use of Sqlite, I try to find out how one will
>> > fetch securely the automatically last row index of a Primary key.
>> >
>> > The problem seems to be that between the insert of rows in a table and
>> > the moment you request the last row id, you may have another insert from
>> > another thread in the same database.
> Hi Hilaire,
>
> See http://www.sqlite.org/c3ref/last_insert_rowid.html, especially
> last paragraph:
>
>   If a separate thread performs a new INSERT on the same database connection
>   while the sqlite3_last_insert_rowid() function is running and thus changes the
>   last insert rowid, then the value returned by sqlite3_last_insert_rowid() is
>   unpredictable and might not equal either the old or the new last insert rowid.
>
> Pierce
>
>

--
Dr. Geo
http://drgeo.eu