May 9, 2018 at 9:49 pm
patrickmcginnis59 10839 - Wednesday, May 9, 2018 6:20 PMWell I like to code front ends and I don't mind saying that I've used sql successfully, I like to do the optimistic concurrency thing, with a well indexed table the lock lasts just as long as needed for a single row update, thats pretty much all I need for like a data entry or update form. After that, its just programming as usual. I get all the benefits, such as parameterized queries that don't bloat the proc cache. Theres no need for lock flags if you use optimistic concurrency, if the "rows updated" says no update has happened, you know someone else has updated the same row before you did and you code for that eventuality. No flag needed.
I can see I described your own db programming practices very well. 🙂
Can you explain why do you prefer to use NOLOCK if "the lock lasts just as long as needed for a single row update"?
_____________
Code for TallyGenerator
May 9, 2018 at 10:15 pm
Steve Thompson-454462 - Wednesday, May 9, 2018 8:18 AM+x
Orlando Colamatteo - Tuesday, May 8, 2018 9:10 PM+x
Steve Thompson-454462 - Tuesday, May 8, 2018 10:23 AM
The definition of "working" is governed by the team's Definition of Done (which applies to all stories in the project) and the Acceptance Criteria which is tailored for each story.
So the who = a combination of the team and the product owner (who proxies for the "customer"). The what encompasses everything from specifying the appropriate amount of test unit coverage, passing regression and integration tests, code reviews and user acceptance testing, as well as leaving room for any other quality check the team feels adds value.Steve, you've mixed a Scrum thing and an Agile practice into your response to a question about the Agile Manifesto and I want to separate them out for clarity in case you have a misunderstanding and also so Sergiy and other readers are not misled.1. Working software over comprehensive documentation is an Agile value. This is nothing to do with processes or artifacts. It simply says we value the item on the left more than on the right while at the same time implying that we still value the item on the right, just less so. The definition of working is left to the implementation however at the end of the day we're judged by what we deliver to our customers, hence my earlier response.
2. The Definition of Done (DoD) is a part of the Scrum framework and is not part of the Agile Manifesto. This is where the left to the implementation kicks in. Scrum is an Agile framework and it helps put some structure around what is considered working, although the Scrum Guide is silent on the definition of working so citing the DoD as the definition of working would be team specific. The Definition of Done defines Doneness, nothing more. Have you ever seen developers that have developed something that satisfies all written requirements yet the end user opens a ticket that says "xyz doesn't work?"
3. User Stories are not part of Scrum nor the Agile Manifesto. They are an Agile practice, some even describe it as a mindset or a behavior, that was designed to help us overcome two major challenges: 1. written requirements documents don't work 2. there is too much to build. Item 1 we've covered. Item 2 gets into prioritization, sizing and business value propositions which addresses a huge problem with big up-front analysis and exhaustive comprehensive requirements documentation, lots of waste.All good points, Orlando. Mainly I was trying to address the implication that adapting an Agile framework results in having no real concept of what "working" means. The Manifesto is driving home that delivering potentially releasable functionality is a better value proposition than delivering detailed documentation but no implementation (such as might happen in the early phases of a Waterfall project). So, my post was defending the fact that Agile teams do have mechanisms by which "working", i.e. potentially releasable, is determined.
But, as you point out, Agile is more than just Scrum, and the Manifesto is separate from the practices it engenders. Also, the self-organizing aspect of the framework often leads to teams doing "Scrum, but...". On teams I've worked with, DoDs are often developed for each stage of the CI pipeline, so that quality checks, such as passing code review before a merge request is completed, are made explicit. May not be canonical but it does seem to bring value.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 9, 2018 at 10:35 pm
Sergiy - Wednesday, May 9, 2018 9:49 PMpatrickmcginnis59 10839 - Wednesday, May 9, 2018 6:20 PMWell I like to code front ends and I don't mind saying that I've used sql successfully, I like to do the optimistic concurrency thing, with a well indexed table the lock lasts just as long as needed for a single row update, thats pretty much all I need for like a data entry or update form. After that, its just programming as usual. I get all the benefits, such as parameterized queries that don't bloat the proc cache. Theres no need for lock flags if you use optimistic concurrency, if the "rows updated" says no update has happened, you know someone else has updated the same row before you did and you code for that eventuality. No flag needed.
I can see I described your own db programming practices very well. 🙂
Can you explain why do you prefer to use NOLOCK if "the lock lasts just as long as needed for a single row update"?
first you'll have to describe where nolock is used, I don't remember saying I used it. To the best of my knowledge, when using nolock, you have to use the hint "with (nolock)" in your query text which I didn't mention anywhere in my post.
edit: I think I do understand why you assumed I did, because I mentioned the "query hint". Its not nolock that I was specifying, its the appropriate query hint for for your query, and (nolock) is not the appropriate query hint to assure reliable concurrency. For any queries that need it, you either specify "isolation mode seralizable" or use the appropriate hint that pretty much causes the same or similar actions. For updating, you only have to specify the field values that must exist for your query to match the read prior to the update.
edit number 2: Actually I don't think I mentioned a query hint at all, but I think for subqueries you either use the updatelock and tablockx (have to check my notes) or just go with serializable. For updating a row, no hint is needed, SQL takes the lock needed, heck for that matter, I don't think (nolock) will be applied with an update anyways. Theres no need to mark a row as being updated, but you do need to code your screen by checking to see if your update succeeded.
edit 3: this page has plenty of good reading, so I don't need to clutter up the thread:
https://en.wikipedia.org/wiki/Concurrency_control
edit 4: I called the semaphore a "lock flag", so thats why I think my reply was misunderstood. I meant to say that the semaphore wasn't needed but I did call it a lock flag, sorry for the confusion thats on me.
C'mon now, lets not assume nolock anywhere, it makes us look bad, LOL
May 9, 2018 at 11:07 pm
I did not assume anything.
You said "I like to do the optimistic concurrency thing", which is the same as specifying NOLOCK hint for a query.
_____________
Code for TallyGenerator
May 9, 2018 at 11:15 pm
Sergiy - Wednesday, May 9, 2018 11:07 PMI did not assume anything.You said "I like to do the optimistic concurrency thing", which is the same as specifying NOLOCK hint for a query.
The importance of understanding what nolock actually is is important enough that you should research it yourself. I don't recommend using it, and its not the same thing as optimistic concurrency but I can't keep typing that over and over, it would become irritating 😉
edit: seriously, I can't keep posting about nolock when its use isn't recommended if you need accuracy but if you insist its the same thing as optimistic concurrency then I'm guessing that no amount of insisting otherwise on my part is going to help anybody, it just clutters the thread, this is the last post by me on the subject of nolock and concurrency control.
May 10, 2018 at 1:21 am
patrickmcginnis59 10839 - Wednesday, May 9, 2018 11:15 PMSergiy - Wednesday, May 9, 2018 11:07 PMI did not assume anything.You said "I like to do the optimistic concurrency thing", which is the same as specifying NOLOCK hint for a query.The importance of understanding what nolock actually is is important enough that you should research it yourself. I don't recommend using it, and its not the same thing as optimistic concurrency but I can't keep typing that over and over, it would become irritating 😉
edit: seriously, I can't keep posting about nolock when its use isn't recommended if you need accuracy but if you insist its the same thing as optimistic concurrency then I'm guessing that no amount of insisting otherwise on my part is going to help anybody, it just clutters the thread, this is the last post by me on the subject of nolock and concurrency control.
I'm not sure Wikipedia it the right source of knowledge about concurrency in SQL Server.
This should b ecloser to th etruth:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/optimistic-concurrency
users who use optimistic concurrency do not lock a row when reading it
_____________
Code for TallyGenerator
May 10, 2018 at 5:13 am
Sergiy - Thursday, May 10, 2018 1:21 AMpatrickmcginnis59 10839 - Wednesday, May 9, 2018 11:15 PMSergiy - Wednesday, May 9, 2018 11:07 PMI did not assume anything.You said "I like to do the optimistic concurrency thing", which is the same as specifying NOLOCK hint for a query.The importance of understanding what nolock actually is is important enough that you should research it yourself. I don't recommend using it, and its not the same thing as optimistic concurrency but I can't keep typing that over and over, it would become irritating 😉
edit: seriously, I can't keep posting about nolock when its use isn't recommended if you need accuracy but if you insist its the same thing as optimistic concurrency then I'm guessing that no amount of insisting otherwise on my part is going to help anybody, it just clutters the thread, this is the last post by me on the subject of nolock and concurrency control.
I'm not sure Wikipedia it the right source of knowledge about concurrency in SQL Server.
This should b ecloser to th etruth:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/optimistic-concurrencyusers who use optimistic concurrency do not lock a row when reading it
Yep, thats a good page, and pretty much describes what I was talking about. Specifically they talk about only updating the row if the row still contains the old value that was present when the row was read. The wikipedia page also talks about another method, that each write updates a "version" of sorts and this is compared to the value that was present when the original record was read. I think it boils down to letting other updates occur before attempting your own update, but making sure your update will fail if the data has changed from what it was when you first read it.
But I guess i have to break my promise about nolock but hopefully to clear up any confusion about this at least in my mind if not anybody elses. While the code does not apply any locks during the read portion, read uncommitted is more about whether the read can disregard OTHER locks, just wanted to mention that little tidbit about whether this is equivalent to specifying the NOLOCK hint. Read uncommitted isolation mode is sometimes referred to as allowing "dirty reads" and the the code that the page contains doesn't do that.
May 10, 2018 at 6:01 am
patrickmcginnis59 10839 - Thursday, May 10, 2018 5:13 AMread uncommitted is more about whether the read can disregard OTHER locks, just wanted to mention that little tidbit about whether this is equivalent to specifying the NOLOCK hint. Read uncommitted isolation mode is sometimes referred to as allowing "dirty reads" and the the code that the page contains doesn't do that.
As they say, RTFM:
READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed.
... This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction.
_____________
Code for TallyGenerator
May 10, 2018 at 6:07 am
patrickmcginnis59 10839 - Thursday, May 10, 2018 5:13 AMThe wikipedia page also talks about another method, that each write updates a "version" of sorts and this is compared to the value that was present when the original record was read. I think it boils down to letting other updates occur before attempting your own update, but making sure your update will fail if the data has changed from what it was when you first read it.
It's named Snapshot Isolation in T-SQL.
Are you aware of all the overheads attached to this type of isolation?
_____________
Code for TallyGenerator
May 10, 2018 at 6:36 am
Sergiy - Thursday, May 10, 2018 6:01 AMpatrickmcginnis59 10839 - Thursday, May 10, 2018 5:13 AMread uncommitted is more about whether the read can disregard OTHER locks, just wanted to mention that little tidbit about whether this is equivalent to specifying the NOLOCK hint. Read uncommitted isolation mode is sometimes referred to as allowing "dirty reads" and the the code that the page contains doesn't do that.As they say, RTFM:
READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed.
... This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction.
hey, I gotta give you kudos for persistence!
May 10, 2018 at 6:47 am
Sergiy - Thursday, May 10, 2018 6:07 AMpatrickmcginnis59 10839 - Thursday, May 10, 2018 5:13 AMThe wikipedia page also talks about another method, that each write updates a "version" of sorts and this is compared to the value that was present when the original record was read. I think it boils down to letting other updates occur before attempting your own update, but making sure your update will fail if the data has changed from what it was when you first read it.It's named Snapshot Isolation in T-SQL.
Are you aware of all the overheads attached to this type of isolation?
Yes, can't argue that. If the client does a "version number" then you can probably do the same thing in read committed, just depends if you want to do it or let the server do it.
May 10, 2018 at 7:17 am
patrickmcginnis59 10839 - Thursday, May 10, 2018 6:47 AMSergiy - Thursday, May 10, 2018 6:07 AMpatrickmcginnis59 10839 - Thursday, May 10, 2018 5:13 AMThe wikipedia page also talks about another method, that each write updates a "version" of sorts and this is compared to the value that was present when the original record was read. I think it boils down to letting other updates occur before attempting your own update, but making sure your update will fail if the data has changed from what it was when you first read it.It's named Snapshot Isolation in T-SQL.
Are you aware of all the overheads attached to this type of isolation?Yes, can't argue that. If the client does a "version number" then you can probably do the same thing in read committed, just depends if you want to do it or let the server do it.
You don't need a client to that.
There is rowversion (timestamp) data type in SQL Server, designed specifically for the purpose.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017
_____________
Code for TallyGenerator
Viewing 12 posts - 181 through 191 (of 191 total)
You must be logged in to reply to this topic. Login to reply