locking problem
От | cheater cheater |
---|---|
Тема | locking problem |
Дата | |
Msg-id | 20040130074142.26889.qmail@webmail36.rediffmail.com обсуждение исходный текст |
Ответы |
Re: locking problem
|
Список | pgsql-sql |
<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>