Re: [SQLObject] transactions; cache sync behavior; sqlite3 relatet?

[Oleg B. <ph...@ph...>]

On Sat, Jun 06, 2009 at 12:20:50PM +0200, Herwig Hochleitner wrote:
> I intended to illustrate following behavior:
>
> Two instances of the same class with same id; one pulled via a  
> connection (with cache=False); one pulled via a transaction createt from  
> that same connection;
> --> Commits to the transaction _do_ invalidate the instance pulled via  
> connection (thanks to cache=False);
> --> Changes to the connection instance _don't_ invalidate the  
> transaction instance;
>
> This asymmetric behavior seems strange to me

   AFAIU it is meaningless from the transaction point of view. Changes
outside a transaction (regardless of the backend and SQLObject) must not
creep inside the transaction, right?
   Actually, in SQLite one cannot even change the DB outside a transaction
if there is an open transaction. Let's see. Start sqlite3 test.db and
initialize a table:

sqlite> CREATE TABLE test (id integer PRIMARY KEY, test VARCHAR (255));
sqlite> INSERT INTO test (test) VALUES ('test1');
sqlite> INSERT INTO test (test) VALUES ('test2');
sqlite> SELECT * FROM test;
1|test1
2|test2

   Now start another sqlite3, open the same DB test.db and start a
transaction:

sqlite> BEGIN;
sqlite> SELECT * FROM test;
1|test1
2|test2

   Back to the first connection:

sqlite> INSERT INTO test (test) VALUES ('test3');
SQL error: database is locked
sqlite> UPDATE test SET test='test3';
SQL error: database is locked

   No way, see? Now the second transactioned connection:

sqlite> COMMIT;

   Back to the first connection:

sqlite> INSERT INTO test (test) VALUES ('test3');

   And again the second transactioned connection:

sqlite> SELECT * FROM test;
1|test1
2|test2
3|test3

   Let us see how Postgres handles this. First createdb test and start two
psql test.

test=# CREATE TABLE test (id serial, test VARCHAR(255));
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
CREATE TABLE
test=# INSERT INTO test (test) VALUES ('test1');
INSERT 0 1
test=# INSERT INTO test (test) VALUES ('test2');
INSERT 0 1
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test1
  2 | test2
(2 rows)

   Second connection:

test=# BEGIN;
BEGIN
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test1
  2 | test2
(2 rows)

   First connection:

test=# INSERT INTO test (test) VALUES ('test3');
INSERT 0 1

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test1
  2 | test2
  3 | test3
(3 rows)

   The new row is visible. Back to the first connection:

test=# UPDATE test SET test='test4';
UPDATE 3

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   The change is visible inside the transaction in Read Committed Isolation
Level. Let's try Serializable Isolation Level. Second transactioned connection:

test=# COMMIT;
ROLLBACK
test=# BEGIN;
BEGIN
test=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   First connection:

test=# INSERT INTO test (test) VALUES ('test5');
INSERT 0 1

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   No changes, see? First connection:

test=# UPDATE test SET test='test6';
UPDATE 4
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test6
  2 | test6
  3 | test6
  4 | test6
(4 rows)

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   Still no changes. Data added or changed outside a transaction is not
visible in the transaction in Serializable Isolation Level. So SQLObject
has to be very clever to manipulate caches inside and outside transactions.

> Can I evade manually syncing all the transactions.

   Well, the simplest (not the best) way would be, probably, to turn off
caching completely:

class MyTable(SQLObject):
   class sqlmeta:
      cacheValues = False

> what's the  
> best way to invalidate a transaction's cache for a given instance/all  
> instances of given class?

   MyTable.sqlmeta.expireAll() or MyTable.sqlmeta.expireAll(transaction)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.