| Groups | Blog | Home |
sql server programming : how to change the default table locking type ?
Is there anyway to change the default recode locking type (Table Locking) to row locking !! ? Thanks in help
Sniper
As far as I know SQL Server begins with a low type (row locking) and increase if it needs. [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... > Hi guys, > Is there anyway to change the default recode locking type (Table > Locking) to row locking !! ? > > Thanks in help > -Sniper
Using the sp_indexoption stored procedure you can play with the options:
AllowRowLocks, AllowPageLocks, DisAllowRowLocks, DisAllowPageLocks for each index. By default, SQL Server considers the locking granularity automatically, and the default choices it makes are usually best. -- BG, SQL Server MVP www.SolidQualityLearning.com [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... > Hi guys, > Is there anyway to change the default recode locking type (Table > Locking) to row locking !! ? > > Thanks in help > -Sniper
In addition to the other posts, supporting the query with good and usable indexes reduces the risk
of getting table locks. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... > Hi guys, > Is there anyway to change the default recode locking type (Table > Locking) to row locking !! ? > > Thanks in help > -Sniper
Hi Tibor,
Thanks for your reply. it's like this, When I do a start transaction and do a row modification, it locks the hole table, it there anyway to lock only the modified row ? Thanks alot for u r time and response, -Aruna [quoted text, click to view] "Tibor Karaszi" wrote:
> In addition to the other posts, supporting the query with good and usable indexes reduces the risk > of getting table locks. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Sniper" <kakopappa@hotmail.com> wrote in message > news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... > > Hi guys, > > Is there anyway to change the default recode locking type (Table > > Locking) to row locking !! ? > > > > Thanks in help > > -Sniper > >
Not directly. As I said, make sure that you support the WHERE clauses you use in the UPDATE with
proper indexes and you are most likely to end up with row locks. How did you determine that the whole table is locked? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@microsoft.com... > Hi Tibor, > > Thanks for your reply. it's like this, When I do a start transaction and do > a row modification, it locks the hole table, it there anyway to lock only the > modified row ? > > Thanks alot for u r time and response, > -Aruna > > > "Tibor Karaszi" wrote: > >> In addition to the other posts, supporting the query with good and usable indexes reduces the >> risk >> of getting table locks. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... >> > Hi guys, >> > Is there anyway to change the default recode locking type (Table >> > Locking) to row locking !! ? >> > >> > Thanks in help >> > -Sniper >> >> >>
Use sp_who, sp_who2, sp_lock etc to determine what types of locks the connection which has modified
the data has on the table. Also, you still haven't provided us with the table structure and what indexes you have on the table and what your UPDATE statement look like. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:5923EEE1-890F-454C-B694-7E9279D605F3@microsoft.com... > > When I open the table from the Enterprise Manager it doesn't show the table > data. or when I do a SELECT * FROM table it's waitting till the table lock to > be released. > > thanks, > Sniper > > "Tibor Karaszi" wrote: > >> Not directly. As I said, make sure that you support the WHERE clauses you use in the UPDATE with >> proper indexes and you are most likely to end up with row locks. How did you determine that the >> whole table is locked? >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@microsoft.com... >> > Hi Tibor, >> > >> > Thanks for your reply. it's like this, When I do a start transaction and do >> > a row modification, it locks the hole table, it there anyway to lock only the >> > modified row ? >> > >> > Thanks alot for u r time and response, >> > -Aruna >> > >> > >> > "Tibor Karaszi" wrote: >> > >> >> In addition to the other posts, supporting the query with good and usable indexes reduces the >> >> risk >> >> of getting table locks. >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://www.solidqualitylearning.com/ >> >> >> >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> >> news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... >> >> > Hi guys, >> >> > Is there anyway to change the default recode locking type (Table >> >> > Locking) to row locking !! ? >> >> > >> >> > Thanks in help >> >> > -Sniper >> >> >> >> >> >> >> >> >>
Also, if you have updated some row, and haven't committed that transaction, any process which tries
to read *all* rows will be blocked. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Sniper" <kakopappa@hotmail.com> wrote in message news:5923EEE1-890F-454C-B694-7E9279D605F3@microsoft.com... > > When I open the table from the Enterprise Manager it doesn't show the table > data. or when I do a SELECT * FROM table it's waitting till the table lock to > be released. > > thanks, > Sniper > > "Tibor Karaszi" wrote: > >> Not directly. As I said, make sure that you support the WHERE clauses you use in the UPDATE with >> proper indexes and you are most likely to end up with row locks. How did you determine that the >> whole table is locked? >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@microsoft.com... >> > Hi Tibor, >> > >> > Thanks for your reply. it's like this, When I do a start transaction and do >> > a row modification, it locks the hole table, it there anyway to lock only the >> > modified row ? >> > >> > Thanks alot for u r time and response, >> > -Aruna >> > >> > >> > "Tibor Karaszi" wrote: >> > >> >> In addition to the other posts, supporting the query with good and usable indexes reduces the >> >> risk >> >> of getting table locks. >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://www.solidqualitylearning.com/ >> >> >> >> >> >> "Sniper" <kakopappa@hotmail.com> wrote in message >> >> news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... >> >> > Hi guys, >> >> > Is there anyway to change the default recode locking type (Table >> >> > Locking) to row locking !! ? >> >> > >> >> > Thanks in help >> >> > -Sniper >> >> >> >> >> >> >> >> >>
When I open the table from the Enterprise Manager it doesn't show the table data. or when I do a SELECT * FROM table it's waitting till the table lock to be released. thanks, Sniper [quoted text, click to view] "Tibor Karaszi" wrote:
> Not directly. As I said, make sure that you support the WHERE clauses you use in the UPDATE with > proper indexes and you are most likely to end up with row locks. How did you determine that the > whole table is locked? > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Sniper" <kakopappa@hotmail.com> wrote in message > news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@microsoft.com... > > Hi Tibor, > > > > Thanks for your reply. it's like this, When I do a start transaction and do > > a row modification, it locks the hole table, it there anyway to lock only the > > modified row ? > > > > Thanks alot for u r time and response, > > -Aruna > > > > > > "Tibor Karaszi" wrote: > > > >> In addition to the other posts, supporting the query with good and usable indexes reduces the > >> risk > >> of getting table locks. > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://www.solidqualitylearning.com/ > >> > >> > >> "Sniper" <kakopappa@hotmail.com> wrote in message > >> news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@microsoft.com... > >> > Hi guys, > >> > Is there anyway to change the default recode locking type (Table > >> > Locking) to row locking !! ? > >> > > >> > Thanks in help > >> > -Sniper > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |