|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
select for updateI am a Oracle DBA new to SQL Server. We actually have a small MSDE
database running here. How do I select records for update so if somebody tries to updates I have locked they will get an error. In Oracle I would just do: select column1 from mytable for update In sql server I try to do the same thing but it does not lock them. I am also selecting the records in a cursor. So I really want all the records in the cursor to be locked no matter what record I am reading at the time. If somebody could help me out I would appreciate it. Thanks Jeff Hello,
Put the SELECT inside a transaction block. Begin Tran Select * from tablename ------- ------- Commit Thanks Hari <jeffchi***@gmail.com> wrote in message Show quote news:1173469664.664519.43860@h3g2000cwc.googlegroups.com... >I am a Oracle DBA new to SQL Server. We actually have a small MSDE > database running here. How do I select records for update so if > somebody tries to updates I have locked they will get an error. > In Oracle I would just do: > select column1 > from mytable > for update > > In sql server I try to do the same thing but it does not lock them. I > am also selecting the records in a cursor. So I really want all the > records in the cursor to be locked no matter what record I am reading > at the time. If somebody could help me out I would appreciate it. > Thanks > > Jeff > Actually that won't do it. You need to specify the UPDLOCK hint inside the
transaction to prevent others from updating the rows until you are done with your transaction. The select will not hold the shared lock once the row is read even in a transaction. -- Show quoteAndrew J. Kelly SQL MVP "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message news:uCUbR1qYHHA.1300@TK2MSFTNGP02.phx.gbl... > Hello, > > Put the SELECT inside a transaction block. > > Begin Tran > Select * from tablename > ------- > ------- > > Commit > > Thanks > Hari > > > <jeffchi***@gmail.com> wrote in message > news:1173469664.664519.43860@h3g2000cwc.googlegroups.com... >>I am a Oracle DBA new to SQL Server. We actually have a small MSDE >> database running here. How do I select records for update so if >> somebody tries to updates I have locked they will get an error. >> In Oracle I would just do: >> select column1 >> from mytable >> for update >> >> In sql server I try to do the same thing but it does not lock them. I >> am also selecting the records in a cursor. So I really want all the >> records in the cursor to be locked no matter what record I am reading >> at the time. If somebody could help me out I would appreciate it. >> Thanks >> >> Jeff >> > > .... or even TABLOCKX (XLOCK won't cut it...) if you want to prevent others from even reading the
rows. This can be very restrictive for concurrency, so one should think twice. Also, in 2005, other readers can use some of the two snapshot modes so they won't be blocked, but this has first to be allowed at the database level and it incurs more resource usage for the modifications. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:u2$ktV3YHHA.1244@TK2MSFTNGP04.phx.gbl... > Actually that won't do it. You need to specify the UPDLOCK hint inside the transaction to prevent > others from updating the rows until you are done with your transaction. The select will not hold > the shared lock once the row is read even in a transaction. > > -- > Andrew J. Kelly SQL MVP > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > news:uCUbR1qYHHA.1300@TK2MSFTNGP02.phx.gbl... >> Hello, >> >> Put the SELECT inside a transaction block. >> >> Begin Tran >> Select * from tablename >> ------- >> ------- >> >> Commit >> >> Thanks >> Hari >> >> >> <jeffchi***@gmail.com> wrote in message >> news:1173469664.664519.43860@h3g2000cwc.googlegroups.com... >>>I am a Oracle DBA new to SQL Server. We actually have a small MSDE >>> database running here. How do I select records for update so if >>> somebody tries to updates I have locked they will get an error. >>> In Oracle I would just do: >>> select column1 >>> from mytable >>> for update >>> >>> In sql server I try to do the same thing but it does not lock them. I >>> am also selecting the records in a cursor. So I really want all the >>> records in the cursor to be locked no matter what record I am reading >>> at the time. If somebody could help me out I would appreciate it. >>> Thanks >>> >>> Jeff >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics