pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlocking problem
2+ messages / 2 participants
[nested] [flat]
* locking problem
@ 2004-01-30 07:41 cheater cheater <cheetor@rediffmail.com>
2004-02-03 15:08 ` Re: locking problem Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 2+ messages in thread
From: cheater cheater @ 2004-01-30 07:41 UTC (permalink / raw)
To: 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;
________________________________________________________________________
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: locking problem
2004-01-30 07:41 locking problem cheater cheater <cheetor@rediffmail.com>
@ 2004-02-03 15:08 ` Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Haller @ 2004-02-03 15:08 UTC (permalink / raw)
To: cheetor@rediffmail.com; +Cc: 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 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-03 15:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-30 07:41 locking problem cheater cheater <cheetor@rediffmail.com>
2004-02-03 15:08 ` Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox