Is this behavior expected?

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

Is this behavior expected?

Baruch Burstein-2
SQLite version 3.20.1 2017-08-24 16:21:36
sqlite> create table T(C);
sqlite> insert into T values("test 1");
sqlite> select last_insert_rowid();
1
sqlite> begin;
sqlite> insert into T values("test 2");
sqlite> select last_insert_rowid();
2
sqlite> rollback;
sqlite> select last_insert_rowid();
2

In other words, the rollback doesn't roll back the rowid.

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
_______________________________________________
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: Is this behavior expected?

Igor Korot
Hi,

On Sun, Sep 24, 2017 at 7:46 AM, Baruch Burstein <[hidden email]> wrote:

> SQLite version 3.20.1 2017-08-24 16:21:36
> sqlite> create table T(C);
> sqlite> insert into T values("test 1");
> sqlite> select last_insert_rowid();
> 1
> sqlite> begin;
> sqlite> insert into T values("test 2");
> sqlite> select last_insert_rowid();
> 2
> sqlite> rollback;
> sqlite> select last_insert_rowid();
> 2
>
> In other words, the rollback doesn't roll back the rowid.

I think it is as rowid is auto-increment.
There was a recent thread about that. Check the archive for details.

Thank you.

>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> _______________________________________________
> 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: Is this behavior expected?

Clemens Ladisch
In reply to this post by Baruch Burstein-2
Baruch Burstein wrote:
> sqlite> select last_insert_rowid();
> 2
> sqlite> rollback;
> sqlite> select last_insert_rowid();
> 2
>
> In other words, the rollback doesn't roll back the rowid.

<http://www.sqlite.org/c3ref/last_insert_rowid.html> says:
| For the purposes of this routine, an INSERT is considered
| to be successful even if it is subsequently rolled back.


Regards,
Clemens
_______________________________________________
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: Is this behavior expected?

Keith Medcalf
In reply to this post by Baruch Burstein-2

Yes.  last_insert_rowid() returns data stored in the connection.  It is "loaded" with a value when an insert occurs on the connection.  Even if you rollback the transaction, the value of the data item stored in the connection is not changed -- the last inserted rowid on the connection was indeed rowid 2.  This does not mean that the next last_insert_rowid will return "3" unless you happen to insert rowid 3 into the table.

In other words, if you repeat the same sequence (inside and after the transaction) you will still get 2 as the last inserted rowid since, in fact, this was the last rowid that was inserted.  last_insert_rowid only knows about "insert" operations.  It does not know or care about "delete" or "update" operations (or transactions).  It merely returns exactly what it says it does -- the last_insert_rowid -- and nothing more nor less.

If you repeat the entire sequence (without the create table) you will get 2 for the first insert and 3 for the second insert.  Lather rinse repeat 3 and 4, 4 and 5, 5 and 6, 6 and 7 and so on and so forth.


---
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 Baruch Burstein
>Sent: Sunday, 24 September, 2017 05:46
>To: General Discussion of SQLite Database
>Subject: [sqlite] Is this behavior expected?
>
>SQLite version 3.20.1 2017-08-24 16:21:36
>sqlite> create table T(C);
>sqlite> insert into T values("test 1");
>sqlite> select last_insert_rowid();
>1
>sqlite> begin;
>sqlite> insert into T values("test 2");
>sqlite> select last_insert_rowid();
>2
>sqlite> rollback;
>sqlite> select last_insert_rowid();
>2
>
>In other words, the rollback doesn't roll back the rowid.
>
>--
>˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>_______________________________________________
>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