Sequential Atomic procedures

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Sequential Atomic procedures

Postby JDSal » Wed Jul 17, 2013 4:21 pm

My question,

If I have three procedures that retrieve the max(id-number) from a single table and then that number is incremented once in each of these three procedures. Will these three procedures start at the same time? Which would create possible duplicate id numbers, which I do not want? So if procedure one takes a long time to process, will stored procedure two start while stored procedure one is still running (i.e. stored procedure two and one will have the same max(id_number)), or will it wait until the first stored procedure is completed? Or, do I need to add some code to make sure one procedure completes prior to executing the second and third procedure?

Thanks
JDSal
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: Sequential Atomic procedures

Postby Tim... » Wed Jul 17, 2013 4:48 pm

Hi.

First, I would say never do this. Use a sequence to provide unique numbers for an ID column. They are fast and don't screw up your performance like selecting max+1 will.

If you must do this, then you need to do something like this,

Code: Select all
CREATE OR REPLACE PROCEDURE proc AS
  l_value NUMBER;
BEGIN
  SELECT MAX(id_number)+1
  INTO      l_value
  FROM   my_table
  FOR UPDATE;

  UPDATE my_table
  SET id_number = l_value;
  COMMIT;
END;
/


Here, we are locking the table, so nothing else can interfere with it until the update is complete and committed. This way, the result is perfect, no matter how many session try to do it at the same time.

Like I said, this is a performance disaster and should be avoided at all costs!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Sequential Atomic procedures

Postby JDSal » Wed Jul 17, 2013 6:35 pm

Thanks I added the FOR UPDATE, I get a message 'for update of this query expression is not allowed'
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: Sequential Atomic procedures

Postby Tim... » Wed Jul 17, 2013 8:01 pm

Hi.

Sorry. My bad. Wasn't paying attention. If this were selecting specific rows it would be fine, but as it is an aggregate function it is not allowed. You would have to explicitly lock the table.

Code: Select all
CREATE OR REPLACE PROCEDURE proc AS
  l_value NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'LOCK TABLE my_table IN EXCLUSIVE MODE';

  SELECT MAX(id_number)+1
  INTO      l_value
  FROM   my_table;

  UPDATE my_table
  SET id_number = l_value;
  COMMIT;
END;
/


I really can't emphasize enough what a bad solution this is. Please don't do it. You are crippling your database performance by serializing in this way.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Sequential Atomic procedures

Postby JDSal » Wed Jul 17, 2013 8:51 pm

I will try to figure something else out, I don't want any issues. Thanks for the information so I take it that procedures don't run sequentially, that is completing one procedure before moving onto another procedure? Also I have too increment the id number in the table a few time in each procedure so I would probably have to lock it each additional time in each time it is incremented in each procedure correct?

This similar to what I had excluding the execute immediate. Would I have to lock it each time as show below?
Code: Select all
CREATE OR REPLACE PROCEDURE proc AS
  l_value NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'LOCK TABLE my_table IN EXCLUSIVE MODE';

  SELECT MAX(id_number)
  INTO      l_value
  FROM   my_table;
 lvalue = lvalue+1
  INSERT INTO my_table
  (id_number, FIELD2) VALUES (l_value, 'A123');
  COMMIT;
EXECUTE IMMEDIATE 'LOCK TABLE my_table IN EXCLUSIVE MODE';
 lvalue = lvalue+1
 INSERT INTO my_table
  (id_number, FIELD2) VALUES (l_value, 'B123');
  COMMIT;
END;
/
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: Sequential Atomic procedures

Postby Tim... » Wed Jul 17, 2013 9:31 pm

Hi.

No. Your solution would not work as between the COMMIT and the following EXECUTE IMMEDIATE, the table would not be locked. During that fraction of a second, another process could step in and lock the record.

Procedures do not run consecutively if there are multiple session calling them.

If this is just to provide a primary key value you should be using a sequence, not manually trying to populate the column yourself. For example, create a sequence for use with this table.

Code: Select all
CREATE SEQUENCE my_table_seq;


From now on the procedure just needs to look like this.

Code: Select all
CREATE OR REPLACE PROCEDURE proc AS
  l_value NUMBER;
BEGIN
  INSERT INTO my_table
  (id_number, FIELD2) VALUES (my_table_seq.NEXTVAL, 'A123');

  INSERT INTO my_table
  (id_number, FIELD2) VALUES (my_table_seq.NEXTVAL, 'B123');
  COMMIT;
END;
/


It will be much faster and safe when multiple session are using the procedure at the same time.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Sequential Atomic procedures

Postby JDSal » Thu Jul 18, 2013 1:37 pm

Code: Select all
CREATE SEQUENCE my_table_seq;


Will this lock the table? In addition I found I don't have rights to peform this, is there a big reason for not allowing developers rights to this?
What does SET TRANSACTION READ WRITE accomplish?
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: Sequential Atomic procedures

Postby Tim... » Thu Jul 18, 2013 3:35 pm

Hi.

1) Using sequences will not lock the table. They are quick and multi-user safe.

2) To create a sequence the user needs CREATE SEQUENCE privilege. If you have the ability to create tables, I can't see anyone stopping you from creating sequences. Just ask for permission.

3) The SET TRANSACTION stuff is there because of the ANSI standards. Do you know how many times I've altered from the default in nearly 20 years of using Oracle? Never. Don't touch it. It is not the solution you are looking for.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests