Sql Server Programming :: How to force table lock error?


	How to force table lock error? Greetings All, I want to know if it is possible to construct a scenario using SQL such that: thread 1 selects from table1 with the tablockx hint thread 2 selects from table1 with the tablockx hint but instead of having thread 2 wait for thread 1 to commit or rollback I want thread 2 to throw an error stating that there is already a table lock? Thanks in advance. TFD LineVoltageHalogen (tropicalfruitdr@yahoo.com) writes: > Greetings All, I want to know if it is possible to construct a > scenario using SQL such that: > thread 1 selects from table1 with the tablockx hint > thread 2 selects from table1 with the tablockx hint > but instead of having thread 2 wait for thread 1 to commit or rollback > I want thread 2 to throw an error stating that there is already a > table lock? SET LOCK_TIMEOUT controls how long time a process waits for a resource to be available. Default is -1, which menas forever. 0 means give up directly. 1000 means one second; the time is milliseconds. Note that this apply to any sort of lock, not just table locks. -- Erland Sommarskog, SQL Server MVP, esq@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx -----------------------------------------------Reply----------------------------------------------- If you don't want to be blocked, look into SET LOCK_TIMEOUT. This will result in error 1222 if you have waited longer that specified. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "LineVoltageHalogen" <tropicalfruitdr@yahoo.com> wrote in message news:1173219028.570589.302020@p10g2000cwp.googlegroups.com... - Hide quoted text - > Greetings All, I want to know if it is possible to construct a > scenario using SQL such that: > thread 1 selects from table1 with the tablockx hint > thread 2 selects from table1 with the tablockx hint > but instead of having thread 2 wait for thread 1 to commit or rollback > I want thread 2 to throw an error stating that there is already a > table lock? > Thanks in advance. TFD On Mar 7, 2:47 am, Erland Sommarskog <esq@sommarskog.se> wrote: - Hide quoted text - > LineVoltageHalogen (tropicalfruitdr@yahoo.com) writes: > > Greetings All, I want to know if it is possible to construct a > > scenario using SQL such that: > > thread 1 selects from table1 with the tablockx hint > > thread 2 selects from table1 with the tablockx hint > > but instead of having thread 2 wait for thread 1 to commit or rollback > > I want thread 2 to throw an error stating that there is already a > > table lock? > SET LOCK_TIMEOUT controls how long time a process waits for a resource > to be available. Default is -1, which menas forever. 0 means give up > directly. 1000 means one second; the time is milliseconds. > Note that this apply to any sort of lock, not just table locks. > -- > Erland Sommarskog, SQL Server MVP, esq@sommarskog.se > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank you very much. This is exactly what I was looking for. TFD -----------------------------------------------Reply----------------------------------------------- On Mar 7, 2:47 am, "Tibor Karaszi" - Hide quoted text - <tibor_please.no.email_kara@hotmail.nomail.com> wrote: > If you don't want to be blocked, look into SET LOCK_TIMEOUT. This will result in error 1222 if you > have waited longer that specified. > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle... > "LineVoltageHalogen" <tropicalfruitdr@yahoo.com> wrote in message > news:1173219028.570589.302020@p10g2000cwp.googlegroups.com... > > Greetings All, I want to know if it is possible to construct a > > scenario using SQL such that: > > thread 1 selects from table1 with the tablockx hint > > thread 2 selects from table1 with the tablockx hint > > but instead of having thread 2 wait for thread 1 to commit or rollback > > I want thread 2 to throw an error stating that there is already a > > table lock? > > Thanks in advance. TFD- Hide quoted text - > Thank you also. TFD