I have multiple clients connected to a database.
These clients make queries, almost at the same time, causing me a headache, when 2 clients get the same result as a select.
How do I know when a table is busy or even lock this table until one client queries to release the other, I do not want to lose the sense of multiuser, as would that query.
Gustavo, there is a difference between multiuser and multitarefa . The symptom you report in the comments, " 2 clients get this same ID ", is probably due to #
You should review the schedule to prevent the same ID being provided to two or more different processes. This is a suggestion based on the "Automatic Handling of Sequence" item in Itzik Ben-Gan's Inside Microsoft SQL Server 2008: T-SQL Programming.
1) Create a table named Provide_ID, where the last ID value will be registered.
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, execute the following snippet:
DECLARE @ID as int;
UPDATE dbo.Fornece_ID set @ID = Valor = Valor +1;
The new ID value is given in the @ID variable. Note that a special variant of the UPDATE statement is used.
Depending on the application and the context, you can implement this transparently for the application, using trigger procedure. Detailed explanations of this method are available in the book indicated.
I believe that, for the above explanation, it is best that you use the newid() function of SQLServer. It will return you a unique hash, preventing two users from obtaining the same identifier. Example:
select newid() -- 77D704BE-B264-41F4-8C89-69886D5C60BE