473,545 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Locking hint problem on multi-servers

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.account ing.dbo.semtab
with (tablockx) where fprefix = '000'

select * from server2.account ing.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!
Jul 20 '05 #1
9 5057
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_releasespplo ck.
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland, thank you very munch for your reply.

I will also use sp_getapplock and sp_releasespplo ck
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!
Jul 20 '05 #3
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

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!
Jul 20 '05 #5
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!
Jul 20 '05 #6
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****@sommarsk og.se

Jul 20 '05 #7
[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****@sommarsk og.se

Jul 20 '05 #8
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!
Jul 20 '05 #9
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
4503
by: Michael Chermside | last post by:
Ype writes: > For the namespaces in Jython this 'Python internal thread safety' > is handled by the Java class: > > http://www.jython.org/docs/javadoc/org/python/core/PyStringMap.html > > which has almost all of it public methods Java synchronized: > > http://cvs.sourceforge.net/viewcvs.py/jython/jython/org/python/core/PyStringMap.
0
1497
by: Dave C. | last post by:
Hi there, We have some code that empties a table. Originally, the code used "delete from", but I am told that this method was either too slow, or held a lock for too long (this was a long time ago and I wasn't involved at that point, so I'm not 100% sure exactly the nature of this problem). So the code was changed to "truncate table". Now...
1
5440
by: Arska | last post by:
Hi Is it possible to force row level locking in one or more tables in some database. We have some problems when SQL Server decides to choose page- or table-level locking. We are using SQL Server 2000. Best regards Aarno
3
5115
by: Ryan | last post by:
I have a problem with record locking / blocking within an application. The app is quite straight forward. Written in Delphi 5 using BDE to access a SQL 7 database (Win2K server). Every so often the users (when they bother to tell me) find that the application locks up and they are unable to work. No errors are produced (error trapping in...
2
1334
by: spammy | last post by:
hi all, im trying to establish whether i have a race condition or critical section in the following. i have a dataaccess class that continually retireves a table from a sqlserver (which may be slow). it provides a cached copy of this table to clients, for them to read from (note: there are no writes). the idea being if a particular table...
375
17700
by: rkusenet | last post by:
This article is very bleak about future of DB2. How credible is the author. http://www.eweek.com/article2/0,1895,1839681,00.asp
15
6163
by: z. f. | last post by:
Hi, i have an ASP.NET project that is using a (Class Library Project) VB.NET DLL. for some reason after running some pages on the web server, and trying to compile the Class Library DLL, it can't compile because the DLL is in use (and the PDB too), and the w3wp.exe process is the process locking the DLL (as viewed with Sysinternals - Process...
5
1697
by: Chris Mullins | last post by:
I've spent some time recently looking into optimizing some memory usage in our products. Much of this was doing through the use of string Interning. I spent the time and checked numbers in both x86 and x64, and have published the results here: http://www.coversant.com/dotnetnuke/Default.aspx?tabid=88&EntryID=24 The benefits for our SoapBox...
2
1346
by: tim | last post by:
I am really new to ASP.NET and I was wondering if most developers use locking hints when accessing the data in SQL Server. What kind of multi-user issues come up when using an ASP.NET application?
9
2591
by: zmickle | last post by:
Experts and books all say that you can share an Access back end on a shared drive with the front end running on each host computer. I have a simple database that tracks student data and it is shared between 4 staff memebers. Each staff computer has a copy of the front-end (linked tables, forms, and queries). They basically only use one...
0
7405
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7428
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5334
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4949
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3455
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3444
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1887
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1019
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
709
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.