Locking hint problem on multi-servers

[john smile]

Hi All,

I want to lock 2 tables on 2 servers using TABLOCKX
hint. These tables function as semaphores in my
application. It means when the tables are locked
then other users will not be able to access them
and automatically they can not continue their works.

I have tried using the following code, but it
does not work. I always got the error :
"Cannot specify an index or locking hint for
a remote data source" on the select command.

begin tran
select * from server1.accounting.dbo.semtab
with (tablockx) where fprefix = '000'

select * from server2.accounting.dbo.semtab
with (tablockx) where fprefix = '000'
-
-
commit tran

The error will disappear if I remove the server
name, like this :

select * from accounting.dbo.semtab
with (tablockx) where fprefix = '000'

But, it does not meet my requirement.
It only locks one table on one server(default).

Please help me

Thanks in advance

John S.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

john smile (nospam) writes:

I want to lock 2 tables on 2 servers using TABLOCKX
hint. These tables function as semaphores in my
application. It means when the tables are locked
then other users will not be able to access them
and automatically they can not continue their works.

Using tables as semaphores is not likely to be a good idea, even on
a single server. If you really need to do it, maybe you can make a
remote procedure all, and have the remote procedure to perform the
SELECT * FROM tbl (TABLOCKX) statement. Need to begin the transaction
on the calling server, though.

For a single-server lock, application locks are often a palatable way
to go. You get and realease them with sp_getapplock and sp_releasespplock.
An application lock is just a virtual resource. The good thing with
them, is that you don't interfere with SQL Server's own locking mechanism
on object, while you still rely on SQL Server to handle the locks.

I was about to write that this does not work for linked servers, but
I figured that I should try first, and lo and behold:

BEGIN TRANSACTION
EXEC IDW4.master.dbo.sp_getapplock 'Nisse', 'Exclusive'
COMMIT TRANSACTION

This worked.

However, I like to throw in the caveat, that there is nothing said about
linked-server support in Books Online, and when I tried against another
server I got an OLE DB error "New transaction cannot enlist in the specified
transaction coordinator".

There might be other alternatives as well, but not knowing anything
about your application, it's difficult to say for sure.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[john smile]

Erland, thank you very munch for your reply.

I will also use sp_getapplock and sp_releasespplock
as semaphores for certain processes on
one server (not for multi-servers).

Below is the function of my semaphore table in brief :

CREATE TABLE [dbo].[semtab] (
[fprefix] [char] (3) NOT NULL ,
[fdesc] [char] (40) NOT NULL ,
[fautokey] [char] (1) NOT NULL ,
[fccy] [bigint] NOT NULL ,
[fc1] [bigint] NOT NULL ,
[fc2] [bigint] NOT NULL ,
[fc3] [bigint] NOT NULL ,
-
etc
-
) ON [PRIMARY]

ALTER TABLE [dbo].[semtab] WITH NOCHECK ADD
CONSTRAINT [PK_semtab] PRIMARY KEY CLUSTERED
( [fprefix] ) ON [PRIMARY]

Column fprefix contains : '000'(semaphore for
exclusive process), 'inv' (semaphore for
invoice entry), 'so' (semaphore for order entry)
etc.

Column fc1 to fc14 contain counter. The last
auto transaction number of invoice, order, etc.

When users insert or update invoice data, first
my program read column fdesc on row '000'.
If the column is empty then it row-locks
on row 'inv'. If row-locking is successful,
one of the counter fc1 to fc14 will be updated and
then it updates other tables. If locking is not
successful, then the program waits for several
seconds. The row-locking here guarantees
the transaction number is unique and sequent.
It also avoids deadlock.

If a user run an exclusive process, like copying
data from server1 to server2, then the program will
exclusive-lock the whole table first, on two servers.
If successful, the row '000' is updated ( column fdesc
is filled with the process name, column fccy is
filled with counter that indicates the progress
of the process). Column fdesc on row '001' to '004'
are filled with the server name and dbase name.
The locking here guarantees the success of
the process. If the process runs 50% and fails,
the other processes on server1 and server2 will
not work. The failed process can be continued
by reading row '000' to '004'.
At the end of the process, the row '000' to '004'
are reseted and exclusive table locking is released.

Erland, if using tables as semaphores is not likely
to be a good idea, could you tell me the reasons ?
Do you have other alternatives for my problem ?

Regards,
John S.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

john smile (nospam) writes:

Erland, if using tables as semaphores is not likely
to be a good idea, could you tell me the reasons ?
Do you have other alternatives for my problem ?

The reason that I think it's not a good idea is that it hampers con-
currency. I think locking of tables should be left to SQL Server to
ascertain the integrity of the database.

From your description, I can make a few suggestions of alternatives,
although I like to stress that since I'm working from a brief overview,
it could all be terribly wrong.

If I understood things right, the problematic issue is locking the
tables for the maintenance jobs. Locking rows for a contigouous
sequence number during a transaction is a perfectly normal thing to do,
and I would not call that a semaphore. We have a requirement for
that in our application as well. We have one one-row one-column table
per series of numbers, though. (Leftover from 6.5 days.)

What you want in your case is to lock out any other processing while
you are running the maintenance job. You also want to leave a progress
indication.

You could use an application lock. The processes that needs a number,
could get a shared application lock for the table, and if they don't
get it within @@lock_timeout they go sleeping. (Or you have @@lock_timeout
to -1, so that they hang.) The maintenance job would get the application
lock with Exclusive mode. However, would the maintenance job die,
the other processes could come in - and that could be too early.

Then again, why bother locking at all? Isn't all that is needed that
maintenance process creates the '000' row, and when the other processes
sees this row, they don't proceed. Nothing has to remain locked. Yes,
this is a way of using a table row as a semaphore, but the sempaphore
is the data, not the lock.

Possibly you could get a problem when you add your '000' row that there
are other processes working and that must be permitted to complete. Hm,
is this the reason you want the tablock? You could add a dummy column
on the table, perform and UPDATE where you change the value of the
dummy column for all rows. This will be blocked until all processes
have completed. By using an UPDATE, you don't need any locking hint...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[john smile]

Erland, you are right. If my semaphore table is
combined with sp_getapplock, tablockx hint for
the table is not needed. It is better. Thanks.

But, this way does not solve my problem. I am still
not able to change the two tables for maintenance job,
because sp_getapplock can not be sent to two servers
at a time.

I have just tried other way :
a. For non-maintenance process (using one server),
I put holdlock or serializable hint on '000' row
plus row-lock on another related row.
b. For maintenance process, I sent update '000' row on
two servers (two tables).

But, unfortunately I found other problem in this trial.
Holdlock or serializable hint could not
stop update. I tested with the following :

Station 1:
begin tran
select * from semtab with (serializable)
where fprefix = '000'

Station 2:
begin tran
update semtab set fdesc = 'copy'
where fprefix = '000'
commit tran

Command on station 1 were executed before
the command on station 2. From many executions,
station 2 command often succeeded.
I only found twice that the hint worked properly.
I did not know why this happened.

Regards,
John S.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[john smile]

Erland, you are right. If my semaphore table is
combined with sp_getapplock, tablockx hint for
the table is not needed. It is better. Thanks.

But, this way does not solve my problem. I am still
not able to change the two tables for maintenance job,
because sp_getapplock can not be sent to two servers
at a time.

I have just tried other way :
a. For non-maintenance process (using one server),
I put holdlock or serializable hint on '000' row
plus row-lock on another related row.
b. For maintenance process, I sent update '000' row on
two servers (two tables).

But, unfortunately I found other problem in this trial.
Holdlock or serializable hint could not
stop update. I tested with the following :

Station 1:
begin tran
select * from semtab with (serializable)
where fprefix = '000'

Station 2:
begin tran
update semtab set fdesc = 'copy'
where fprefix = '000'
commit tran

Command on station 1 were executed before
the command on station 2. From many executions,
station 2 command almost always succeeded.
I only found twice that the hint worked properly.
I did not know why this happened.

Regards,
John S.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

john smile <nospam> writes:

Erland, you are right. If my semaphore table is
combined with sp_getapplock, tablockx hint for
the table is not needed. It is better. Thanks.

But, this way does not solve my problem. I am still
not able to change the two tables for maintenance job,
because sp_getapplock can not be sent to two servers
at a time.
(First: my reply is coming late, because my ISP had problems with their
newsserver, and your post has not yet turned up here, but I found it
on Google.)

I might not have not understood your setup completely. From what you
have described, I don't really see why you need the application lock
on two servers. On the user server, the user processes check the
application lock before getting the number. And on the maintenance
server, you get the application lock on the remote server. Don't
need a lock on the maintenance server as far as I can see. But I guess
that I have yet to hear the fully story of this.
But, unfortunately I found other problem in this trial.
Holdlock or serializable hint could not
stop update. I tested with the following :

Station 1:
begin tran
select * from semtab with (serializable)
where fprefix = '000'

Station 2:
begin tran
update semtab set fdesc = 'copy'
where fprefix = '000'
commit tran

Did you run this all on the server where semtab is?

Did the UPDATE actually change the value? SQL Server is smart enough
to realize that you don't need a lock if you update a column to its
existing value.

I had a few more suggestions in my previous post, and one was that
you should not use locks at all as semaphore, but simply data. If
the user process sees certain data in the '000' row, it just quits.
This sounds a lot simpler to me.
--
Erland Sommarskog, Stockholm, es****@sommarskog.se

[Erland Sommarskog]

[Repost, this time with the correct subject line.]

john smile <nospam> writes:

Erland, you are right. If my semaphore table is
combined with sp_getapplock, tablockx hint for
the table is not needed. It is better. Thanks.

But, this way does not solve my problem. I am still
not able to change the two tables for maintenance job,
because sp_getapplock can not be sent to two servers
at a time.
(First: my reply is coming late, because my ISP had problems with their
newsserver, and your post has not yet turned up here, but I found it
on Google.)

I might not have not understood your setup completely. From what you
have described, I don't really see why you need the application lock
on two servers. On the user server, the user processes check the
application lock before getting the number. And on the maintenance
server, you get the application lock on the remote server. Don't
need a lock on the maintenance server as far as I can see. But I guess
that I have yet to hear the fully story of this.
But, unfortunately I found other problem in this trial.
Holdlock or serializable hint could not
stop update. I tested with the following :

Station 1:
begin tran
select * from semtab with (serializable)
where fprefix = '000'

Station 2:
begin tran
update semtab set fdesc = 'copy'
where fprefix = '000'
commit tran

Did you run this all on the server where semtab is?

Did the UPDATE actually change the value? SQL Server is smart enough
to realize that you don't need a lock if you update a column to its
existing value.

I had a few more suggestions in my previous post, and one was that
you should not use locks at all as semaphore, but simply data. If
the user process sees certain data in the '000' row, it just quits.
This sounds a lot simpler to me.
--
Erland Sommarskog, Stockholm, es****@sommarskog.se

[john smile]

Erland,

Server-1 works as a user server and server-2 can
be either a backup server or a user server with
different users. Most maintenance processes deal
with large data. So, I break each process into
many batches / transactions. Before it starts,
all other processes must be complete.

From several alternatives, I think I should take
the way as I discribed in my previous post.
Below is the clear version :

a. For non-maintenance processes on server-1 and
non-maintenance processes on server-2,
1) Read '000' row from semtab (with holdlock) on
default server.
2) If read is successful, goto 3.
Otherwise exit (caused by update command
from a maintenance process).
3) If column fdesc contain '' then goto 4.
Otherwise exit (caused by an uncomplete
maintenance process).
4) Read a related row from semtab (with updlock)
on default server.
5) If read is successful, goto 6.
Otherwise exit (another user is running
the similar process).
6) Update counter on the row, etc.

b. For transfering data from one server to other
server:
1) Read '000' row from semtab on 2 servers.
2) If read is successful, goto 3.
Otherwise exit (caused by update
command from another maintenance process).
3) If column fdesc on 2 servers contain 'copy' or ''
then goto 4. Oherwise exit (caused by another
uncomplete maintenance process).
4) If column fdesc on 2 servers contain '' then
set column fdesc = 'copy' and fccy = 1.
If update (on 2 servers) is successful, goto 6.
Otherwise exit (caused by holdlock from
non-maintenance processes).
5) If column fdesc on 2 servers contain 'copy' then
set column fccy = fccy + 1 (on 2 servers, as
progress indicator).
If update is successful, goto 6. Otherwise exit
(the process is being executed by other user).
6) Copying from position indicated by column fccy. Etc

c. The other maintenance procedures are like transfering.
But, they only uses one server.

Erland, it would be greatly appriciated if you could
show me the weakness of the above procedures.
The procedures use locks and data.

Adding a dummy column on the table and peform update it
for all rows seems to be more expensive.

I am not sure the problem I told you (update vs
serial hint) was caused by SQL Server. From my last
test, I found serial hint could block update,
with or without changing the value like this :

Station 1:
begin tran
select * from semtab with (serializable)
where fprefix = '000'

Station 2:
begin tran
update semtab set fdesc = fdesc
where fprefix = '000'
commit tran

Regards,
John S.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

john smile (nospam) writes:

a. For non-maintenance processes on server-1 and
non-maintenance processes on server-2,
1) Read '000' row from semtab (with holdlock) on
default server.
2) If read is successful, goto 3.
Otherwise exit (caused by update command
from a maintenance process).
In several places, you talk about "if read is successful". Normally
a lock blocks you until the resource is available. But there are two
ways around this:

1) You can use SET LOCK_TIMEOUT to set a lock timeout. In this case,
you need to check @@error after the crucial statement, and if it's
the lock-timeout error, this is an anticipated situation. Note that
the client program also needs to be taught how to ignore the lock-
timeout error. A good thing with lock timeouts is that you can opt
to wait for, say, five seconds before you give up.

2) You can use the READPAST hint and then check @@rowcount. READPAST skips
blocked rows, so @@rowcount would be 0 in this case. The advantage
with this is that no error messages are generated. With READPAST there
is no option to make a short wait.

Then again, you mean something else with "if successful".

Erland, it would be greatly appriciated if you could
show me the weakness of the above procedures.
The procedures use locks and data.

I would prefer to not say anything conclusive at all. It is not that
I see any apparent blunders, but without knowing the tables, and not
seeing the code, there are too many unknowns for me.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp