Skip site navigation (1) Skip section navigation (2)

locking problem

From: "cheater cheater" <cheetor(at)rediffmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: locking problem
Date: 2004-01-30 07:41:42
Message-ID: 20040130074142.26889.qmail@webmail36.rediffmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
hi,

can anyone help me out on the following scenario:
why this is happening, if i'm doing any thing wrong or its the feature of postgres...

regards
cheetor
========================================================================
 PostgreSQL
 Steps:

 1. Create a table
 create table mytab (name varchar(100), marks NUMERIC(9));

 2. insert a row into the table:
 INSERT INTO mytab (name, marks) VALUES ('abc', 3);

 3. compile the function myproc (at end of mail)

 4. Open sql prompt and type:
     begin;
     select myproc(1, 'xyz', 3, 'abc', 10, 'pqr');

 This would insert into the table the values 'xyz' and 1.

 5. Open another sql prompt and type:
     begin;
     select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');

 This would try and insert into the table values 'pqr' and 10.

 But as the query in step4 has locked the table records, the query of
 step 5 would wait..

 6. On the first  sql prompt type commit;
 This would let the transaction of step 5 complete, but it outputs the
 statement "not exists". This means that even after the transaction was
 commited, the insert of step 4 was not visible in query of step 5.

 7. on sql prompt of step 5, again type
 select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');

 and this outputs "exists" which means that now the insert is visible.
 Therefore it implies that if the second transaction is blocking on a
 locked resource, after it resumes, it does not see any inserts, but if
 has not blocked, these inserts are visible.

 The same steps were tried on oracle 8.1.7.
 Steps:

 1. Create a table
 create table mytab (name varchar(100), marks int);

 2. insert a row into the table:
 INSERT INTO mytab (name, marks) VALUES ('abc', 3);
 commit;

 3. compile the procedure myproc (at end of mail)

 4. Open sql prompt (set server output on) and type:
     exec myproc(1, 'xyz', 2, 'abc', 10, 'pqr');

 5. Open another sql prompt and type (set server output on):
 exec myproc(10, 'pqr', 2, 'abc', 1, 'xyz');
 But as the query is step4 has locked the table records, the query of
 step 5 would wait..

 6. On the first sql type commit;
 This would let the transaction of step 5 complete, and it outputs the
 statement "exists". This means that after the transaction was
 commited, the insert of step 4 is visible in query of step 5.

______________________________________________________________________
 Postgres function
____________________________________________________________________

 CREATE FUNCTION myproc (INT8, VARCHAR, INT8, VARCHAR, INT8, VARCHAR)
 RETURNS TEXT AS '
 DECLARE

 DBMarks  ALIAS FOR $1;
 DBName  ALIAS FOR $2;

 DBMarks2 ALIAS FOR $3;
 DBName2  ALIAS FOR $4;

 DBMarks3 ALIAS FOR $5;
 DBName3  ALIAS FOR $6;

 DBMarks4 INT8;
 DBName4  VARCHAR (100);

 BEGIN

 SELECT name, marks
 INTO   DBName4, DBMarks4
 FROM mytab
 WHERE name = DBName2
 AND marks = DBMarks2 FOR UPDATE;

 raise notice '' name : % : marks : % :'', DBName4, DBMarks4;

 INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks);

 raise notice ''insert done'';

 IF EXISTS(SELECT * FROM mytab WHERE name = DBName3 AND marks =
 DBMarks3)
 THEN
  raise notice ''exists'';
 ELSE
  raise notice ''not exists'';
 END IF;


 return ''done'';


 END;
 ' language 'plpgsql';


________________________________________________________________________
 Oracle procedure
_______________________________________________________________________

 CREATE OR REPLACE PROCEDURE myproc
 (
  DBMarks  INT,
  DBName  VARCHAR,
  DBMarks2 INT,
  DBName2  VARCHAR,
  DBMarks3 INT,
  DBName3  VARCHAR
 )
 AS
  DBMarks4 INT;
  DBName4  VARCHAR (100);

  BEGIN
   SELECT name, marks
   INTO   DBName4, DBMarks4
   FROM mytab
   WHERE name = DBName2
   AND marks = DBMarks2 FOR UPDATE;

   dbms_output.put_line(' Name :' || DBName4 || ' : Marks : ' ||
 DBMarks4 ||':');

   INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks);

   dbms_output.put_line('Insert Done');

   BEGIN
    SELECT name, marks into DBName4, DBMarks4 FROM mytab WHERE name =
 DBName3 AND marks = DBMarks3;
    dbms_output.put_line('exists');
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('not exists');
   END;
  dbms_output.put_line('done');
  END;
________________________________________________________________________

Responses

pgsql-sql by date

Next:From: treemlDate: 2004-01-30 08:19:32
Subject: update more than 1 table (mysql to postgres)
Previous:From: Shridhar DaithankarDate: 2004-01-30 07:03:45
Subject: Re: On the performance of views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group