This link will explain different types of record locking in SQL. Locking an entire table on the server is usually not the best idea however, so you may want to explore optimistic locking.
Thanks for your reply but my question is Lock and unlock in sqlserver 2005. I researched this title and found that "When you update the table, it automatically creates a lock on that table, so no other user can update that table at the same time.
when you are updating table, user has to wait until you complete your updates."
Is it right in sql server 2005? please give me your idea about this title.
Yes its still same in 2005 and will remain the same in future versions too.. the default isolation level is Read Committed which causes this behaviour. If you want to change this behaviour try the other isolation levels. You can read data while updates are
being done. There is a new isolation level in SQL 2005 called Snapshot Isolation. Check that out too.
If you can explain what exactly you are trying to do then perhaps someone can offer better advise..
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
Thanks for your reply. In my project, when a lot of person insert into a table at the same time, i want at the same time, only person can insert into this table and when this person insert into this table successfully, next pserson can. I wrote store procedure:
Thiendung
0 Points
3 Posts
Lock and Unlock table in sql server 2005
May 29, 2009 05:12 PM|LINK
How can i lock and unlock table in sql server 2005?
steve.kraatz
Member
196 Points
33 Posts
Re: Lock and Unlock table in sql server 2005
May 29, 2009 06:00 PM|LINK
http://www.wwwcoder.com/parentid/191/tabid/68/type/art/site/6508/default.aspx
This link will explain different types of record locking in SQL. Locking an entire table on the server is usually not the best idea however, so you may want to explore optimistic locking.
Thiendung
0 Points
3 Posts
Re: Lock and Unlock table in sql server 2005
May 31, 2009 03:10 PM|LINK
Thanks for your reply but my question is Lock and unlock in sqlserver 2005. I researched this title and found that "When you update the table, it automatically creates a lock on that table, so no other user can update that table at the same time. when you are updating table, user has to wait until you complete your updates."
Is it right in sql server 2005? please give me your idea about this title.
ndinakar
All-Star
49092 Points
6868 Posts
Moderator
MVP
Re: Lock and Unlock table in sql server 2005
May 31, 2009 03:39 PM|LINK
Yes its still same in 2005 and will remain the same in future versions too.. the default isolation level is Read Committed which causes this behaviour. If you want to change this behaviour try the other isolation levels. You can read data while updates are being done. There is a new isolation level in SQL 2005 called Snapshot Isolation. Check that out too.
If you can explain what exactly you are trying to do then perhaps someone can offer better advise..
Dinakar Nethi
Life is short. Enjoy it.
***********************
Thiendung
0 Points
3 Posts
Re: Lock and Unlock table in sql server 2005
May 31, 2009 04:04 PM|LINK
Thanks for your reply. In my project, when a lot of person insert into a table at the same time, i want at the same time, only person can insert into this table and when this person insert into this table successfully, next pserson can. I wrote store procedure:
ALTER procedure TicketAdd(
@mamucdotk int, @matt int, @mabp int, @makh int, @chude nvarchar(255),
@noidung nvarchar(1000), @ngaydang bigint, @ngayhethan bigint,
@nguoitraloigannhat nvarchar(50),@ngaytraloiganhat bigint, @tenfile nvarchar(255),@loaifile nvarchar(255),
@kichthuocfile int,@tenfilethat nvarchar(255))
as
BEGIN TRANSACTION
insert into Ticket(Ma_MD_Ticket,Ma_TT_Ticket,MaBP,MaKH,ChuDe,NoiDung,NgayDang,NgayHetHan,
NguoiTraLoiGanNhat,NgayTraLoiGanNhat,TenFile,LoaiFile,KichThuocFile,TenFileThat)
values (@mamucdotk ,@matt,@mabp,@makh,@chude,@noidung,@ngaydang,@ngayhethan,
@nguoitraloigannhat,@ngaytraloiganhat,@tenfile,@loaifile,@kichthuocfile,@tenfilethat);
Select IDENT_CURRENT('Ticket')
COMMIT TRANSACTION;
Is it right? please help me about this title!
ixtli
Participant
1812 Points
318 Posts
Re: Lock and Unlock table in sql server 2005
Jun 03, 2009 09:26 AM|LINK
http://www.codeproject.com/KB/aspnet/Concurrency_Control.aspx
http://www.codeproject.com/KB/aspnet/concurrentupdates.aspx