Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Krishnam
Starting Member
1 Post |
 Posted - 2010-04-12 : 12:45:55
|
| Hi,I have a select query with a sub query like below(sub query is also using same table 'schd_dtl'). I want to make it process safe.(i.e. more than one process should not execute this query at a time. If I want to use WITH(READPAST,UPDLOCK) hints, should I use it for every SELECT statement involved in the query? or it is enough to use in only sub query?I am from Oracle background, new to SQL Server. Please help me.SELECT @Schd_Detl_ID = schd_dtl_idFROM schd_dtl WITH(READPAST,UPDLOCK)WHERE schd_dtl_id = ( SELECT min(TD1.schd_dtl_id) FROM schd_dtl TD1 WITH(READPAST,UPDLOCK) WHERE ( TD1.next_fire_sch_time = ( SELECT min(TD2.next_fire_sch_time) FROM schd_dtl TD2 WITH(READPAST,UPDLOCK) WHERE (TD2.next_fire_sch_time <= @Next_Fire_Sch_Time) ) ) ) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-12 : 17:57:42
|
| Adding the locks does not prevent the query from running in another session at the same time. The locks are used to lock data, not a query. So you could have two different sessions running the same query and not block each other at all because perhaps they are hitting a different MIN(ColumnName). I don't know of a way to lock a query except to write code in the application for this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
SQL Server UPDLOCK hint with subqueries