MSSQL Locking Problems
It has been a long time since my last post. Currently I’m very busy on a big project…
Yesterday we had a nice problem. A company I work is using the following configuration on it’s webserver: IIS, PHP and MSSQL. (yes an S). What’s wrong with this configuration? Well two things: IIS and MSSQL.
That’s not all: in PHP we connect to the database via 2 methods: via the ADODB COM object and the normal mssql functions from PHP.
We had the following problem in a certain module:
Every time we executed a Second query, the second query failed but we NEVER got an error message. Example:
(pseudo code:)
SELECT * FROM mytable WHERE kind=’item1′
… more code …
SELECT * FROM mytable WHERE kind=’item2′ // <= This query failed every time, without an error message
... more code 2 ...
COMMIT TRANSACTION
Looks very strange, but after too many hours we found our problem:
(pseudo code:)
… code 1 …
SELECT * FROM mytable WHERE kind=’item1′
…
INSERT .. INTO mytable( … ) VALUES ( … )
..
SELECT * FROM mytable WHERE kind=’item2′ // <= This query failed every time, without an error message
... code 3 ...
COMMIT TRANSACTION
What’s the problem:
SELECT * FROM … # is executed by mssql_* functions from PHP
INSERT … # is executed by ADODB
SELECT * FROM # is executed by mssql_* functions from PHP
..
Well when retrieving the data again the mssql_* connection fails because it notices the data has changed and it must wait for the other transaction to finish…
Aaaaaargh…. 8 hours of my time wasted!
Btw. A few year ago we had a good reason forusing ADODB in PHP: the mssql version of PHP wasn’t realiable. And we are using “queries from hell” ( sql server XML queries ), which (at that time) couldn’t be retrieve by the mssql functions of PHP.
