|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ISOLATION!!!Hi All, I have a procedure that updates a row in multiple steps. First it
inserts a record and gets the @@Identity and then using this ID goes and updates that particular Row in several steps. How can I prevent any other application doing a SELECT on this table with this ID while update is in progress....? TIA "Vai2000" wrote: The short answer is that you can't prevent another process from reading the > Hi All, I have a procedure that updates a row in multiple steps. First it > inserts a record and gets the @@Identity and then using this ID goes and > updates that particular Row in several steps. > How can I prevent any other application doing a SELECT on this table with > this ID while update is in progress....? > > TIA > > data while you are modifying it if the application issues the SELECT with the NOLOCK hint. Using NOLOCK allows the application to read uncommitted data. If you can guarantee that no application will issue a SELECT with NOLOCK, I would recommed re-structuring your procedure so that it performs only one update, instead of successive updates based on the value of @@IDENTITY (btw, it would be better to use IDENT_CURRENT to retreive the latest identity value ) INSERT INTO [YourTable] ..... SET @currentid = IDENT_CURRENT('YourTable') BEGIN TRAN --use the value of @currentid to calculate the appropriate values for all of the columns in the table, and do a single update UPDATE [YourTable] SET col1 = .... COMMIT TRAN I can control the users to not use NOLOCK, though I can't make major change
in the SP unfortunately.... any more suggestions? Show quote "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message news:B85E7D75-9C65-4576-AA45-4990531CA8DA@microsoft.com... > "Vai2000" wrote: > > > Hi All, I have a procedure that updates a row in multiple steps. First it > > inserts a record and gets the @@Identity and then using this ID goes and > > updates that particular Row in several steps. > > How can I prevent any other application doing a SELECT on this table with > > this ID while update is in progress....? > > > > TIA > > > > > > The short answer is that you can't prevent another process from reading the > data while you are modifying it if the application issues the SELECT with the > NOLOCK hint. Using NOLOCK allows the application to read uncommitted data. > > If you can guarantee that no application will issue a SELECT with NOLOCK, I > would recommed re-structuring your procedure so that it performs only one > update, instead of successive updates based on the value of @@IDENTITY (btw, > it would be better to use IDENT_CURRENT to retreive the latest identity value > ) > > INSERT INTO [YourTable] ..... > > SET @currentid = IDENT_CURRENT('YourTable') > > BEGIN TRAN > --use the value of @currentid to calculate the appropriate values for all > of the columns in the table, and do a single update > UPDATE [YourTable] SET col1 = .... > COMMIT TRAN When writing a query, use judgement when deciding whether or not to modify
the default isolation level. If this this a transactional select (ex: what is the customer's current balance), then do not read uncommitted. If it's a reporting query (ex: what is the average price of a stock's price over a 1 hour period), then perhaps reading uncommitted data is acceptable and even necessary to prevent process blocking. However, this decision is up to the query writer and not the data writer. For example, a janitor can put up a sign that says "caution: wet floor!", but people can still choose to ignore it. It's ultimately thier responsibility. Show quote "Vai2000" <nospam@microsoft.com> wrote in message news:uq4vVBvPGHA.3984@TK2MSFTNGP14.phx.gbl... >I can control the users to not use NOLOCK, though I can't make major change > in the SP unfortunately.... > any more suggestions? > > > "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message > news:B85E7D75-9C65-4576-AA45-4990531CA8DA@microsoft.com... >> "Vai2000" wrote: >> >> > Hi All, I have a procedure that updates a row in multiple steps. First > it >> > inserts a record and gets the @@Identity and then using this ID goes >> > and >> > updates that particular Row in several steps. >> > How can I prevent any other application doing a SELECT on this table > with >> > this ID while update is in progress....? >> > >> > TIA >> > >> > >> >> The short answer is that you can't prevent another process from reading > the >> data while you are modifying it if the application issues the SELECT with > the >> NOLOCK hint. Using NOLOCK allows the application to read uncommitted >> data. >> >> If you can guarantee that no application will issue a SELECT with NOLOCK, > I >> would recommed re-structuring your procedure so that it performs only one >> update, instead of successive updates based on the value of @@IDENTITY > (btw, >> it would be better to use IDENT_CURRENT to retreive the latest identity > value >> ) >> >> INSERT INTO [YourTable] ..... >> >> SET @currentid = IDENT_CURRENT('YourTable') >> >> BEGIN TRAN >> --use the value of @currentid to calculate the appropriate values for > all >> of the columns in the table, and do a single update >> UPDATE [YourTable] SET col1 = .... >> COMMIT TRAN > > I HOPE YOU MEANT SCOPE_IDENTITY INSTEAD OF IDENT_CURRENT!!!!!?
Show quote "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message news:B85E7D75-9C65-4576-AA45-4990531CA8DA@microsoft.com... > "Vai2000" wrote: > >> Hi All, I have a procedure that updates a row in multiple steps. First it >> inserts a record and gets the @@Identity and then using this ID goes and >> updates that particular Row in several steps. >> How can I prevent any other application doing a SELECT on this table >> with >> this ID while update is in progress....? >> >> TIA >> >> > > The short answer is that you can't prevent another process from reading > the > data while you are modifying it if the application issues the SELECT with > the > NOLOCK hint. Using NOLOCK allows the application to read uncommitted data. > > If you can guarantee that no application will issue a SELECT with NOLOCK, > I > would recommed re-structuring your procedure so that it performs only one > update, instead of successive updates based on the value of @@IDENTITY > (btw, > it would be better to use IDENT_CURRENT to retreive the latest identity > value > ) > > INSERT INTO [YourTable] ..... > > SET @currentid = IDENT_CURRENT('YourTable') > > BEGIN TRAN > --use the value of @currentid to calculate the appropriate values for all > of the columns in the table, and do a single update > UPDATE [YourTable] SET col1 = .... > COMMIT TRAN Mark Williams wrote:
Show quote > "Vai2000" wrote: Not that BEGIN TRAN and COMMIT TRAN don't work as expected in stored> >> Hi All, I have a procedure that updates a row in multiple steps. >> First it inserts a record and gets the @@Identity and then using >> this ID goes and updates that particular Row in several steps. >> How can I prevent any other application doing a SELECT on this >> table with this ID while update is in progress....? >> >> TIA >> >> > > The short answer is that you can't prevent another process from > reading the data while you are modifying it if the application issues > the SELECT with the NOLOCK hint. Using NOLOCK allows the application > to read uncommitted data. > > If you can guarantee that no application will issue a SELECT with > NOLOCK, I would recommed re-structuring your procedure so that it > performs only one update, instead of successive updates based on the > value of @@IDENTITY (btw, it would be better to use IDENT_CURRENT to > retreive the latest identity value ) > > INSERT INTO [YourTable] ..... > > SET @currentid = IDENT_CURRENT('YourTable') > > BEGIN TRAN > --use the value of @currentid to calculate the appropriate values > for all of the columns in the table, and do a single update > UPDATE [YourTable] SET col1 = .... > COMMIT TRAN procedures. SQL Server does a kind of fake nested transactions: it counts BEGIN and COMMIT until the outermost nesting COMMIT is seen and only *then* actually commits. This behavior has caused us some troubles when invoking SP's via JDBC. Kind regards robert By default, your update statement performs row level locking, and you will
want to wrap the insert and updates in a transaction (begin tran.. end tran). By default, a select by another process will not see your uncommitted changes (read committed), but the query developer can choose to override this by specifying a (nolock) hint or setting a different isolation level (ex: read uncommitted), so it's really their problem and not yours. Show quote "Vai2000" <nospam@microsoft.com> wrote in message news:OLt$RhuPGHA.2108@TK2MSFTNGP10.phx.gbl... > Hi All, I have a procedure that updates a row in multiple steps. First it > inserts a record and gets the @@Identity and then using this ID goes and > updates that particular Row in several steps. > How can I prevent any other application doing a SELECT on this table with > this ID while update is in progress....? > > TIA > > Here's the short answer: Don't do that! Touch the database only when
absolutely necessary, and use set-based operations whenever possible. Here's a simple solution: if the table you're updating is named Widgits, then create a table variable called @Widgits that matches the structure of the table that you're updating. Change the target of the insert and the updates from Widgits to @Widgits throughout the procedure, then add a simple insert at the end to commit the changes. If that doesn't work because you're using UPDATE FROM, create a temp table called #Widgits and change Widgits to #Widgits. If the procedure inserts related rows, then you may have to create a couple table variables or temp tables. It can really kill scalability if you issue an insert and then several successive updates of the same row, especially if there are triggers. Show quote "Vai2000" <nospam@microsoft.com> wrote in message news:OLt$RhuPGHA.2108@TK2MSFTNGP10.phx.gbl... > Hi All, I have a procedure that updates a row in multiple steps. First it > inserts a record and gets the @@Identity and then using this ID goes and > updates that particular Row in several steps. > How can I prevent any other application doing a SELECT on this table with > this ID while update is in progress....? > > TIA > > Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. You seem to have missed some of the basics of SQL. You do not seem to know that rows are not anything like records. You have no relational keys, but prefer to write with proprietary code to mimic a sequential file. Finally you say "I have a procedure that updates a row in multiple steps" which says that you still think in procedural code and not in SQL, which declarative. If you will post your code instead of a narrative, you might be able to do this in one UPDATE statement and find a relatioanl key so you can have an RDBMS instead of a "fake file".  |
|||||||||||||||||||||||
Other interesting topics