Perl DBI(ODBC) to MSSQL

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Help!!!

I have several servers that use the Perl ODBC DBI driver to access the same backend MSSQL(2000) server. My goal is to be able to do the following:

if (table lock successful)
{
  make some updates to the table
  unlock the table
}
else
{
  sleep to give whoever got the lock time to finish
}

All my servers will be running the same code at the same time, so its a matter of who gets there first, wins! I've been trying to find the LOCK command syntax for MSSQL tables, but I have come up short. Does anyone have any suggestions on how to achieve this goal? And yes, unfortunately, it has to be MSSQL.(grumble)

thanks!

Comment on Perl DBI(ODBC) to MSSQL

Replies are listed 'Best First'.
Re: Perl DBI(ODBC) to MSSQL by runrig (Abbot) on Oct 12, 2007 at 22:05 UTC
Do they really need to lock the table, or would database transactions suffice? You can use transactions if you turn AutoCommit off (see DBI docs), and then commit() when you are done updating. The thing to worry about here is multiple people selecting the same data, then someone updating it first, then someone else updating it based on stale data (if you care about this situation). But there are ways to solve that also without resorting to locking the entire table.	[reply]
Re^2: Perl DBI(ODBC) to MSSQL by pparrish (Initiate) on Oct 12, 2007 at 22:11 UTC
I have multiple servers hitting the backend database. I only want one of them to make the updates to the table. I dont really care which one makes the update, just the first one to get there I guess. If I setup a transaction, will that mean that each of the servers will simply wait until the table is available and then attempt to run the code since they are doing the connections in seperate threads on seperate boxes?	[reply]
Re^3: Perl DBI(ODBC) to MSSQL by runrig (Abbot) on Oct 12, 2007 at 22:14 UTC
Depending on the locking scheme for the table (page or row level or whatever else MSSQL has), you shouldn't have to wait for the table, but may have to wait for the row or page.	[reply]
Re: Perl DBI(ODBC) to MSSQL by eric256 (Parson) on Oct 12, 2007 at 23:45 UTC
Oracle lets you do a `select for update` which locks the table it reads from until the end of the transaction. Maybe MSSQL has somethingl ike that? ___________ Eric Hodges	[reply] [d/l]
Re^2: Perl DBI(ODBC) to MSSQL by Somni (Friar) on Oct 13, 2007 at 00:59 UTC
You have to be careful and read the docs regarding exactly what `SELECT ... FOR UPDATE` locks. Specifically, with Postgres, `SELECT ... FOR UPDATE` is a row level lock on the returned rows. As a result you can't use aggregates. So, you have to analyze carefully what your entire transaction needs to do, then decide if you need to lock or the natural transaction consistency will suffice. If you do need to lock, make sure to lock as little as possible, but be sure to lock it such that you can avoid deadlocks. For example, with a recent application I was writing that uses Postgres, I needed to do some aggregating to determine what to insert. I could've locked with SHARE mode. However, two sessions locking with SHARE mode don't conflict, so the subsequent implicit ROW EXCLUSIVE lock performed by the insert later might've resulted in a deadlock.	[reply] [d/l] [select]