Returning only one select at a time multiuser

Asked

Viewed 55 times

1

I have several clients connected to a bunch of data.

These customers make inquiries, almost at the same time, causing me headache, when 2 customers catch the same result of a select.

How do I know when a table is busy or even lock this table until a customer refers to release to another, I do not want to lose the sense of multiuser, as would be this query.

database sql-server select

  • as well, what problem you have of two client consult the table at the same time?

  • 1

    Hello @Gustavoarantes! Welcome to Sopt! I kindly ask you to read the items How to create a Minimum, Complete and Verifiable example and Help Center so that we can help you! Thanks in advance! Greetings,

  • Goku I’m sorry I expressed myself badly, I forgot to mention that each query returns an ID number, and for each ID returns on each client, an edition is made on that data, what happens, and that 2 clients take that same ID and make the change in the table.

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

2 answers

3

Gustavo, there’s a difference between multi-user and multitasking. The symptom you report in the comments, "2 customers get the same ID", probably due to the competition of proceedings of a multi-tasking system.

You should review the schedule to prevent the same ID from being provided to two or more different processes. Here is a suggestion based on the "Automatic Handling of Sequence" item from the book "Inside Microsoft SQL Server 2008: T-SQL Programming" by Itzik Ben-Gan.

1) Create a table named Supplier, where the last ID value will be recorded.

IF OBJECT_ID('dbo.Fornece_ID', 'U') is not null
  DROP TABLE dbo.Fornece_ID;
GO
CREATE TABLE dbo.Fornece_ID (Valor int not null);
INSERT INTO dbo.Fornece_ID values (0);

2) Whenever you need to get a new ID value, run the following snippet:

DECLARE @ID as int;
UPDATE dbo.Fornece_ID set @ID = Valor = Valor +1;

The new ID value is provided in the @ID variable. Note that a special variant of the UPDATE statement is used.

Depending on the application and context, you can implement this transparently for the application, through procedure Trigger. Detailed explanations of this method are available in the book indicated.

1

I believe that, for the above explanation, the ideal is that you use the function newid() Sqlserver. It will return you a unique hash, preventing two users from getting the same identifier. Example:

select newid() -- 77D704BE-B264-41F4-8C89-69886D5C60BE

Browser other questions tagged database sql-server select

You are not signed in. Login or sign up in order to post.