TSQl advise

Question

TSQl advise

Vandana.verma

SSC Enthusiast

Points: 112
More actions
April 22, 2009 at 12:53 pm

#133406

When the user clicks on the button, the following should happen:
The system should check if any of the rows of a table that satisfy some condition ONLY are locked
If none are locked, all the rows that are related to that particular condition should be dropped and new rows should be inserted using a view,say.
If any are locked, the user should be given a message that they cannot update at this time and can try again in 1min – this locking should only take a couple of seconds anyway.
How do I do this in a stored procedure

Viewing 6 posts - 1 through 5 (of 5 total)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Vandana.verma (4/22/2009)
When the user clicks on the button, the following should happen:
The system should check if any of the rows of a table that satisfy some condition ONLY are locked
If none are locked, all the rows that are related to that particular condition should be dropped and new rows should be inserted using a view,say.
If any are locked, the user should be given a message that they cannot update at this time and can try again in 1min – this locking should only take a couple of seconds anyway.
How do I do this in a stored procedure

Well, I'm sure you didn't try to request us to write your complete solution...

So please specify where you have which problem.

Do you want to know how to handle custom locking?

Do you want to know how to update/delete data?

Do you want to know how to send error information to a client application?

Greets

Flo

Vandana.verma SSC Enthusiast Points: 112 More actions · Answer 2

Hi,

Thanks for the reply.

Actually I have a view that brings retired assets from an Asset Management Database server. In my .NET application, I am using this view to get the count of retired assets depending on the storagesite that a user selects on the main webpage. Since this view brings the retired assets from a different database server(Asset Management database server i.e. AMS), it takes too long to bring up the information on the page. So we are thinking of the following:

let’s create temporary tables on our database server that we will reference for all of our queries of Asset Management Database server – it will be much faster to do that .

This table will only be created once, but it will updated every time the user wishes .

The table should have row-locking enabled to ensure that one user cannot delete a row that someone else is working on .

In order to be able to effectively do this, we will need the following:

Disable the automatic lookup into AMS every time the webpage is loaded – it should only look at the local temporary table .

To update the temporary table with AMS data, the user will need to manually click on an ‘Update’ button from the webpage.

When the user clicks on the button, the following should happen:

The system should check if any of the rows of the temporary table that reference the PARTICULAR Secure Storage site ONLY are locked .

If none are locked, all the rows that are related to that particular site should be dropped and new rows from AMS should be inserted .

If any are locked, the user should be given a message that they cannot update at this time and can try again in 1min – this locking should only take a couple of seconds anyway.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

hi,

I got what you are trying to achieve. I am not sure if I can give any solution to the problem with your approach, but a small suggestion in case it helps:

We used have this kind of scenarios in our application and before bringing the fresh data ideally its safer to make the page (functionality or application) offline (which is going to be used for modification of the data that you are bringing) before bringing(refreshing the table) the data. By this you will not have any user actively working on it and safely you can refresh all the data.

Thanks.

---------------------------------------------------------------------------------

Vandana.verma SSC Enthusiast Points: 112 More actions · Answer 4

Hi Pakki,

Thanks for responding. I did not really clearly understand the concept. I would appreciate if you could elaborate it more and how to actually do it.

Thanks.

Vandana

Mohan Kumar VS SSCommitted Points: 1724 More actions · Answer 5

Vandana

Regarding:

The table should have row-locking enabled to ensure that one user cannot delete a row that someone else is working on .

You can use SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

which Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.

SERIALIZABLE

Specifies that:

1. Statements cannot read data that has been modified but not yet committed by other transactions.

2. No other transactions can modify data that has been read by the current transaction until the current transaction completes.

3. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Refer to BOL for more details.

Rgds

Mohan Kumar VS