|
gentle228
Starting Member
2 Posts |
 Posted - 2012-01-23 : 02:49:51
|
| Hi,could you please help me how to lock TOP N records in sql server 2008,i have written below query to select TOP 5 records matching the given condition, actual query may result 100 + records but i need only top 5 and lock them from a process. if another process comes and execute the same query i should be able to get next 5 records from the 100+ which are not locked and process. but this query actually locking all 100+ records and returned me TOP 5. so other process could not able to get any records.SELECT jobdetails.* FROM MedJobDetails jobdetails WITH (ROWLOCK,UPDLOCK,READPAST) INNER JOIN (SELECT TOP 1 job.* FROM MedJobDetails job WITH (ROWLOCK,UPDLOCK,READPAST) INNER JOIN dbo.MedTimeZoneData tz ON (job.MedSourceTimeZoneName = tz.MedSourceTimeZoneName) WHERE job.JobType = :jobType AND (job.MedProcStatusID=1 OR (job.MedProcStatusID =4 AND DATEDIFF (MINUTE,CONVERT(varchar(25),job.MedProcEnd,120),CONVERT(varchar(25),getdate(),120)) >= :retryTime) OR (job.MedProcStatusID =2 AND DATEDIFF (MINUTE,CONVERT(varchar(25),job.MedProcStart,120),CONVERT(varchar(25),getdate(),120)) >= :maxWaitTime) ) AND (DATEDIFF(MINUTE,job.JobDateTime,DATEADD(minute,tz.STDMinutesOffsetFromUTC - :jobDelay,GETUTCDATE())) >0) ORDER BY job.JobDateTime ASC) job1 ON (jobdetails.MedSourceIp=job1.MedSourceIp AND jobdetails.JobDateTime = job1.JobDateTime) INNER JOIN dbo.MedTimeZoneData tz ON (jobdetails.MedSourceTimeZoneName = tz.MedSourceTimeZoneName) WHERE jobdetails.JobType = :jobType AND (jobdetails.MedProcStatusID=1 OR (jobdetails.MedProcStatusID =4 AND DATEDIFF (MINUTE,CONVERT(varchar(25),jobdetails.MedProcEnd,120),CONVERT(varchar(25),getdate(),120)) >= :retryTime) OR (jobdetails.MedProcStatusID =2 AND DATEDIFF (MINUTE,CONVERT(varchar(25),jobdetails.MedProcStart,120),CONVERT(varchar(25),getdate(),120)) >= :maxWaitTime) ) AND (DATEDIFF(MINUTE,jobdetails.JobDateTime,DATEADD(minute,tz.STDMinutesOffsetFromUTC - :jobDelay,GETUTCDATE())) >0)kindly provide me the solution as soon as possible |
|
Lock Top N records in sql server 2008