dbms job

I have job which calls a package.The job executes every 3 minutes.Suppose my package take 10 minutes to execute.Will the job be locked till the first call to package completes.what happens
suhinrasheedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

techjiCommented:
it depends on the code in the package. If your entire transaction is DML there may be locks held on rows, tables and or indexes. The next execute will wait until those locks are released.
But if all the package is doing is a select then you are ok.
SujithData ArchitectCommented:
Please close ur previous posts
sathyagiriCommented:
It will continue to execute the package every 3 minutes irrespective of whether the first call completed.

Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

suhinrasheedAuthor Commented:
sathya are you sure.Suppose what my package or procedure whatever it is does is INSERT RECORDS INTO A TABLE X.

Suppose there are 1000 records to be inserted and after the end of 3 mints only 800 have been inserted.So do you mean if after 3 minutes if the jon again starts executing that means there will be an overwriting of records,even before a commit happens in the first run
techjiCommented:
Here is a summary of implicit locks held by oracle when you do DML.
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10743/consist.htm#BABCJIAJ

A transaction gets an exclusive DML lock for each row modified by any of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause. Due to these locks there will be no overwriting of data.

Any future transactions hitting these rows will have to wait until the previous transactions commits. But this wait is not significant unless you are updating million odd rows and you have index on every column of the table.

suhinrasheedAuthor Commented:
techji i didnt understand your last stanza and could you briefly give an explanation for my post
techjiCommented:
If you look at the above link, there is an explanation on implicit locks. These locks are necessary for transaction consistency and data concurrency of the tables. Say you are doing a DML on a table, oracle implicitly locks the rows that it is modifying. These row level locks are exclusive locks, meaning no other transaction can modify these rows until the first transaction which opened it finishes. A transaction finishes when a commit is issued.

Assuming your package inserts 1000 records in a single execution, oracle implicitly locks all the 1000 rows that are being inserted. There is no limit on how many row level locks can be held by oracle. No other sql or transaction will be able to touch these rows until a commit or rollback is issued from the first exection. So to answer your concern about overwriting, oracle wont allow it.

Assuming your second execution inserts another 1000 records, not same as the first set, then oracle will acquire another 1000 implicit row level locks and holds them until a commit or rollback is issued. But even if this execution finishes earlier than the first execution, the updates to table will wait for the first update to finish before its flushed down to disk.

UPDATE & DELETE are little more complicated than INSERT. Please read the above link to find out more.

Hope this helps :)
suhinrasheedAuthor Commented:
thanx techji i got your point.So can i confirm from your side that the job will never get locked and it will continue to execute the package every 3 minutes irrespective of whether the first call completed.
But the data will be written to the database only when the first call to the job has been completed..which means even though the second call may have finished the procedure on time and done all inserts and updates,these changes will not be reflected in database unless the first call to procedure finishes.RIGHT?
techjiCommented:
You are correct.
sathyagiriCommented:

>>sathya are you sure.Suppose what my package or procedure whatever it is does is INSERT RECORDS INTO A TABLE X.

Suppose there are 1000 records to be inserted and after the end of 3 mints only 800 have been inserted.So do you mean if after 3 minutes if the jon again starts executing that means there will be an overwriting of records,even before a commit happens in the first run

The DBMS_JOB will invoke your package every 3 minutes. But whether or not your inserts/delete/updates get executed will be controlled by Oracle's locking mechanism
suhinrasheedAuthor Commented:
Tchji this is with reference to your following lines

Assuming your second execution inserts another 1000 records, not same as the first set, then oracle will acquire another 1000 implicit row level locks and holds them until a commit or rollback is issued. But even if this execution finishes earlier than the first execution, the updates to table will wait for the first update to finish before its flushed down to disk.

So if the second executions finishes earlier and issues a commit also,still it will not be written to database...the procedure has a commit statement inside it
techjiCommented:
A simple example should clarify your dilema -

In the first example, I am inserting the same records from 2 sessions. The first session executed an insert and the row got inserted. The second session did the same sql command, but the command waits until the first session commits. This behavior is because you are trying to insert the same record from both sessions. Whoever does the first insert gets preference.

session 1
---------------
SQL> insert into dept values (1,'hello','whatever');
1 row created.

session 2
---------------
SQL> insert into dept values (1,'hello','whatever');
waits...............................

session 1
---------------
SQL> commit;
Commit complete.

session 2
---------------
SQL> insert into dept values (2,'hello','whatever');
insert into dept values (2,'hello','whatever')
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$ORACLE.SYS_C001976) violated

---------------------------------------------------------------------------------------
this is how implicit locking works on inserts. Now if you are inserting totally different set of records, then the second commit will work just fine.

session 1
---------------
SQL> insert into dept values (2,'hello','whatever');
1 row created.

session 2
---------------
SQL> insert into dept values (3,'hello','whatever');
1 row created.

session 2
---------------
SQL> commit;
Commit complete.

session 1
---------------
SQL> commit;
Commit complete.

-----------------------------------------------------------------------------------------------------
hope this helps :)
techjiCommented:
suhinrasheedAuthor Commented:
NOW MY JOB WILL BE EXECUTING IN THE SAME SESSION.SO WHEN YOU TELL THAT EVEN IF SECOND CALL TO THE JOB COMPLETES BEFORE THE FIRST CALL..THAT'S MY POINT OF CONFUSION.hOW CAN SECOND CALL FINISH BEFORE FIRST CALL IF LOCKS ARE ATTAINED BY THE FIRST CALL
techjiCommented:
>>>> NOW MY JOB WILL BE EXECUTING IN THE SAME SESSION
you are going to run this as a DBMS_JOB. So it no the same session which is executing the package. Once it is submitted as a dbms_job, oracle spawns a new session everytime the job is executed. are you clear on how the dbms_job works ???? dbms_jobs is oracle scheduler similar to other schedulars (cron, task schedular etc.,). Just relate how these schedulars work with dbms_job in oracle.

>>>> SO WHEN YOU TELL THAT EVEN IF SECOND CALL TO THE JOB COMPLETES BEFORE THE FIRST CALL ..THAT'S MY POINT OF CONFUSION .hOW CAN SECOND CALL FINISH BEFORE FIRST CALL IF LOCKS ARE ATTAINED BY THE FIRST CALL
please relate to the example I gave you. If your inserts are same records, then the second call will not finish until you finish your first call. If your inserts are different records each time, then the second call will finish and the data is written to disk before the first records. That is exactly what my examples depict.
suhinrasheedAuthor Commented:
that means if it is a different set of records in the second call and it finishes quicker and writes records to database and now the first call finishes ,it will now overite the database with old set
techjiCommented:
what do you mean by overwrite ? you are inserting new records each time. they get added to the table you are inserting. Your table grows with every insert statement. There is no overwriting of old records with inserts.

what is your experience in database technology. You seem to ask some very basic questions on how the data is stored in the database ??????

I can understand a question about how transaction consistency is mainted (your oringial question). but now you are asking about an insert overwriting previous insert ????????

An update would change the existing records. An insert inserts new records to the table. The table grows with inserts as the data gets appended to the table. A delete would remove records from the table and the table shrinks after a delete.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suhinrasheedAuthor Commented:
sorry i got wrong...got totally out ..i got it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.