SQL Server Isolation Level - Table Locks

[TimHop12]

Hello,

I ran into a problem, where I am not sure how sql server is reacting -

There is a table dbo.T1 with columns (OWNER, DATA)- now I have 3 stored procs - dbo.Begin, dbo.Copy, dbo.END.

There is a process running which reads a file (file contains - OWNER and DATA) and calls these procs in the sequence dbo.Begin, dbo.Copy, dbo.END.

dbo.Begin proc deletes the data from dbo.T1 table where OWNER='X'
dbo.Copy proc copies the data to dbo.T1 table. (So this time all the existing data for this owner 'X' is deleted from the table T1)
dbo.END proc grabs the copied data from the table T1 and distribute in multiple tables.


Now my question is - if there is a file f1.txt placed so dbo.Begin will clear data from T1 table and after then dbo.Copy will start copying the data from f1.txt table dbo.T1. Now - if a new file f2.txt for the same Owner is placed - dbo.Begin will start clearing the data - but the problem is earlier execution of dbo.Copy is not yet finished. So this may be a problem and the 2nd instance of dbo.Begin might NOT delete all the data from table T1.

Please advice how this could be handles, I think SQL setver would take care of itself by having locks on the table T1, but not exactly sure how.

Any help is appreciated. Thanks

[Delerna]

Im not absolutely sure either, but I think so.
If you are worried about it though then you could create a table that only ever has either 1 or no records in it. Or only 1 or no records for a particular user. Or some other scheme that satisfies you.

The first stored proc in the sequence first checks to see if a record exist.
if not then it adds a record and allows the stored proc to run.
if so it reports back to the caller that they have to wait their turn.

The last stored proc then deletes the record from the table after it has finished.
I have only sketched the idea here and you will need to work out the exact details to make it work for you.

The Idea can't hurt performance much and might give you peace of mind, even if unnecessary!