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:)

BEGIN TRANSACTION
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:)

BEGIN TRANSACTION
... 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:

BEGIN TRANSACTION # is executed by ADODB
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.

Share

Flash Actionscript XML.send( … ) doesn’t send

Once in a while you require Flash… Well yesterday was such a day :-)
I’m was working on a Flash component that tries to submit data back to the server…
I don’t have a lot of flash experience so I still don’t know all its quirks.

Well I was trying to submit an XML document the following way:

var xml:XML = new XML()
//... fill xml ...
xml.send( '/my/url' );

Well… it didn’t submit! No interaction with the server…
After some searching I discovered this was caching. Always that stupid caching…
So I solved this by adding a timestamp:

xml.send( '/my/url?ts=' + ( new Date() ).getTime() )

Again the timestamp to the rescue…

Share