Update and locking
in [Programming SQL]
|
Prev: Concatenate with delimiter
Next: decimal problem
From: simon on 28 Apr 2010 07:24 Hi, i have many different users(50) at the same time, which create updates on the same table - each user on different rows. Is it possible that all updates are executed at the same time? Because, now, I guess, when first user create update, the all table is locked and others must wait. What setting should i put into my update statement, that it won't block other users to update their rows? What is the best scenario? Thank you, Simon
From: Uri Dimant on 28 Apr 2010 07:48 Simon If your UPDATE locks the entire table I think something is wrong with your logic. It is possible that many users update the same table at the same time however , you can be careful with blocking/locking as for update SQL Server aquires an exclusive lock meaning nobody cannot update till the the first processes finished the update. "simon" <zupan.net(a)gmail.com> wrote in message news:9b06fe43-b04e-46e1-8a06-65577aec769f(a)g11g2000yqe.googlegroups.com... > Hi, > > i have many different users(50) at the same time, which create updates > on the same table - each user on different rows. > Is it possible that all updates are executed at the same time? > Because, now, I guess, when first user create update, the all table is > locked and others must wait. > What setting should i put into my update statement, that it won't > block other users to update their rows? > What is the best scenario? > > Thank you, > Simon
From: simon on 28 Apr 2010 08:07 Hi Uri, how can I control which lock is executed at update statement? I would like that always is used row lock(RID) and newer table lock. thank you, Simon On 28 apr., 13:48, "Uri Dimant" <u...(a)iscar.co.il> wrote: > Simon > If your UPDATE locks the entire table I think something is wrong with your > logic. It is possible that many users update the same table at the same time > however , you can be careful with blocking/locking as for update SQL Server > aquires an exclusive lock meaning nobody cannot update till the the first > processes finished the update. > > "simon" <zupan....(a)gmail.com> wrote in message > > news:9b06fe43-b04e-46e1-8a06-65577aec769f(a)g11g2000yqe.googlegroups.com... > > > > > Hi, > > > i have many different users(50) at the same time, which create updates > > on the same table - each user on different rows. > > Is it possible that all updates are executed at the same time? > > Because, now, I guess, when first user create update, the all table is > > locked and others must wait. > > What setting should i put into my update statement, that it won't > > block other users to update their rows? > > What is the best scenario? > > > Thank you, > > Simon- Skrij navedeno besedilo - > > - Prika¾i citirano besedilo -
From: Uri Dimant on 28 Apr 2010 08:21 Kalen wrote the below script CREATE VIEW DBlocks AS SELECT request_session_id as spid, db_name(resource_database_id) as dbname, CASE WHEN resource_type = 'OBJECT' THEN object_name(resource_associated_entity_id) WHEN resource_associated_entity_id = 0 THEN 'n/a' ELSE object_name(p.object_id) END as entity_name, index_id, resource_type as resource, resource_description as description, request_mode as mode, request_status as status FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p ON p.hobt_id = t.resource_associated_entity_id WHERE resource_database_id = db_id(); "simon" <zupan.net(a)gmail.com> wrote in message news:9824ce49-ffa5-4b19-8273-c57f70f3eec9(a)z3g2000yqz.googlegroups.com... Hi Uri, how can I control which lock is executed at update statement? I would like that always is used row lock(RID) and newer table lock. thank you, Simon On 28 apr., 13:48, "Uri Dimant" <u...(a)iscar.co.il> wrote: > Simon > If your UPDATE locks the entire table I think something is wrong with your > logic. It is possible that many users update the same table at the same > time > however , you can be careful with blocking/locking as for update SQL > Server > aquires an exclusive lock meaning nobody cannot update till the the first > processes finished the update. > > "simon" <zupan....(a)gmail.com> wrote in message > > news:9b06fe43-b04e-46e1-8a06-65577aec769f(a)g11g2000yqe.googlegroups.com... > > > > > Hi, > > > i have many different users(50) at the same time, which create updates > > on the same table - each user on different rows. > > Is it possible that all updates are executed at the same time? > > Because, now, I guess, when first user create update, the all table is > > locked and others must wait. > > What setting should i put into my update statement, that it won't > > block other users to update their rows? > > What is the best scenario? > > > Thank you, > > Simon- Skrij navedeno besedilo - > > - Prika�i citirano besedilo -
From: Dan Guzman on 28 Apr 2010 08:42
> What setting should i put into my update statement, that it won't > block other users to update their rows? Specify the primary key in the UPDATE statement WHERE clause so that only the intended row is accessed. This will help ensure that only one row is touched by the UPDATE and improve concurrency. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "simon" <zupan.net(a)gmail.com> wrote in message news:9b06fe43-b04e-46e1-8a06-65577aec769f(a)g11g2000yqe.googlegroups.com... > Hi, > > i have many different users(50) at the same time, which create updates > on the same table - each user on different rows. > Is it possible that all updates are executed at the same time? > Because, now, I guess, when first user create update, the all table is > locked and others must wait. > What setting should i put into my update statement, that it won't > block other users to update their rows? > What is the best scenario? > > Thank you, > Simon |