Christian Smith wrote:
"Note that the BEGIN command does not acquire any locks on the database.
After a BEGIN command, a SHARED lock will be acquired when the first
SELECT statement is executed. A RESERVED lock will be acquired when the
first INSERT, UPDATE, or DELETE statement is executed."
Surely, after a BEGIN is executed, the database should be considered a
current snapshot, and no subsequent changes should be visible in this
transaction.
However, if connection A executes a BEGIN, then is preempted by connection
B (perhaps in a different process) which updates the database, by the time
connection A is queried, the database will have changed between the BEGIN
and the first statement.
I'd expect the transaction boundry to begin with the BEGIN statement, not
wait until the first command.
Suppose user A executes the following SQL:
(Slot 1)-->
BEGIN;
(Slot 2)-->
SELECT * FROM t1;
(Slot 3)-->
UPDATE t1 SET ....;
You point out that user B could have changed the database
during Slot 2. This is true. But from the point of view
of user A, because A has no knowledge of the content of the
database until it does its first SELECT, the database looks
the same to A as if B had made his change at Slot 1. And
if A cannot tell the different, it doesn't really matter.
Transactions are still isolated.
The important thing is that B is not able to change the
database during Slot 3.
Another suggestion I might add, how about maintaining a transaction ID of
the last transaction committed? That would allow connections to maintain
their cache between transactions using the following rules:
- Cache is valid if:
- Last transaction ID is unchanged from the data in the cache.
- Last transaction ID is the same as our last write.
- Else, the cache is invalid.
The transaction ID will then be simply incremented for every write
transaction.
Something like this is in the code but it probably won't be
fully activated in the first release.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
