|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to do row level locking in SQL Server 2005?Hi,
For example, there are 2 users connecting to the same table and want to update the different rows. They are requested to use the row level locking. Pls advise and provide the example of sql statement or sample code. Thank you Connie wrote:
> For example, there are 2 users connecting to the same table and want to I'm only using SQL Server 2000, but I'm not aware of any differences in> update the different rows. They are requested to use the row level locking. > this area in SQL Server 2005 (someone correct me if I'm wrong). Anyway, row-level locking is the default granularity that SQL Server will use when locking rows. It will escalate them to page/table locks if necessary (I believe based on the amount of locking resources available). So you should rarely have to force the optimiser to use row-level locking, however if you have to then you can use locking hints. e.g. UPDATE YourTable WITH (ROWLOCK) SET YourCol = 'Value' WHERE ID = @ID If you are talking about explicitly locking a row BEFORE the user updates it, then you would need to use other locking hints, such as UPDLOCK or XLOCK. e.g. BEGIN TRANSACTION SELECT * FROM YourTable WITH (UPDLOCK) WHERE ID = @ID ........ COMMIT TRANSACTION Note that the lock is only held for the duration of the transaction. Chris Dear Chris Lim,
Thanks for reply. Yes, however in case let said ......for example, as below.. Trans 1 : UPDATE YourTable WITH (ROWLOCK) SET YourCol = 'Value' WHERE ID = 1 Trans 2: UPDATE YourTable WITH (ROWLOCK) SET YourCol = 'Value' WHERE ID = 2 Whats happen is that the Trans 2 will get waiting ....& hanging there until ...the Trans 1 release or commit... My problem here is ..how could i get a message that ....when the trans 1 row is being locked (updating), the trans 2 will get the message and diplay the allert to the user. Pls advise, thank you. Best Regards. Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1152851163.679887.69180@b28g2000cwb.googlegroups.com... > Connie wrote: >> For example, there are 2 users connecting to the same table and want to >> update the different rows. They are requested to use the row level >> locking. >> > > I'm only using SQL Server 2000, but I'm not aware of any differences in > this area in SQL Server 2005 (someone correct me if I'm wrong). > > Anyway, row-level locking is the default granularity that SQL Server > will use when locking rows. It will escalate them to page/table locks > if necessary (I believe based on the amount of locking resources > available). > > So you should rarely have to force the optimiser to use row-level > locking, however if you have to then you can use locking hints. > > e.g. > > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = @ID > > > If you are talking about explicitly locking a row BEFORE the user > updates it, then you would need to use other locking hints, such as > UPDLOCK or XLOCK. > > e.g. > > BEGIN TRANSACTION > > SELECT * > FROM YourTable WITH (UPDLOCK) > WHERE ID = @ID > > ....... > > COMMIT TRANSACTION > > > Note that the lock is only held for the duration of the transaction. > > Chris > The code you listed should not block each other. Do you have any additional
code in your transaction? Ben Nevarez, MCDBA, OCP Database Administrator Show quote "Connie" wrote: > Dear Chris Lim, > > Thanks for reply. Yes, however in case let said ......for example, as > below.. > > Trans 1 : > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > Trans 2: > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 2 > > Whats happen is that the Trans 2 will get waiting ....& hanging there until > ...the Trans 1 release or commit... > My problem here is ..how could i get a message that ....when the trans 1 row > is being locked (updating), the trans 2 will get the message and diplay the > allert to the user. > Pls advise, thank you. > > Best Regards. > > "Chris Lim" <blackca***@hotmail.com> wrote in message > news:1152851163.679887.69180@b28g2000cwb.googlegroups.com... > > Connie wrote: > >> For example, there are 2 users connecting to the same table and want to > >> update the different rows. They are requested to use the row level > >> locking. > >> > > > > I'm only using SQL Server 2000, but I'm not aware of any differences in > > this area in SQL Server 2005 (someone correct me if I'm wrong). > > > > Anyway, row-level locking is the default granularity that SQL Server > > will use when locking rows. It will escalate them to page/table locks > > if necessary (I believe based on the amount of locking resources > > available). > > > > So you should rarely have to force the optimiser to use row-level > > locking, however if you have to then you can use locking hints. > > > > e.g. > > > > UPDATE YourTable WITH (ROWLOCK) > > SET YourCol = 'Value' > > WHERE ID = @ID > > > > > > If you are talking about explicitly locking a row BEFORE the user > > updates it, then you would need to use other locking hints, such as > > UPDLOCK or XLOCK. > > > > e.g. > > > > BEGIN TRANSACTION > > > > SELECT * > > FROM YourTable WITH (UPDLOCK) > > WHERE ID = @ID > > > > ....... > > > > COMMIT TRANSACTION > > > > > > Note that the lock is only held for the duration of the transaction. > > > > Chris > > > > > Connie wrote:
Show quote > Trans 1 : Hmm, I wouldn't expect Trans 2 to be blocked because it's a different> UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > Trans 2: > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 2 > > Whats happen is that the Trans 2 will get waiting ....& hanging there until > ..the Trans 1 release or commit... > My problem here is ..how could i get a message that ....when the trans 1 row > is being locked (updating), the trans 2 will get the message and diplay the > allert to the user. row. I ran the following test quickly: create table temp1(test1 int) create unique index pk_temp1 on temp1(test1) insert temp1 values(1) insert temp1 values(2) Session 1 ---------------- begin tran update temp1 set test1 = 1 where test1 = 1 Session 2 begin tran update temp1 set test1 = 1 where test2 = 2 Session 2 was not blocked by session 1. You do have a primary key on your table? Chris Connie
How about indexes ? Do you have any? In your case it dopes not seem the tran 2 is going to be blocked. There is no need to specify rowlock hint because SQL Server performs its by default Show quote "Connie" <yfc***@kdu.edu.my> wrote in message news:uPPCY7vpGHA.524@TK2MSFTNGP05.phx.gbl... > Dear Chris Lim, > > Thanks for reply. Yes, however in case let said ......for example, as > below.. > > Trans 1 : > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > Trans 2: > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 2 > > Whats happen is that the Trans 2 will get waiting ....& hanging there > until ..the Trans 1 release or commit... > My problem here is ..how could i get a message that ....when the trans 1 > row is being locked (updating), the trans 2 will get the message and > diplay the allert to the user. > Pls advise, thank you. > > Best Regards. > > "Chris Lim" <blackca***@hotmail.com> wrote in message > news:1152851163.679887.69180@b28g2000cwb.googlegroups.com... >> Connie wrote: >>> For example, there are 2 users connecting to the same table and want to >>> update the different rows. They are requested to use the row level >>> locking. >>> >> >> I'm only using SQL Server 2000, but I'm not aware of any differences in >> this area in SQL Server 2005 (someone correct me if I'm wrong). >> >> Anyway, row-level locking is the default granularity that SQL Server >> will use when locking rows. It will escalate them to page/table locks >> if necessary (I believe based on the amount of locking resources >> available). >> >> So you should rarely have to force the optimiser to use row-level >> locking, however if you have to then you can use locking hints. >> >> e.g. >> >> UPDATE YourTable WITH (ROWLOCK) >> SET YourCol = 'Value' >> WHERE ID = @ID >> >> >> If you are talking about explicitly locking a row BEFORE the user >> updates it, then you would need to use other locking hints, such as >> UPDLOCK or XLOCK. >> >> e.g. >> >> BEGIN TRANSACTION >> >> SELECT * >> FROM YourTable WITH (UPDLOCK) >> WHERE ID = @ID >> >> ....... >> >> COMMIT TRANSACTION >> >> >> Note that the lock is only held for the duration of the transaction. >> >> Chris >> > > Dear all,
Thank you for reply. Sorry ..mistake in my example statement. Actually...there is no problem in updating the different row..However...when multiple users update the same row, then ...the 2nd trans ..will hang ...and in the waiting status..until ..the 1st trans release.. Trans 1 : UPDATE YourTable WITH (ROWLOCK) SET YourCol = 'Value' WHERE ID = 1 Trans 2: UPDATE YourTable WITH (ROWLOCK) SET YourCol = 'Value' WHERE ID = 1 in this example, Is there any way ....to allow the 2nd trans ..to alert ...the user..when the 1st...trans is updating the row.? Pls advise, thank you. Best Regards. Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:eKD1XKwpGHA.4920@TK2MSFTNGP04.phx.gbl... > Connie > How about indexes ? Do you have any? > In your case it dopes not seem the tran 2 is going to be blocked. There is > no need to specify rowlock hint because SQL Server performs its by default > > > > > > > > "Connie" <yfc***@kdu.edu.my> wrote in message > news:uPPCY7vpGHA.524@TK2MSFTNGP05.phx.gbl... >> Dear Chris Lim, >> >> Thanks for reply. Yes, however in case let said ......for example, as >> below.. >> >> Trans 1 : >> UPDATE YourTable WITH (ROWLOCK) >> SET YourCol = 'Value' >> WHERE ID = 1 >> >> Trans 2: >> UPDATE YourTable WITH (ROWLOCK) >> SET YourCol = 'Value' >> WHERE ID = 2 >> >> Whats happen is that the Trans 2 will get waiting ....& hanging there >> until ..the Trans 1 release or commit... >> My problem here is ..how could i get a message that ....when the trans 1 >> row is being locked (updating), the trans 2 will get the message and >> diplay the allert to the user. >> Pls advise, thank you. >> >> Best Regards. >> >> "Chris Lim" <blackca***@hotmail.com> wrote in message >> news:1152851163.679887.69180@b28g2000cwb.googlegroups.com... >>> Connie wrote: >>>> For example, there are 2 users connecting to the same table and want to >>>> update the different rows. They are requested to use the row level >>>> locking. >>>> >>> >>> I'm only using SQL Server 2000, but I'm not aware of any differences in >>> this area in SQL Server 2005 (someone correct me if I'm wrong). >>> >>> Anyway, row-level locking is the default granularity that SQL Server >>> will use when locking rows. It will escalate them to page/table locks >>> if necessary (I believe based on the amount of locking resources >>> available). >>> >>> So you should rarely have to force the optimiser to use row-level >>> locking, however if you have to then you can use locking hints. >>> >>> e.g. >>> >>> UPDATE YourTable WITH (ROWLOCK) >>> SET YourCol = 'Value' >>> WHERE ID = @ID >>> >>> >>> If you are talking about explicitly locking a row BEFORE the user >>> updates it, then you would need to use other locking hints, such as >>> UPDLOCK or XLOCK. >>> >>> e.g. >>> >>> BEGIN TRANSACTION >>> >>> SELECT * >>> FROM YourTable WITH (UPDLOCK) >>> WHERE ID = @ID >>> >>> ....... >>> >>> COMMIT TRANSACTION >>> >>> >>> Note that the lock is only held for the duration of the transaction. >>> >>> Chris >>> >> >> > > Connie wrote:
Show quote > Dear all, There's no easy way to do this, but you might find the following thread> Thank you for reply. Sorry ..mistake in my example statement. > Actually...there is no problem in updating the different row..However...when > multiple users update the same row, then ...the 2nd trans ..will hang ...and > in the waiting status..until ..the 1st trans release.. > > Trans 1 : > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > Trans 2: > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > > in this example, Is there any way ....to allow the 2nd trans ..to alert > ..the user..when the 1st...trans is updating the row.? relevant: http://tinyurl.com/nrz8d Your updates shouldn't take long, so I don't see any problem with the second process waiting until the first one has finished. If you are worried about the second process overwriting the first process's changes, then you could implement optimistic locking. Chris Dear Chris,
thanks for reply. Yes..this is a simple update...however..when ..1st user perform the batch processing ..& 2nd..user..want to update.....the record.......that being updated by the 1st user.. Then ...how? In this case also..even though the 1st tran already released ..but still the 2nd trans ...is hanging there....non-stop.... haha..just thinking want to alert the 2nd user....when ..he try to update..... FYI, Currently .. in my application ..i already make use of Optimistic Condurrency Control (SQL 2005) ..where i uses snapshot isolation .level control (for read and update..row version)...but not update and update scenario... Pls advise...Thank you Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1152855896.468106.92030@s13g2000cwa.googlegroups.com... > Connie wrote: >> Dear all, >> Thank you for reply. Sorry ..mistake in my example statement. >> Actually...there is no problem in updating the different >> row..However...when >> multiple users update the same row, then ...the 2nd trans ..will hang >> ...and >> in the waiting status..until ..the 1st trans release.. >> >> Trans 1 : >> UPDATE YourTable WITH (ROWLOCK) >> SET YourCol = 'Value' >> WHERE ID = 1 >> >> Trans 2: >> UPDATE YourTable WITH (ROWLOCK) >> SET YourCol = 'Value' >> WHERE ID = 1 >> >> >> in this example, Is there any way ....to allow the 2nd trans ..to alert >> ..the user..when the 1st...trans is updating the row.? > > There's no easy way to do this, but you might find the following thread > relevant: > > http://tinyurl.com/nrz8d > > Your updates shouldn't take long, so I don't see any problem with the > second process waiting until the first one has finished. If you are > worried about the second process overwriting the first process's > changes, then you could implement optimistic locking. > > Chris > A well designed application and database will have little blocking. If you
are getting timeouts because of long running transactions and blocking behavior, then there are other issues involved. The simple examples you posted should never block each other. How often, and how many users are attempting to update the same row? Occasionally -probably not worth much effort. Frequently, then there may be a way to attempt to gain control and stop the transaction before it even starts so the user isn't blocked. These articles exposes a couple of possibilities. (Forewarned: This much control reaches the state of extreme micromanagement.) http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm http://blogs.msdn.com/federaldev/archive/2006/03/13/550585.aspx -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Connie" <yfc***@kdu.edu.my> wrote in message news:%23gc1MYwpGHA.4996@TK2MSFTNGP05.phx.gbl... > Dear all, > Thank you for reply. Sorry ..mistake in my example statement. > Actually...there is no problem in updating the different > row..However...when multiple users update the same row, then ...the 2nd > trans ..will hang ...and in the waiting status..until ..the 1st trans > release.. > > Trans 1 : > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > Trans 2: > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > > in this example, Is there any way ....to allow the 2nd trans ..to alert > ..the user..when the 1st...trans is updating the row.? > > Pls advise, thank you. > > Best Regards. > > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:eKD1XKwpGHA.4920@TK2MSFTNGP04.phx.gbl... >> Connie >> How about indexes ? Do you have any? >> In your case it dopes not seem the tran 2 is going to be blocked. There >> is no need to specify rowlock hint because SQL Server performs its by >> default >> >> >> >> >> >> >> >> "Connie" <yfc***@kdu.edu.my> wrote in message >> news:uPPCY7vpGHA.524@TK2MSFTNGP05.phx.gbl... >>> Dear Chris Lim, >>> >>> Thanks for reply. Yes, however in case let said ......for example, as >>> below.. >>> >>> Trans 1 : >>> UPDATE YourTable WITH (ROWLOCK) >>> SET YourCol = 'Value' >>> WHERE ID = 1 >>> >>> Trans 2: >>> UPDATE YourTable WITH (ROWLOCK) >>> SET YourCol = 'Value' >>> WHERE ID = 2 >>> >>> Whats happen is that the Trans 2 will get waiting ....& hanging there >>> until ..the Trans 1 release or commit... >>> My problem here is ..how could i get a message that ....when the trans 1 >>> row is being locked (updating), the trans 2 will get the message and >>> diplay the allert to the user. >>> Pls advise, thank you. >>> >>> Best Regards. >>> >>> "Chris Lim" <blackca***@hotmail.com> wrote in message >>> news:1152851163.679887.69180@b28g2000cwb.googlegroups.com... >>>> Connie wrote: >>>>> For example, there are 2 users connecting to the same table and want >>>>> to >>>>> update the different rows. They are requested to use the row level >>>>> locking. >>>>> >>>> >>>> I'm only using SQL Server 2000, but I'm not aware of any differences in >>>> this area in SQL Server 2005 (someone correct me if I'm wrong). >>>> >>>> Anyway, row-level locking is the default granularity that SQL Server >>>> will use when locking rows. It will escalate them to page/table locks >>>> if necessary (I believe based on the amount of locking resources >>>> available). >>>> >>>> So you should rarely have to force the optimiser to use row-level >>>> locking, however if you have to then you can use locking hints. >>>> >>>> e.g. >>>> >>>> UPDATE YourTable WITH (ROWLOCK) >>>> SET YourCol = 'Value' >>>> WHERE ID = @ID >>>> >>>> >>>> If you are talking about explicitly locking a row BEFORE the user >>>> updates it, then you would need to use other locking hints, such as >>>> UPDLOCK or XLOCK. >>>> >>>> e.g. >>>> >>>> BEGIN TRANSACTION >>>> >>>> SELECT * >>>> FROM YourTable WITH (UPDLOCK) >>>> WHERE ID = @ID >>>> >>>> ....... >>>> >>>> COMMIT TRANSACTION >>>> >>>> >>>> Note that the lock is only held for the duration of the transaction. >>>> >>>> Chris >>>> >>> >>> >> >> > > If they update the same record then set the @@lock_timeout with enough time
for them to go for a cup of coffee :-) Ben Nevarez, MCDBA, OCP Database Administrator Show quote "Connie" wrote: > Dear all, > Thank you for reply. Sorry ..mistake in my example statement. > Actually...there is no problem in updating the different row..However...when > multiple users update the same row, then ...the 2nd trans ..will hang ...and > in the waiting status..until ..the 1st trans release.. > > Trans 1 : > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > Trans 2: > UPDATE YourTable WITH (ROWLOCK) > SET YourCol = 'Value' > WHERE ID = 1 > > > in this example, Is there any way ....to allow the 2nd trans ..to alert > ...the user..when the 1st...trans is updating the row.? > > Pls advise, thank you. > > Best Regards. > > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:eKD1XKwpGHA.4920@TK2MSFTNGP04.phx.gbl... > > Connie > > How about indexes ? Do you have any? > > In your case it dopes not seem the tran 2 is going to be blocked. There is > > no need to specify rowlock hint because SQL Server performs its by default > > > > > > > > > > > > > > > > "Connie" <yfc***@kdu.edu.my> wrote in message > > news:uPPCY7vpGHA.524@TK2MSFTNGP05.phx.gbl... > >> Dear Chris Lim, > >> > >> Thanks for reply. Yes, however in case let said ......for example, as > >> below.. > >> > >> Trans 1 : > >> UPDATE YourTable WITH (ROWLOCK) > >> SET YourCol = 'Value' > >> WHERE ID = 1 > >> > >> Trans 2: > >> UPDATE YourTable WITH (ROWLOCK) > >> SET YourCol = 'Value' > >> WHERE ID = 2 > >> > >> Whats happen is that the Trans 2 will get waiting ....& hanging there > >> until ..the Trans 1 release or commit... > >> My problem here is ..how could i get a message that ....when the trans 1 > >> row is being locked (updating), the trans 2 will get the message and > >> diplay the allert to the user. > >> Pls advise, thank you. > >> > >> Best Regards. > >> > >> "Chris Lim" <blackca***@hotmail.com> wrote in message > >> news:1152851163.679887.69180@b28g2000cwb.googlegroups.com... > >>> Connie wrote: > >>>> For example, there are 2 users connecting to the same table and want to > >>>> update the different rows. They are requested to use the row level > >>>> locking. > >>>> > >>> > >>> I'm only using SQL Server 2000, but I'm not aware of any differences in > >>> this area in SQL Server 2005 (someone correct me if I'm wrong). > >>> > >>> Anyway, row-level locking is the default granularity that SQL Server > >>> will use when locking rows. It will escalate them to page/table locks > >>> if necessary (I believe based on the amount of locking resources > >>> available). > >>> > >>> So you should rarely have to force the optimiser to use row-level > >>> locking, however if you have to then you can use locking hints. > >>> > >>> e.g. > >>> > >>> UPDATE YourTable WITH (ROWLOCK) > >>> SET YourCol = 'Value' > >>> WHERE ID = @ID > >>> > >>> > >>> If you are talking about explicitly locking a row BEFORE the user > >>> updates it, then you would need to use other locking hints, such as > >>> UPDLOCK or XLOCK. > >>> > >>> e.g. > >>> > >>> BEGIN TRANSACTION > >>> > >>> SELECT * > >>> FROM YourTable WITH (UPDLOCK) > >>> WHERE ID = @ID > >>> > >>> ....... > >>> > >>> COMMIT TRANSACTION > >>> > >>> > >>> Note that the lock is only held for the duration of the transaction. > >>> > >>> Chris > >>> > >> > >> > > > > > > > Dear Ben,
thanks for your reply. ..Can i said that .the solution for this as below. when ..1st user perform the batch processing ..& 2nd..user..want to update.....the record.......that being updated by the 1st user.. ..In my SQL statement..i need to add in the i .e ..the SET LOCK_TIMEOUT 1800....where when the 2nd trans ..is being locked...& after waiting ..for 1800 milliseconds .then in my program..i check for the SQL error...Msg 1222..only then i alert the user. manually, right? FYI, Currently .. in my application ..i already make use of Optimistic Condurrency Control (SQL 2005) ..where i uses snapshot isolation .level control (for read and update..row version)...but not update and update scenario... Pls advise...Thank you Show quote "Ben Nevarez" <bneva***@sjm.com> wrote in message news:8E9B1394-BFB7-4061-AB4F-AFE5FD131F9D@microsoft.com... > > If they update the same record then set the @@lock_timeout with enough time > for them to go for a cup of coffee :-) > > Ben Nevarez, MCDBA, OCP > Database Administrator > > > "Connie" wrote: > >> Dear all, >> Thank you for reply. Sorry ..mistake in my example statement. >> Actually...there is no problem in updating the different row..However...when >> multiple users update the same row, then ...the 2nd trans ..will hang ....and >> in the waiting status..until ..the 1st trans release.. >> >> Trans 1 : >> UPDATE YourTable WITH (ROWLOCK) >> SET YourCol = 'Value' >> WHERE ID = 1 >> >> Trans 2: >> UPDATE YourTable WITH (ROWLOCK) >> SET YourCol = 'Value' >> WHERE ID = 1 >> >> >> in this example, Is there any way ....to allow the 2nd trans ..to alert >> ...the user..when the 1st...trans is updating the row.? >> >> Pls advise, thank you. >> >> Best Regards. >> >> >> "Uri Dimant" <u***@iscar.co.il> wrote in message >> news:eKD1XKwpGHA.4920@TK2MSFTNGP04.phx.gbl... >> > Connie >> > How about indexes ? Do you have any? >> > In your case it dopes not seem the tran 2 is going to be blocked. There is >> > no need to specify rowlock hint because SQL Server performs its by default >> > >> > >> > >> > >> > >> > >> > >> > "Connie" <yfc***@kdu.edu.my> wrote in message >> > news:uPPCY7vpGHA.524@TK2MSFTNGP05.phx.gbl... >> >> Dear Chris Lim, >> >> >> >> Thanks for reply. Yes, however in case let said ......for example, as >> >> below.. >> >> >> >> Trans 1 : >> >> UPDATE YourTable WITH (ROWLOCK) >> >> SET YourCol = 'Value' >> >> WHERE ID = 1 >> >> >> >> Trans 2: >> >> UPDATE YourTable WITH (ROWLOCK) >> >> SET YourCol = 'Value' >> >> WHERE ID = 2 >> >> >> >> Whats happen is that the Trans 2 will get waiting ....& hanging there >> >> until ..the Trans 1 release or commit... >> >> My problem here is ..how could i get a message that ....when the trans 1 >> >> row is being locked (updating), the trans 2 will get the message and >> >> diplay the allert to the user. >> >> Pls advise, thank you. >> >> >> >> Best Regards. >> >> >> >> "Chris Lim" <blackca***@hotmail.com> wrote in message >> >> news:1152851163.679887.69180@b28g2000cwb.googlegroups.com... >> >>> Connie wrote: >> >>>> For example, there are 2 users connecting to the same table and want to >> >>>> update the different rows. They are requested to use the row level >> >>>> locking. >> >>>> >> >>> >> >>> I'm only using SQL Server 2000, but I'm not aware of any differences in >> >>> this area in SQL Server 2005 (someone correct me if I'm wrong). >> >>> >> >>> Anyway, row-level locking is the default granularity that SQL Server >> >>> will use when locking rows. It will escalate them to page/table locks >> >>> if necessary (I believe based on the amount of locking resources >> >>> available). >> >>> >> >>> So you should rarely have to force the optimiser to use row-level >> >>> locking, however if you have to then you can use locking hints. >> >>> >> >>> e.g. >> >>> >> >>> UPDATE YourTable WITH (ROWLOCK) >> >>> SET YourCol = 'Value' >> >>> WHERE ID = @ID >> >>> >> >>> >> >>> If you are talking about explicitly locking a row BEFORE the user >> >>> updates it, then you would need to use other locking hints, such as >> >>> UPDLOCK or XLOCK. >> >>> >> >>> e.g. >> >>> >> >>> BEGIN TRANSACTION >> >>> >> >>> SELECT * >> >>> FROM YourTable WITH (UPDLOCK) >> >>> WHERE ID = @ID >> >>> >> >>> ....... >> >>> >> >>> COMMIT TRANSACTION >> >>> >> >>> >> >>> Note that the lock is only held for the duration of the transaction. >> >>> >> >>> Chris >> >>> >> >> >> >> >> > >> > >> >> >> Just run the update statement against the row. Try something like this
begin tran update emp set name = 'Deep Purple' where id = 1 then run select * from sys.dm_tran_locks You could see a request_mode X, request_type LOCK, request_status GRANT. resource_type could be RID or KEY. Ben Nevarez, MCDBA, OCP Database Administrator Show quote "Connie" wrote: > Hi, > > For example, there are 2 users connecting to the same table and want to > update the different rows. They are requested to use the row level locking. > > Pls advise and provide the example of sql statement or sample code. > > Thank you > > > Dear Ben,
Dear all, Thank you for reply. Sorry ..mistake in my example statement. Actually...there is no problem in updating the different row..However...when multiple users update the same row, then ...the 2nd trans ..will hang ....and in the waiting status nonstop..even through after..the 1st trans released.. Trans 1 : UPDATE YourTable WITH (ROWLOCK) SET YourCol = 'Value' WHERE ID = 1 Trans 2: UPDATE YourTable WITH (ROWLOCK) SET YourCol = 'Value' WHERE ID = 1 1. in this example, Is there any way ....to allow the 2nd trans ..to alert ...the user..when the 1st...trans is updating the row.? 2. When the 2nd trans is hanging there..i run the statement ..as from your email. select resource_type, request_mode X, request_type LOCK, request_status from sys.dm_tran_locks the output is as below DATABASE S LOCK GRANT DATABASE S LOCK GRANT DATABASE S LOCK GRANT OBJECT IX LOCK GRANT OBJECT IX LOCK GRANT PAGE IX LOCK GRANT PAGE IX LOCK GRANT KEY X LOCK GRANT KEY X LOCK WAIT KEY X LOCK GRANT Pls advise, thank you. Best Regards. Show quote "Ben Nevarez" <bneva***@sjm.com> wrote in message news:F9AE9A42-FED6-4C4C-974A-60E88261BF63@microsoft.com... > > Just run the update statement against the row. Try something like this > > begin tran > update emp set name = 'Deep Purple' where id = 1 > > then run > select * from sys.dm_tran_locks > > You could see a request_mode X, request_type LOCK, request_status GRANT. > resource_type could be RID or KEY. > > Ben Nevarez, MCDBA, OCP > Database Administrator > > > "Connie" wrote: > >> Hi, >> >> For example, there are 2 users connecting to the same table and want to >> update the different rows. They are requested to use the row level >> locking. >> >> Pls advise and provide the example of sql statement or sample code. >> >> Thank you >> >> >>  
Other interesting topics
|
|||||||||||||||||||||||
