Hi, all

I have some problems with transactions and locks...
I have 5 questions about it...
 
1. NOTICE:  (transaction aborted): queries ignored until END
   *ABORT STATE*
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   I would like to know what's mean the above message.
    - Seems that transactions aborts at smallest syntax error ?
    - Seems that every work done until this point is lost. 
    - ?Am I right?
    - If yes, ?what can I do to go on?
      Seems that I can't do nothing but COMMIT or ROLLBACK.
    - Seems that COMMIT has the same effect of ROLLBACK,
      because all changes are lost in anyway.
    - If that's true  ?why is it neccessary to do COMMIT or ROLLBACK?
    - and ?what about locks?
      ?are all locks released before COMMIT/ROLLBACK?
 
2. LOCKED FOR EVER AND EVER...
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^
   If user2 try to SELECT a table locked by user1, user2 falls in a trap
   he can't do nothing to free himself from this trap,
   and he must wait that user1 ends his work.

   - ?Is there a way to avoid this trap? 
   I think that's useless to lock tables even for readonly operations.
   - user2 shouldn't be able to UPDATE tables but he should be able 
     to SELECT tables.
   - ...or at least user2 should have the possibility to choice if 
     he wants wait for ever that a table become available or retry latter
     to see if table was unlocked.
     A message like:
          "TABLE <tablename> IS LOCKED BY USER <username> PLEASE, TRY LATTER"
     would be preferable.
   - If this interests to someone;
             the Oracle'sLOCK TABLE <tablename> IN EXCLUSIVE MODE
     allows read access to locked tables.
    
3. DROP TABLE <tablename> or DELETE FROM <tablename> ?
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   Dropping a table inside a transactions and then rolling back the work
   is equivalent to DELETE FROM <tablename>; the table's structure will
   be restored but data will be lost:
 
   postgres=> begin;
   BEGIN
 
   postgres=> select * from cities;
   code|city
   ----+-------------
   SFO |SAN FRANCISCO
   STL |ST. LOUIS
   (2 rows)
 
   postgres=> drop table cities;
   DROP
 
   postgres=> rollback;
   ABORT
 
   postgres=> select * from cities;
   code|city
   ----+----
   (0 rows)
 
4. MIRACLE DROPPED TABLE IS RETURNED.
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   User <manuel> dropps a table inside a transactions while 
   user <jose> is trying to query the same table,
   user <manuel> change his mind and rolls back the work,
   at this point user <jose> see the result of his query, 
   and insert a new row to the table.
   At this point the dropped table returns with all its data.
 
 --user: manuel-------------------------------------------------
 
   postgres=> select current_user;
   getpgusername
   -------------
   manuel
 
   postgres=> begin;
   BEGIN
 
   postgres=> select * from cities;
   code|city
   ----+-------------
   SFO |SAN FRANCISCO
   STL |ST. LOUIS
   (2 rows)
 
   postgres=> lock cities;
   DELETE 0
 
 --user jose--------------------------------------------------
 
   postgres=> select current_user;
   getpgusername
   -------------
   jose
 
   postgres=>  select * from cities;
   --jose was caught in a trap, wait for ever and ever ...
 
 --user manuel again---------------------------------------------
 
   postgres=> drop table cities;
   DROP
 
   postgres=> rollback;
   ABORT
 
 --user jose again---------------------------------------------
 -- (finally jose is "free" and have his query result):
 
   code|city
   ----+-------------
   SFO |SAN FRANCISCO
   STL |ST. LOUIS
   (2 rows)
 
 -- and now, jose decide to append a new row to the table...
   postgres=> insert into cities values ('SJC','SAN JOSE');
   INSERT 460390 1
 
 --and user manuel query table... ------------------------------------------
 -- et voila'... the table and all its data are returned...
   postgres=>  select * from cities;
   code|city
   ----+-------------
   SFO |SAN FRANCISCO
   STL |ST. LOUIS
   SJC |SAN JOSE
   (3 rows)
 
5. LOCK AT ROW LEVEL
   ^^^^^^^^^^^^^^^^^
   Massimo Dal Zotto have done a very useful work with locks at row level
   (refer to .../contrib/userlock) and it should be interesting to implement
   these functions as SQL statements.
   
   --to lock a row(s)...
   SELECT user_write_lock_oid(OID), oid, *
      FROM cities
      WHERE city LIKE 'SAN%';
   
   user_write_lock_oid|   oid|code|city
   -------------------+------+----+-------------
                     1|460388|SFO |SAN FRANCISCO
                     1|460390|SJC |SAN JOSE

   --if result of "user_write_lock_oid" is 1, then the row(s) are available
   --and you can update it...
     
   --to unlock the row(s)...
   SELECT user_write_unlock_oid(OID)
      FROM cities
      WHERE oid = 460388 OR oid = 460390;
 
   - If this interests to someone, Oracle uses a similar way to locking rows,
     take a look...

   SELECT ROWID, *
      FROM cities
      WHERE city LIKE 'SAN%';
      FOR UPDATE OF city;
   
    ROWID	CODE	CITY
   __________________________________
   460388	SFO 	SAN FRANCISCO
   460390	SJC 	SAN JOSE

   --if row(s) is/are available then you can update it/them...

   UPDATE cities
      SET city = INITCAP(city)
      WHERE rowid = 460388 OR rowid = 460390;

   --to unlock the row(s)...
   COMMIT

   Oracle uses ROWIDs to lock rows, we also have OIDs...
   ?How much difficult is it to implement ?

                                                            Ciao, Jose'


Mon, 20 Apr 1998 10:08:45 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
errors on transactions and locks ?