Locks

Question

Locks

amalanto

SSC Eights!

Points: 840
More actions
January 11, 2010 at 8:24 am

#144082

I Require to implement lock(Nolock,Read committed,Repeatable Read,serializable) into my production to improve my queries at production.Can anyone explain which one is opt for which scenario?
please.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

changing lock styles has a very minimal impact on queries, especially in SQL2005 and above.

For example, unless you have a LOT of updates going on every x milliseconds, SELECT statements would not be slowed down waiting for an update to occur, and would not be impacted at all by any of the changes you propose.

I'm more than sure that your performance problems are more related to slower execution plans for your queries instead.

The places to look are Indexes and SARG-able arguments in your WHERE statements first. If you can find an example of a slow running query, you could post the query here, and a copy of the execution plan as a .sqlplan xml document.

If you can do that, many of us here can help you change your queries or indexes to greatly (as in orders of magnitude) faster and more efficient.

once you grasp this concept, I believe the ability to analyze a SQL statement and make it more efficient by making change like i identified above is what changes a developer into a true DBA.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I would suggest you optimise your queries before starting to fiddle with locking and isolation levels.

Try these articles for starters

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

And, before you consider changing isolation levels, do some reading as to what the various ones do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amalanto SSC Eights! Points: 840 More actions · Answer 3

Soory for the unclear message of mine .Actually a common queue maintained about customer data displayed in .net(Grid control) environment ,will be accessed by around 50 users .In that scenario, i need to implement lock to control multiple update for single row.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You're gonna have to give us more details than that to work with. Table structure would be useful, as would some pseudo code, details on exactly what's happening.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amalanto SSC Eights! Points: 840 More actions · Answer 5

Thanks a lot. Here i have provided some sample data of my production .Below customer data will be displayed to all users in their front end application(.net grid).Now one user will select one row and tried update which option available on their screen .It will pass one parameter called as TransactionID to stored procedure to fetch data of that respective row details and then take them into new screen to update comments.Information will be saved Once user click save button .

Here some times same row will be accessed by many users.Unknowingly many users select same row and update.How do we restrict this scenario.

Create Table [dbo].TransactionTest

( [id] [int] IDENTITY(1,1) NOT NULL,

[TransactionID] [int] NOT NULL,

[Status] varchar(5) NULL,

[EmployeeID] varchar(10) NULL,

[CREATEDDATE] DATETIME NULL,

[Comments] varchar(100) NULL

)

GO

INSERT INTO TransactionTest ( [TransactionID], [Status], [EmployeeID], [CREATEDDATE],[Comments])

SELECT '10811454','Open','',01/12/2010,'' UNION ALL

SELECT '10811453','Open','',01/12/2010,'' UNION ALL

SELECT '10811452','Open','',01/12/2010,'' UNION ALL

SELECT '10811451','Open','',01/12/2010,'' UNION ALL

SELECT '10811450','Open','',01/12/2010,'' UNION ALL

SELECT '10811449','Open','',01/12/2010,'' UNION ALL

SELECT '10811448','Open','',01/12/2010,'' UNION ALL

SELECT '10811447','Open','',01/12/2010,'' UNION ALL

SELECT '10811446','Open','',01/12/2010,'' UNION ALL

SELECT '10811445','Open','',01/12/2010,''

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Transactions held open over multiple screens are usually a bad idea. What happens if the user gets to the comment screen then goes for lunch, leaving the rows locked, and comes back an hour later. You'll have other users screaming blue murder.

Two options that I can suggest for you, neither easy, both need app changes.

1) Manual 'locking'

Add another column to the table. 'EditingBy' or similar

When the user selects the row, the EditingBy is set to that users' username, machine name or something else. No one is allowed to start editing a row that's marked as being edited by someone else.

Downside to this is that the 'edited by' must be cleared when the user finished, even if they don't save the changes, even if they just close the app, even if a power failure shuts their machine down.

This one is not easy to get right, but it is easy to get a half-working version that works for a while and causes lots of problems.

2) Optimistic concurrency.

When the user starts editing the row, the app saves the state of the row in memory. When it goes to save it checks that the values in the row are the ones that it remembers. If not, it means that someone else has modified the row in the meantime. If that happens, an error must be thrown and the current user's changes discarded.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amalanto SSC Eights! Points: 840 More actions · Answer 7

Thanks you sir.

Yes ,Right now we have followed the second one (Optimistic concurrency) by checking employeeid column i.e if it is length greater than 1 then we say through alert window that somebody editing this row .so plese try after soemtime. The thing is we are providing message after clcik save button.This is not accepted by users and says that production time is affected due to post message.

Another thing ,I need to learn the lock mechanism. so kindly send me some sample about locks available in Sql server 2005 if possible.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

What you're currently doing is the best way. Locking records while users type stuff in is a very bad idea. Leads to massive locking and very poor performance.

If a user starts to edit a row, goes off to lunch (or home) and doesn't finish for a few hours, then nobody else can touch (read or update) that row for those hours. What you want to do is strongly not recommended.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amalanto SSC Eights! Points: 840 More actions · Answer 9

Once again thanks a lot.

Can you provide some sample for lock in sql server 2005.please...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Can you be more specific? Sample for what regarding locking?

All SQL queries take locks without you having to do anything. In the default isolation level shared locks are released as soon as they are not needed, exclusive locks are held to the end of the transaction.

May I suggest reading through Books Online for the details on the different isolation levels and how they affect the length and type of locks taken?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 11

another idea is to add a column of type rowversion and use that as a semaphore for whether the person should update;

so for example:

data example:

create table myTest(testid int identity(1,1) not null primary key,

testtext varchar(30),

UpdateVer rowversion)

insert into myTest(testtext)

select 'one' union all select 'two' union all select 'three'

testid testtext UpdateVer

1 one 0x00000000000007D1

2 two 0x00000000000007D2

3 three 0x00000000000007D3

now, if someone else updates any of my three rows, the row version will change, so my personal update statement in the app would be something like:

UPDATE myTest SET [testtext] = 'Two' WHERE testid = 2 and UpdateVer = '0x00000000000007D2'

if someone were to change the data, no rows would be updated, since the rowversion changed; probably not what you wanted exactly, so i'd lean more towards using the rowversino to detect changes ;

so you can query the rowversion just prior to updating, and if it is different, present a screen that says the data has been changed since you last loaded it; do you want to overwrite/reload. that would involve modifying your application, of course, which I'm not sure wether that is something you are after.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!