write Conflict

[Jaspal]

Hi All,

I have an access from end application with linked tables to SQL server. The tables are access by many users at the same time (multi user enviroment). The Users are able to scroll through, edit etc the records. However now and again users are getting the message box that there is a "write conflict", at first i thought it was when the users were in the same record at the same time, but this is not the case.

I am currently searching for other answers and will carry on to do so. However if anybody has a solution or suggestion that would be great!

Many thanks in advance

 

[FoFa]

Not sure if this is any help, but SQL can use either Row, Page or Table locking. So someone may not have to be on that exact record and you can still get a lock. Also if you read a record, and update AFTER someone else has updated it between your read and update, that can be an issue. What the best thing to do is when you have this happen, use the SQL tools to determine what is causing the lock.

 

[Jaspal]

Thanks for the help, what would be the best tools to use in this situation

 

[FoFa]

If you have EM (Enterprize Manager) it is a GUI tool you can see the blocking going on. If you have QA (Query Analyzer) you can run SP_WHO or SP_WHO2 to determine the blocking.

 

[Jaspal]

okay FoFa thanks for your help ill give that a shot

 

[Matty]

In the past, I've encountered a write conflict error when I KNEW there weren't other users locking the record. After much searching, I found that if I have any bit fields in my tables, they need to be set to not allow nulls and their default value has to be set to zero.

So if you're still having trouble, give that a shot.

 

[Geoff Convery]

That's interesting, Matty. If you make the change retrospectively or set the value of the bit field manually does it release the apparent lock?

 

[Matty]

I'm thinking it would, but I'm not sure. I know there's a thread about this on here somewhere, but it could have been lost when a bunch of threads got wiped out a while back.

I just know that when I went through my tables and set default values to zero and did not allow null values, my Write Conflict errors went away. I now just set bit fields that way when I create them from now on. I was never able to figure out a reasonable explanation for it.