how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
>>Take for example ticketmaster where they lock a certain section, row and
>>even seat numbers for a duration..
>>I am assuming all this info may be in one table . How can one ensure row
>>level locking without SQL Server escalating it to some high level lock.
>>And even if a particular row is locked, does that mean that one can update
>>rows that are not locked
>>Trying to find a solution to do locking at a row level while still leaving
>>other rows for DML i.e select, updates,inserts,deletes
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Take for example ticketmaster where they lock a certain section, row and
>> even seat numbers for a duration..
>> I am assuming all this info may be in one table . How can one ensure row
>> level locking without SQL Server escalating it to some high level lock.
>> And even if a particular row is locked, does that mean that one can
>> update rows that are not locked
>> Trying to find a solution to do locking at a row level while still
>> leaving other rows for DML i.e select, updates,inserts,deletes
>> Thanks
>