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/ p...@phd.pp.ru
Programmers don't die, they just GOSUB without RETURN.
------------------------------------------------------------------------------
Are you an open source citizen? Join us for the Open Source Bridge conference!
Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250.
Need another reason to go? 24-hour hacker lounge. Register today!
http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
