Which lock should i use ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Which lock should i use ?

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-09 : 11:42:17

Hello,
Am running a billing system and using transaction locks to achieve this. I have been reading up on locking and transactions and confused which is best to use for locking a row. While...

1. REad
2. Updates
are carried out.

Thanks


create procedure [dbo].[Transactions]

as
declare @tran varchar(20)
select @tran = 'mytransaction'+getdate()

set transaction isolation level read committed;
go
begin transaction @tran;
go

--MY CODES GO HERE
commit transaction @tran;

Yes O !

georgev
Posting Yak Master

122 Posts

Posted - 2008-09-09 : 12:14:34

Not enough information to answer your question...

Unless it is as obvious as
>> I have been reading up on locking and transactions
>> which is best to use for locking a row

and then isn't the clue in the question?

George

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-09 : 13:06:05

I want to update a row, based on the user's username. And while the update is going on. I want to lock that row alone.

Not the entire table, but just the row. During the update, which of the locks is best to use ???

Its just a question.

Yes O !

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-09-09 : 13:22:20

The SQL engine will assign the lock that it thinks is the most appropriate based on efficiency and the current activity. You don't assign a lock and, by and large, you don't need to worry about it. You can influence the SQL engine by using a lock hint (e.g., ROWLOCK) but this does not force the lock.

If you are doing a single UPDATE statement, you don't need to worry about locks at all. The SQL engine treats that as an atomic event and so won't let anyone else change a value in the row while you are performing your update.

Does that answer your question?

=======================================
It is impossible to imagine Goethe or Beethoven being good at billiards or golf. -H.L. Mencken, writer, editor, and critic (1880-1956)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-09 : 13:23:31

Already wrote this when I saw Bustaz Kool's post - but I'll post anyway :)

The default transaction isolation level "read committed" is usually sufficient without using any explicit locking hints. Especially if you are using stored procedures where the update is virtually instantaneous (as opposed to holding the row locked from an application during user interaction). Are you experiencing any blocking problems?

Be One with the Optimizer
TG

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-09 : 14:31:22

thanks guys.

We have a high volume transaction db. And transactions are based on csv strings passed to the db. When this is done a split function checks for errors and makes a history.

REAL LIFE SENARIO
A user can make a transaction of 100,000 csvs and while this is being processed. ..

He can make another transaction of 1 or 2 and etc

Obviously, while the first transaction is being processed by the split function, it will be overtaken by the 2nd or 3rd.

What we worry about, is how do we make the billing "QUEUED"[/D]

So updates to the billing table will be consistent ???

Yes O ![b]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-09 : 15:12:00

I think it's called a ledger.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-09 : 15:14:12

I've got to go back to what georgev said - we don't have enough info to address these concerns.

We would need to know more about the architecture and requirements. If you need to insure that individual transactions are "processed" in a specific sequence then that is a different subject then isolation levels and locking/blocking. And we would need to know what specifically "processed" means. And specifcially how are you passing 100000 transactions to the database in a csv.

If you really need to queue transactions based on a submission date (or something) then you will need a either a processingQueue table or a messageQueueing system that is processed by a job or service so that all verified transactions happen in sequence and where a high volume of requests simply adds to the queue rather than clogging up your processing.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources