What happens if two users perform an SQL query to an access database at the same time

[ks101]

I have tried to simulate it but it still worked. I am assuming if they submitted at the same exact milisecond there might be a problem.

If there is a conflict, what happens? Does it error out as read only (because the DB is locked)?

If so, should I just do a try/catch to keep retrying up to a certain number of times in the event of this happening?

[dday9]

If you attempt to have multiple connections open at the same time then an InvalidOperationException will occur. What you should do next is dependent on how the query is being submitted, is it automatic or is a user submitting it?

[ks101]

It's a result of a button press.

If an error will happen (which is fine, the two things I dont want to happen are data loss or the user thinking the data was written when it wasnt) then should I do something like this? :

Try

(write data)

catch retry as exception

(wait 2 seconds)

(write data)

end try

[FunkyDexter]

That's reasonable. There are two broad options:-
1. Tell the user it failed and give them the option to resubmit
2. Resubmit automatically so the user isn't even aware there was a problem (except it too a few more seconds than usual.

You've basically picked option 2. The only thing worth considering is that the 2nd attempt may also fail. And the third. And the fourth... Sooner or later you should stop iterating and let the user know.

[SJWhiteley]

Obviously, we don't know the situation regarding the database, but considering that you are testing such an situation with multiple users, wouldn't a proper multi-user database system be more appropriate? e.g. SQLServer Express.

[techgnome]

What you are dealing with is what's known as concurrency issues... and it's something that is at the heart of every centralized database system. There are a couple of ways of dealing with it. Actually, let me back up some. Your question isn't entirely clear on what kind of queries we're talkng about... a selection query or an action query ... selection queries are just that, queries that select data. Action queries actually perform an action, Insert, Update, Delete. With Select queries, concurrency is generally a non-issue. That leaves Inserts, Updates and Deletes.
Insert - It is very rare for two users to be entering the same new record at the same time. I can't even think of a fringe case where that would happen - actually, in the case of one of my clients, I could see it happening, but even for them it would still be a stretch.
Delete - if two users try to delete the same record at the same time ... one is going to get it and the other isn't. I'm not sure how much of an issue that is, but you can test for the record, and if it's already been deleted, simply report back to the user that it's been deleted (optionally tell them that someone else did it.)
That leaves updates, where the biggest issue of concurrency happens. Generally, there's two ways to deal with this: optimistically, and pessimistically. In the optimistic mode, you simply update the record. You're "optimistic" that your data is the latest and greatest. It's also known as "last one to update wins." In a previous life, this is how we dealt with it. We let our customers know, and for the most part they were OK with that and understood the ramifications. Due to the nature of their business, it was highly unlikely that there would be concurrency issues in the first place.
In the pessimistic mode, it's more of a "trust by verify" kind of mentality... you're trusting that you have the latest, but you verify it first. This is usually done by putting some kind of timestamp on the record. When you read the record, you grab the timestamp. When you then go to save, you first check the stamp on the record, if it has NOT changed, you issue the update... if it HAS changed, or is different from what was loaded, it means someone updated the record since the user last loaded it. You can then notify them, throw an error, or something to indicate to the user that something has since changed. This is what the system I work on now does. Every record in every table has a TS field that gets loaded when the record is loaded. On a save, the loaded TS is compared to the database TS and if they differ, an error is thrown and the user is notified that the record has since changed and cannot be saved.

There are other ways as well, such as merging, and if the data is simple that's fairly easily done... but if the data is complex in any way... it can become a big mess real quick.

-tg

[ks101]

What you are dealing with is what's known as concurrency issues... and it's something that is at the heart of every centralized database system. There are a couple of ways of dealing with it. Actually, let me back up some. Your question isn't entirely clear on what kind of queries we're talkng about... a selection query or an action query ... selection queries are just that, queries that select data. Action queries actually perform an action, Insert, Update, Delete. With Select queries, concurrency is generally a non-issue. That leaves Inserts, Updates and Deletes.
Insert - It is very rare for two users to be entering the same new record at the same time. I can't even think of a fringe case where that would happen - actually, in the case of one of my clients, I could see it happening, but even for them it would still be a stretch.
Delete - if two users try to delete the same record at the same time ... one is going to get it and the other isn't. I'm not sure how much of an issue that is, but you can test for the record, and if it's already been deleted, simply report back to the user that it's been deleted (optionally tell them that someone else did it.)
That leaves updates, where the biggest issue of concurrency happens. Generally, there's two ways to deal with this: optimistically, and pessimistically. In the optimistic mode, you simply update the record. You're "optimistic" that your data is the latest and greatest. It's also known as "last one to update wins." In a previous life, this is how we dealt with it. We let our customers know, and for the most part they were OK with that and understood the ramifications. Due to the nature of their business, it was highly unlikely that there would be concurrency issues in the first place.
In the pessimistic mode, it's more of a "trust by verify" kind of mentality... you're trusting that you have the latest, but you verify it first. This is usually done by putting some kind of timestamp on the record. When you read the record, you grab the timestamp. When you then go to save, you first check the stamp on the record, if it has NOT changed, you issue the update... if it HAS changed, or is different from what was loaded, it means someone updated the record since the user last loaded it. You can then notify them, throw an error, or something to indicate to the user that something has since changed. This is what the system I work on now does. Every record in every table has a TS field that gets loaded when the record is loaded. On a save, the loaded TS is compared to the database TS and if they differ, an error is thrown and the user is notified that the record has since changed and cannot be saved.

There are other ways as well, such as merging, and if the data is simple that's fairly easily done... but if the data is complex in any way... it can become a big mess real quick.

-tg

I ended up implementing a record locking system. A field gets added to the database called "current editor" and it locks out any other user who does not have that username.

I built a GUI for the interface from scratch in VB.NET, that mirrors the database list in a DataGridView, with a doubleclick event on the DataGridView opening a ticket for editing (i.e. a form that populates all the fields into the form, then resaves them to the record, allowing you to update all the fields).

If someone has the EditTicket form open, it adds their username to the "Current Editor" field for that record, and if anyone else tries to open the record while they have it open in my program, it will lock them out, but give them the option to override if they are an administrator.

[Shaggy Hiker]

That may be overkill without actually solving the problem. As long as collisions are rare, you could live for a very long time this way, but you may have created a race condition.

How can any program know whether or not it can use a record? It has to query for that field to know whether or not it can acquire the lock. How do you handle the case where two programs try to acquire the lock at the same time? Unless it was handled with some kind of atomic operation (which may not exist), they might both see the field as empty, and both try to lock the record.

[techgnome]

There's a reason I didn't suggest the locking approach. I personally do not like that kind of locking system at all. Two biggest problems with it: - and both of these will happen - 1) Someone opens the record, locks it... then goes to lunch... a two hour power lunch... 2) While they were at their extended power lunch, computer crashes... now the record is locked because it never got the chance to be unlocked. Now you have to have it manually unlocked, which means finding the right person to do so. And that person is on vacation - you may mock that but I have seen that exact scenario play out in real life. In that case, as well as in yours, it will end up with someone going into the database directly and unlocking the record.

All this means, that the moment you lock that record in that fashion, any other operation on that record will operate in an "abnormal" case. It's just not good.

-tg