Thread: locking problem

locking problem

From
"cheater cheater"
Date:
<p> hi,<br /><br /> can anyone help me out on the following scenario:<br /> why this is happening, if i'm doing any
thingwrong or its the feature of postgres...<br /><br /> regards<br /> cheetor<br />
========================================================================<br/> PostgreSQL<br /> Steps:<br /><br /> 1.
Createa table<br /> create table mytab (name varchar(100), marks NUMERIC(9));<br /><br /> 2. insert a row into the
table:<br/> INSERT INTO mytab (name, marks) VALUES ('abc', 3);<br /><br /> 3. compile the function myproc (at end of
mail)<br/><br /> 4. Open sql prompt and type:<br />     begin;<br />     select myproc(1, 'xyz', 3, 'abc', 10,
'pqr');<br/><br /> This would insert into the table the values 'xyz' and 1.<br /><br /> 5. Open another sql prompt and
type:<br/>     begin;<br />     select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');<br /><br /> This would try and insert
intothe table values 'pqr' and 10.<br /><br /> But as the query in step4 has locked the table records, the query of<br
/>step 5 would wait..<br /><br /> 6. On the first  sql prompt type commit;<br /> This would let the transaction of step
5complete, but it outputs the<br /> statement "not exists". This means that even after the transaction was<br />
commited,the insert of step 4 was not visible in query of step 5.<br /><br /> 7. on sql prompt of step 5, again type<br
/>select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');<br /><br /> and this outputs "exists" which means that now the insert
isvisible.<br /> Therefore it implies that if the second transaction is blocking on a<br /> locked resource, after it
resumes,it does not see any inserts, but if<br /> has not blocked, these inserts are visible.<br /><br /> The same
stepswere tried on oracle 8.1.7.<br /> Steps:<br /><br /> 1. Create a table<br /> create table mytab (name
varchar(100),marks int);<br /><br /> 2. insert a row into the table:<br /> INSERT INTO mytab (name, marks) VALUES
('abc',3);<br /> commit;<br /><br /> 3. compile the procedure myproc (at end of mail)<br /><br /> 4. Open sql prompt
(setserver output on) and type:<br />     exec myproc(1, 'xyz', 2, 'abc', 10, 'pqr');<br /><br /> 5. Open another sql
promptand type (set server output on):<br /> exec myproc(10, 'pqr', 2, 'abc', 1, 'xyz');<br /> But as the query is
step4has locked the table records, the query of<br /> step 5 would wait..<br /><br /> 6. On the first sql type
commit;<br/> This would let the transaction of step 5 complete, and it outputs the<br /> statement "exists". This means
thatafter the transaction was<br /> commited, the insert of step 4 is visible in query of step 5.<br /><br />
______________________________________________________________________<br/> Postgres function<br />
____________________________________________________________________<br/><br /> CREATE FUNCTION myproc (INT8, VARCHAR,
INT8,VARCHAR, INT8, VARCHAR)<br /> RETURNS TEXT AS '<br /> DECLARE<br /><br /> DBMarks  ALIAS FOR $1;<br /> DBName 
ALIASFOR $2;<br /><br /> DBMarks2 ALIAS FOR $3;<br /> DBName2  ALIAS FOR $4;<br /><br /> DBMarks3 ALIAS FOR $5;<br />
DBName3 ALIAS FOR $6;<br /><br /> DBMarks4 INT8;<br /> DBName4  VARCHAR (100);<br /><br /> BEGIN<br /><br /> SELECT
name,marks<br /> INTO  DBName4, DBMarks4<br /> FROM mytab<br /> WHERE name = DBName2<br /> AND marks = DBMarks2 FOR
UPDATE;<br/><br /> raise notice '' name : % : marks : % :'', DBName4, DBMarks4;<br /><br /> INSERT INTO mytab (name,
marks)VALUES (DBName, DBMarks);<br /><br /> raise notice ''insert done'';<br /><br /> IF EXISTS(SELECT * FROM mytab
WHEREname = DBName3 AND marks =<br /> DBMarks3)<br /> THEN<br />   raise notice ''exists'';<br /> ELSE<br />   raise
notice''not exists'';<br /> END IF;<br /><br /><br /> return ''done'';<br /><br /><br /> END;<br /> ' language
'plpgsql';<br/><br /><br /> ________________________________________________________________________<br /> Oracle
procedure<br/> _______________________________________________________________________<br /><br /> CREATE OR REPLACE
PROCEDUREmyproc<br /> (<br />   DBMarks  INT,<br />   DBName  VARCHAR,<br />   DBMarks2 INT,<br />   DBName2 
VARCHAR,<br/>   DBMarks3 INT,<br />   DBName3  VARCHAR<br /> )<br /> AS<br />   DBMarks4 INT;<br />   DBName4  VARCHAR
(100);<br/><br />   BEGIN<br />   SELECT name, marks<br />   INTO  DBName4, DBMarks4<br />   FROM mytab<br />   WHERE
name= DBName2<br />   AND marks = DBMarks2 FOR UPDATE;<br /><br />   dbms_output.put_line(' Name :' || DBName4 || ' :
Marks: ' ||<br /> DBMarks4 ||':');<br /><br />   INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks);<br /><br />
 dbms_output.put_line('Insert Done');<br /><br />   BEGIN<br />     SELECT name, marks into DBName4, DBMarks4 FROM
mytabWHERE name =<br /> DBName3 AND marks = DBMarks3;<br />     dbms_output.put_line('exists');<br />   EXCEPTION<br />
   WHEN NO_DATA_FOUND THEN<br />     dbms_output.put_line('not exists');<br />   END;<br />  
dbms_output.put_line('done');<br/>   END;<br />
________________________________________________________________________<br /><br /><a
href="http://clients.rediff.com/signature/track_sig.asp"target="_blank"><img border="0" height="74" hspace="0"
src="http://ads.rediff.com/RealMedia/ads/adstream_nx.cgi/www.rediffmail.com/inbox.htm@Bottom"vspace="0" width="496"
/></a>

Re: locking problem

From
Christoph Haller
Date:
> 
> 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 p=
> ostgres...
I'd say it's a feature - see below. 
> 
> 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.

My understanding of PG transactions is within a transaction started 
with BEGIN; you'll see only what was committed before the BEGIN; 
So even after commit of step 4, step 5 is still in the pre-commit state. 

> 
>  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';
> 
> 

Regards, Christoph