Re: [sqlite] Three questions
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
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
> 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
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
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
> 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
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
>> 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
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
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