473,698 Members | 1,955 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 5060
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
4547
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
1505
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 however, this table is being replicated, so truncate table is no longer an option. I need to change...
1
5447
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
5121
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 the app is good). They 'shout round' to each other and get someone to exit the data entry screen....
2
1345
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 retrival takes time, the class would still provide a cached copy. now the code:
375
18033
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
6192
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 Explorer). this is a huge problem. i need to do IIS reset in order to free the DLL! 1. why is...
5
1700
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 suite of products are pretty compelling, memory wise. Before I roll the changes into our...
2
1358
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
2599
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 form. The form works like this
0
8668
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8597
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9148
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9012
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8855
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7708
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4358
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4611
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2319
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.