| Author |
Topic |
|
rakesh_09
Starting Member
8 Posts |
 Posted - 2008-06-25 : 06:41:30
|
| Hi,Can someone tell how to lock a particluar table in SQL Server 2005? Also how do I unlock the table???? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 06:47:15
|
| Locks are automatically placed by server based on action you perform on it and also transaction isolation set. DO you mean denying permission on table for some users? |
 |
|
|
rakesh_09
Starting Member
8 Posts |
Posted - 2008-06-25 : 07:15:33
|
| yes... absolutely |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 07:48:42
|
quote:
Originally posted by rakesh_09
yes... absolutely
look for GRANT & DENY statement in sql server books online |
|
|
|
rakesh_09
Starting Member
8 Posts |
Posted - 2008-06-25 : 07:55:41
|
| how do i lock a table in EXCLUSIVE mode manually??? i mean by using sql statements |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 08:01:02
|
quote:
Originally posted by rakesh_09
how do i lock a table in EXCLUSIVE mode manually??? i mean by using sql statements
you can use XLOCK. see this:-http://www.tar.hu/sqlbible/sqlbible0057.html |
|
|
|
rakesh_09
Starting Member
8 Posts |
Posted - 2008-06-26 : 02:18:31
|
| i have two users user1 and user2...i execute the below sql statement using user1BEGIN TRANSACTIONselect * from user_tbl with (XLOCK)so will the user_tbl locked for user2 until user1 gives a COMMIT statement? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 02:47:50
|
quote:
Originally posted by rakesh_09
i have two users user1 and user2...i execute the below sql statement using user1BEGIN TRANSACTIONselect * from user_tbl with (XLOCK)so will the user_tbl locked for user2 until user1 gives a COMMIT statement?
yup. it will be locked until end of transaction. |
|
|
|
rakesh_09
Starting Member
8 Posts |
Posted - 2008-06-26 : 03:00:23
|
| thanks...but still the SELECT query works on the table... is there any lock that does not even allow the SELECT query to be executed??? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 03:28:07
|
quote:
Originally posted by rakesh_09
thanks...but still the SELECT query works on the table... is there any lock that does not even allow the SELECT query to be executed???
SELECT query? which select query? you mean for second user? |
|
|
|
rakesh_09
Starting Member
8 Posts |
Posted - 2008-06-26 : 04:03:19
|
| yes..... if the second user is able to execute a SELECT query on the lock table..... is there any type of lock which does not even allow to execute the SELECT query? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 04:13:22
|
quote:
Originally posted by rakesh_09
yes..... if the second user is able to execute a SELECT query on the lock table..... is there any type of lock which does not even allow to execute the SELECT query?
you mean second user is able to view select query results while first user hasnt comited/rollbacked his transaction? |
|
|
|
rakesh_09
Starting Member
8 Posts |
Posted - 2008-06-26 : 04:19:00
|
| yes... first user has locked the table and has not committed the transaction... but the second user can see the select query results.... is there any lock that an be applied on the table so that the second user is no able to execute the select query also??? |
|
|
|
|
Table Lock