Re: [sqlite] Three questions

[Nico Williams]

On Wed, May 11, 2011 at 12:18 PM, Martin Engelschalk
 wrote:
> This question does not arise with SQLite, because parallel transaction
> are not supported, as Igor and Pavel pointed out.
>
> However, consider this: If you have a unique constraint on a table like
> in your example, when should the database enforce it?

SQLite enforces UNIQUE constraints (and indices) immediately.  Foreign
key reference enforcement can be delayed to the end of the
transaction, but UNIQUE constraints, sadly, cannot be.

> To use your example and add a second colum
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1, 'foo') // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1, 'bar') // lets say, this 
> also works like you expected.

With SQLite3 B will have to wait for A to finish or rollback its transaction.

> 00:05 Transaction B: COMMIT // shall this work? If yes, the Record ('1', 
> 'bar') is now committed. However, Transaction A was first!
> 00:06 Transaction A: COMMIT // This cannot work. What error message would you 
> expect?
>
> Now, consider large transactions with many Operations.
> Therefore, the second insert fails on every database system i ever 
> encountered.

B will have constructed the key before it knew that A had created a
row with that same key.  This necessarily means that B's insert must
fail (unless B used INSERT OR REPLACE, say).  And this is necessarily
true for any RDBMS that gives you unique constraints and/or indices.
What can vary is when the constraint is checked: at insert or commit
time (see above).

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Dagdamor]

Martin,
Thank you for great explanation. I think I understand it better now. :)

Regards,
Serge


Martin Engelschalk  писал(а) в своём письме Wed, 11 
May 2011 23:18:13 +0600:

> Hello,
>
> This question does not arise with SQLite, because parallel transaction
> are not supported, as Igor and Pavel pointed out.
>
> However, consider this: If you have a unique constraint on a table like
> in your example, when should the database enforce it?
>
> To use your example and add a second colum
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1, 'foo') // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1, 'bar') // lets say, this 
> also works like you expected.
> 00:05 Transaction B: COMMIT // shall this work? If yes, the Record ('1', 
> 'bar') is now committed. However, Transaction A was first!
> 00:06 Transaction A: COMMIT // This cannot work. What error message would you 
> expect?
>
> Now, consider large transactions with many Operations.
> Therefore, the second insert fails on every database system i ever 
> encountered.
>
> Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Pavel Ivanov]

> Therefore, the second insert fails on every database system i ever 
> encountered.

Apparently you didn't encounter Oracle. In such situation Oracle
freezes transaction B until transaction A is committed or rollbacked.
After that it knows what to return to transaction B - error or success
correspondingly.


Pavel


On Wed, May 11, 2011 at 1:18 PM, Martin Engelschalk
 wrote:
> Hello,
>
> This question does not arise with SQLite, because parallel transaction
> are not supported, as Igor and Pavel pointed out.
>
> However, consider this: If you have a unique constraint on a table like
> in your example, when should the database enforce it?
>
> To use your example and add a second colum
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1, 'foo') // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1, 'bar') // lets say, this 
> also works like you expected.
> 00:05 Transaction B: COMMIT // shall this work? If yes, the Record ('1', 
> 'bar') is now committed. However, Transaction A was first!
> 00:06 Transaction A: COMMIT // This cannot work. What error message would you 
> expect?
>
> Now, consider large transactions with many Operations.
> Therefore, the second insert fails on every database system i ever 
> encountered.
>
> Martin
>
>
> Am 11.05.2011 17:24, schrieb Dagdamor:
>> and two transactions (from two different connections) are trying to insert a 
>> record at once:
>>
>> 00:01 Transaction A: BEGIN
>> 00:02 Transaction B: BEGIN
>> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
>> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
>> key' error! why???
>> 00:05 Transaction A: ROLLBACK // works okay, table remains empty
>> 00:06 Transaction B: ??? // has nothing to do because was unable to insert a 
>> record into an empty table!
>>
>> To put it simple, transaction A tried to insert a record but soon aborted 
>> itself via ROLLBACK. If I understand transactions principle correctly, a 
>> rolled-back transaction should act like it never happened in the first 
>> place, and other threads should not see its traces. But for some reason 
>> another transaction noticed that and refused to insert values into table. 
>> The question is: is that a correct behavior, and I should keep this in mind, 
>> or SQLite would handle this scenario different way? :/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Martin Engelschalk]

Hello,

This question does not arise with SQLite, because parallel transaction 
are not supported, as Igor and Pavel pointed out.

However, consider this: If you have a unique constraint on a table like 
in your example, when should the database enforce it?

To use your example and add a second colum

00:01 Transaction A: BEGIN
00:02 Transaction B: BEGIN
00:03 Transaction A: INSERT INTO test VALUES (1, 'foo') // works okay
00:04 Transaction B: INSERT INTO test VALUES (1, 'bar') // lets say, this also 
works like you expected.
00:05 Transaction B: COMMIT // shall this work? If yes, the Record ('1', 'bar') 
is now committed. However, Transaction A was first!
00:06 Transaction A: COMMIT // This cannot work. What error message would you 
expect?

Now, consider large transactions with many Operations.
Therefore, the second insert fails on every database system i ever encountered.

Martin


Am 11.05.2011 17:24, schrieb Dagdamor:
> and two transactions (from two different connections) are trying to insert a 
> record at once:
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
> key' error! why???
> 00:05 Transaction A: ROLLBACK // works okay, table remains empty
> 00:06 Transaction B: ??? // has nothing to do because was unable to insert a 
> record into an empty table!
>
> To put it simple, transaction A tried to insert a record but soon aborted 
> itself via ROLLBACK. If I understand transactions principle correctly, a 
> rolled-back transaction should act like it never happened in the first place, 
> and other threads should not see its traces. But for some reason another 
> transaction noticed that and refused to insert values into table. The 
> question is: is that a correct behavior, and I should keep this in mind, or 
> SQLite would handle this scenario different way? :/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Dagdamor]

Simon Slavin  писал(а) в своём письме Wed, 11 May 2011 
22:31:54 +0600:

> That would be purely for a free-format presentation of the entire data in a 
> row.  That's ideal for a utility.  Normally when you write a database 
> application you're specifically looking up some data: the name and phone 
> number of a particular customer, or something.

Not the entire data "for utility"; only needed columns, for specific interface 
a customer deals with.

But what if you need only five columns to display, and your table consists of 
10 columns, does such kind of economy ("SELECT column1,column2,...columnN" 
instead of "SELECT *") make much sense? You don't read the whole table anyway, 
only a small subset of rows, so you won't waste much memory on these "unneeded" 
columns. The speed of the query also won't change if you use "*". In fact, it 
can become even faster because SQL server doesn't have to parse and validate 
the list of columns you've specified. :)

> You're using there ... I'm guessing it's PHP, using an object model.  Which 
> works out the names of the columns for you and makes them available.  What 
> most people get back from "SELECT *" is a list of unlabelled variables.  So 
> they have to remember that item 1 is 'id', item 2 is 'name', item 3 is 
> something they don't actually need right now, item 4 is 'phoneNumber'.  Then 
> you add a new field of 'schoolAddress' and now 'phoneNumber' is actually 
> column 5, not column 4.

You're right, it's PHP, but no, it's not an object model. Just pure PHP (or 
pure PHP + small framework) in most of my apps. Even if you don't use heavy 
instruments like OOP, ORM etc, PHP still allows you to address columns in a 
nice way, i.e. $row["name"]. I'm quite surprized that other programming 
languages still force you to use numeric indices. Indeed "SELECT *" shouldn't 
be used under such circumstances.

> We're not saying there no place for '*', merely that it shouldn't be used 
> unless you actually want '*'.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Jos Groot Lipman]

> We're not saying there no place for '*', merely that it shouldn't be used
unless you actually want '*'.

And suppose there is very_large_blob_field you don't need right now. If you
do a 'select *' it will be read from disk unnecessary even if you are not
going to use it. That is reason enough to not use 'select *' but only name
the fields you intend to use.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Simon Slavin]

On 11 May 2011, at 4:24pm, Dagdamor wrote:

> Earlier in this list, I noticed several replies generally saying "using 
> SELECT * FROM is a no-no, it's evil, you should never using that" etc.
> 
> If you use fetching methods that give you associated (named) data, like 
> mysqli_fetch_assoc() or sqlite_fetch_array(SQLITE_ASSOC), using SELECT * FROM 
> is perfectly fine. It gives you simplified queries and allows you to separate 
> business logic (i.e. code) from presentation layer (i.e. templates), because 
> your logic don't have to worry about what columns are required for templates, 
> and what not.

That would be purely for a free-format presentation of the entire data in a 
row.  That's ideal for a utility.  Normally when you write a database 
application you're specifically looking up some data: the name and phone number 
of a particular customer, or something.

> Even if your DB schema is modified (row order changes, new rows added), your 
> code still works because you use something like $row["id"] or $row->id, not 
> $row[5] (which I reckon somewhat unclear).

You're using there ... I'm guessing it's PHP, using an object model.  Which 
works out the names of the columns for you and makes them available.  What most 
people get back from "SELECT *" is a list of unlabelled variables.  So they 
have to remember that item 1 is 'id', item 2 is 'name', item 3 is something 
they don't actually need right now, item 4 is 'phoneNumber'.  Then you add a 
new field of 'schoolAddress' and now 'phoneNumber' is actually column 5, not 
column 4.

We're not saying there no place for '*', merely that it shouldn't be used 
unless you actually want '*'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Pavel Ivanov]

>> 00:01 Transaction A: BEGIN
>> 00:02 Transaction B: BEGIN
>> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
>> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
>> key' error! why???
>
> I get SQLITE_BUSY "database is locked" at this point, as I would expect.

MySQL may behave differently but that's discussion not for this list.


Pavel


On Wed, May 11, 2011 at 11:41 AM, Igor Tandetnik  wrote:
> On 5/11/2011 11:24 AM, Dagdamor wrote:
>> Hello. First of all, sorry for posting three completely different questions 
>> into the same message ;)
>>
>> Question #1. I was working with transactions in InnoDB (MySQL) and noticed a 
>> weird behavior. Consider we have a table:
>>
>> CREATE TABLE test (id INT NOT NULL PRIMARY KEY);
>>
>> and two transactions (from two different connections) are trying to insert a 
>> record at once:
>>
>> 00:01 Transaction A: BEGIN
>> 00:02 Transaction B: BEGIN
>> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
>> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
>> key' error! why???
>
> I get SQLITE_BUSY "database is locked" at this point, as I would expect.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three questions

[Igor Tandetnik]

On 5/11/2011 11:24 AM, Dagdamor wrote:
> Hello. First of all, sorry for posting three completely different questions 
> into the same message ;)
>
> Question #1. I was working with transactions in InnoDB (MySQL) and noticed a 
> weird behavior. Consider we have a table:
>
> CREATE TABLE test (id INT NOT NULL PRIMARY KEY);
>
> and two transactions (from two different connections) are trying to insert a 
> record at once:
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
> key' error! why???

I get SQLITE_BUSY "database is locked" at this point, as I would expect.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Three questions

[Dagdamor]

Hello. First of all, sorry for posting three completely different questions 
into the same message ;)

Question #1. I was working with transactions in InnoDB (MySQL) and noticed a 
weird behavior. Consider we have a table:

CREATE TABLE test (id INT NOT NULL PRIMARY KEY);

and two transactions (from two different connections) are trying to insert a 
record at once:

00:01 Transaction A: BEGIN
00:02 Transaction B: BEGIN
00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate key' 
error! why???
00:05 Transaction A: ROLLBACK // works okay, table remains empty
00:06 Transaction B: ??? // has nothing to do because was unable to insert a 
record into an empty table!

To put it simple, transaction A tried to insert a record but soon aborted 
itself via ROLLBACK. If I understand transactions principle correctly, a 
rolled-back transaction should act like it never happened in the first place, 
and other threads should not see its traces. But for some reason another 
transaction noticed that and refused to insert values into table. The question 
is: is that a correct behavior, and I should keep this in mind, or SQLite would 
handle this scenario different way? :/

-

Question #2. (well, not really a question :)) Earlier in this list, I noticed 
several replies generally saying "using SELECT * FROM is a no-no, it's evil, 
you should never using that" etc.

If you use fetching methods that give you associated (named) data, like 
mysqli_fetch_assoc() or sqlite_fetch_array(SQLITE_ASSOC), using SELECT * FROM 
is perfectly fine. It gives you simplified queries and allows you to separate 
business logic (i.e. code) from presentation layer (i.e. templates), because 
your logic don't have to worry about what columns are required for templates, 
and what not. Even if your DB schema is modified (row order changes, new rows 
added), your code still works because you use something like $row["id"] or 
$row->id, not $row[5] (which I reckon somewhat unclear).

-

Question #3. Is anyone still interested in SQLite Documentation in Windows HTML 
Help format I kept posting here in the past? I can continue posting links to 
newer .CHM versions here if you like :)

Regards,
Serge


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users