Re: commit

From: "Jay Savage" <daggerquill@xxxxxxxxx>
On 11/20/06, Octavian Rasnita <orasnita@xxxxxxxxx> wrote:

What is the point of committing if you've only queried the
database?
Hi,

I am using SQLite, and I want to select the max(time) from a certain
table, then to select more rows from that table which are newer than
a certain time, and I don't want new rows to be inserted in the
table after selecting the max(time) and before selecting the values
from the rows. The update of the table is made by another program. I
have seen that sometimes when I use "select" from a table, SQLite
gives an error telling that the database is locked if in that moment
it is updated. So I thought that if I will put these 2 "select"
queries in a kind of transaction, they will be executed without
allowing an update until the "transaction" finishes.

Perhaps I am wrong, but is there a way for doing what I need?

Thank you.

Teddy


Teddy,

That's not quite how it works. Transactions and table locking are two
different things. Using transactions won't prevent someone else from
updating the database or getting a read or write lock. It is still
possible for the second query to fail. What transactions do, in part,
is ensure that if the second query fails, the first does, too. And
that's assuming that SELECT doesn't ignore BEGIN TRANSACTION, which
it probably does. Ask the SQLite folks, though, to be sure.

This is not exactly correct. At least not with the higher end
databases. You can add locking hints to the select to ask the server
to lock either the selected rows, the pages they are in or even the
whole table(s) until the end of the transaction. That way noone is
able to modify or delete the selected rows or even other rows in the
table(s) until you commit your transaction. Don't know if SQLite
supports this though.

If what you want is a read or write lock, check the SQLite man pages
for obtaining locks. I think it uses fnctl, but I'm not sure.

It probably uses fnctl inside the code to make sure only one process
modifies the file containing the whole SQLite database at a given
moment or something, but that should not be something you need to
worry about.

In this case it's better to remember the timestamp of the last
processed row instead of trying to prevent other processes/threads
from adding rows. Actually while this will make sure you do not
process any row twice it may, if you are using transactions and are
exceptionaly unlucky, skip some rows.

Imagine that process A starts a transaction and inserts a new row
into the table. The row (rowA) gets assigned a timestamp, but is not
visible to other processes yet. Then process B inserts another row
and commits. This row (rowB) gets a later timestamp! Then, but still
before process A commits the transaction, the Octavian's process
fetches the new rows from the database, including rowB, but excluding
rowA, processes them in some way and remembers the timestamp of rowB.
Only then does process A commit its transaction and the rowA becomes
visible. It's not ever processed by Octavian's process though since
it will only look for rows with timestamps later than rowB's.

Of course unless some transactions inserting those rows take very
very long this is very unlikely to ever happen. If you want to
prevent this you'd have to add a "processed" flag to the rows and
mark the rows you've processed and then each time select all
notprocessed rows whose
timestamp > last_processed_timestamp -
time_longer_than_any_transaction


Jenda
===== Jenda@xxxxxxxxxxx === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

.

Relevant Pages

  • Re: Driver AutoCommit issue
    ... I am using the container managed transaction and I expect the container to handle that for me. ... Is there different driver class that i have to use in the connection pool configuration. ... Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Managing transations involving thousands of records
    ... Where are they before they are in the database? ... This statement indicates that the logical transaction is the individual ... > C. Commit in blocks ... Often that intensity is worth it. ...
    (comp.databases.oracle.server)
  • Re: commit
    ... I am using SQLite, and I want to select the maxfrom a certain table, ... error telling that the database is locked if in that moment it is updated. ... So I thought that if I will put these 2 "select" queries in a kind of ... transaction, they will be executed without allowing an update until the ...
    (perl.beginners)
  • Managing transations involving thousands of records
    ... more than one insert operation into the database. ... four different ways of implementing transaction ... Commit in blocks ... Using savepoint ...
    (comp.databases.oracle.server)
  • Re: Behavior of Connection.commit()
    ... we would not throw an exception: ... transaction under the covers. ... //COMMIT will no longer throw an exception, ... >>> database. ...
    (microsoft.public.sqlserver.jdbcdriver)