How lock few tables at ones?

In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure