Smalltalk › Pharo › Pharo Smalltalk Users

SQLite and sure last_insert_rowid

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

6 messages Options

HilaireFernandes

SQLite and sure last_insert_rowid

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

http://drgeo.eu

Sven Van Caekenberghe-2

Re: SQLite and sure last_insert_rowid

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
>
>

HilaireFernandes

Re: SQLite and sure last_insert_rowid

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

http://drgeo.eu

Sven Van Caekenberghe-2

Re: SQLite and sure last_insert_rowid

> 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
>
>

Pierce Ng-3

Re: SQLite and sure last_insert_rowid

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

HilaireFernandes

Re: SQLite and sure last_insert_rowid

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

http://drgeo.eu