Insert and select on a table at the same time

Question

Insert and select on a table at the same time

Pink123

Default port

Points: 1486
More actions
February 24, 2011 at 7:26 am

#232648

Hi,
I have a job A which inserts data into a table and another job B which selects data from the same table .Both the jobs run at the same time.
Will this cause any issue?
The insert will add some extra data or may be no data at all depending on the conditions.
Please help.
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

That scenario won't cause deadlocking or any other issue, AFAIK. READ COMMITTED is the default isolation level in SQL Server, which will only allow reads on committed records - so even if the read and the insert happened at the same exact moment, the read operation would not even attempt to access the newly (uncommitted) records that are in the process of being inserted.

Check out this article:

http://blogs.msdn.com/b/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx

_________________________________
seth delconte
http://sqlkeys.com

Pink123 Default port Points: 1486 More actions · Answer 2

Thanks for the reply.

My job A does some insert and updates on Table A

Job B select data from table A at the same time.

There are no records for select as my query uses

if exist() for job B

If I seperately execute the query I do get results?

I am very confused as to why is this happening?

Please help

Thanks,

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

Pink123 (2/24/2011)
There are no records for select as my query uses
if exist() for job B
If I seperately execute the query I do get results?
I am very confused as to why is this happening?

What is your objective? Do you have to see the uncommitted records as soon as the insert operation starts? If so, I think changing the isolation level to READ UNCOMMITTED for the read operation is the only solution. Be aware that although this solves the concurrency problem, the record could still be rolled back after it is read.

USE MyDB

GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT...

FROM...

_________________________________
seth delconte
http://sqlkeys.com

Pink123 Default port Points: 1486 More actions · Answer 4

no my objective is just to select the records ,it doesnt matter whether the record is updated one or not.

Job A

insert into A

update A

In my job B which does a select

if exist

(select from table A)

execute SP

Job A and B run at the same time.

'if exists 'doesnt return anything which implies that select statement dint fetch any rows ,but If i execute the select statement it does fetches record.

seth delconte SSCertifiable Points: 6388 More actions · Answer 5

Pink123 (2/24/2011)
'if exists 'doesnt return anything which implies that select statement dint fetch any rows ,but If i execute the select statement it does fetches record.

Then it sounds like there is a logic error in your query. Do you have an example?

_________________________________
seth delconte
http://sqlkeys.com

Pink123 Default port Points: 1486 More actions · Answer 6

I dont think there is any problem in the logic because there is a similar job which exceutes fine .The one i mentioned in my previous post is the example it self.

Job b

If exist(Select statements)

exec sp to send email

No email is sent but the job shows sucess.

The select statement in the if exists block does return rows but no email is sent i.e the sp is not executed

seth delconte SSCertifiable Points: 6388 More actions · Answer 7

Pink123 (2/24/2011)
I dont think there is any problem in the logic because there is a similar job which exceutes fine .The one i mentioned in my previous post is the example it self.

You are providing very few details at a time about your problem. Now I understand that you want to know why the select query returns records, while the same query inside of IF EXISTS seems to return false. You need to do some basic troubleshooting and answer the following questions for yourself:

1. Does the 'send mail' stored procedure work on it's own? Has it been properly tested?

2. Is the IF EXISTS really returning false? Have you put in a print statement or anything to verify this?

3. Is the 'send mail' stored procedure being given the correct parameters?

If you have gone over all of that, you need to provide code examples so that we can help you.

_________________________________
seth delconte
http://sqlkeys.com