Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users.

Over time many records are locked and editing is not possible anymore.

Is there a way to bulk unlock all records?

The Error Message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changed to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

share|improve this question

migrated from stackoverflow.com Sep 17 '12 at 22:00

This question came from our site for professional and enthusiast programmers.

    
it it still locked. UNLOCK TABLES; doesnt work for me. it's not the table that is locked, its the records. –  unr3al011 Sep 17 '12 at 7:01
    
UNLOCK TABLES; won't work in this case, because records were locked from some other session. Sorry about that. –  Sreenath Soman Sep 17 '12 at 7:17
1  
This sounds like a MySQL issue. Add a mysql tag to attract the attention of more people who know MySQL. –  HansUp Sep 17 '12 at 7:28
    
It looks like Access does the locking. Related: stackoverflow.com/questions/12026199/… –  ta.speot.is Sep 17 '12 at 8:05
    
yes but, even if this would work, this will make the data read-only. but it has to be writeable too. –  unr3al011 Sep 17 '12 at 8:13
show 2 more comments

2 Answers

To help you troubleshoot this, here is a very important piece of information to keep in mind:

Locks, in MySQL, whether they are on rows, tables, advisory/named locks, and even the global FLUSH TABLES WITH READ LOCK are held by continued existence of the specific connection thread that obtained the lock. When I say "connection thread," in this context, I mean the specific instance of a client's connection to the server... the one where the lock was requested. These are the threads listed in SHOW FULL PROCESSLIST;

If the requesting connection is closed gracefully, all locks requested by that connection release immediately.

If the specific connection thread is killed by an admin (mysql> KILL #; where # is the ID from SHOW FULL PROCESSLIST that you want to force to disconnect) the locks are released as soon as that thread disappears from SHOW FULL PROCESLIST;

If that connection is not closed gracefully, the connection eventually times out (the default is 28800 seconds unless you have changed it) and then the locks are released.

Row and table locks cannot persist in the absence of the connection that originally obtained the lock... so there is no such thing in MySQL as locks piling up over time and needing to be unlocked, in the absence of client connections still holding on to the locks.

Even if it's the case that clients are improperly holding locks, then forcing a disconnect of the client will be your only workaround, as locks are there for a reason... to keep your data consistent. Forcing disconnect does not mean having everyone log out, since you've explain this isn't practical. See the KILL command, above.

My speculation is that Access is not properly interpreting responses from MySQL and only thinks records are locked when in fact it's something else.

As has already been observed, you should be able to update values in these rows using Toad, HeidiSQL, Query Browser, Workbench, or simply the mysql command line... and it's very likely you will be able to update them no problem... proving that they aren't really locked, and that Access is just confused.

Posting the precise wording of the message users are seeing from Access will probably be the best way to shed accurate light on the problem.

share|improve this answer
    
The Error: This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changed to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes. –  unr3al011 Sep 18 '12 at 6:13
    
But i found a solution how to Fix it: i added a Field named "TIMESTAMP" of type timestamp. Then i updated all records with TIMESTAMP=NOW(); –  unr3al011 Sep 18 '12 at 6:14
add comment
up vote 2 down vote accepted

I finally could fix my problem by adding a field named TIMESTAMP of type timestamp and default value CURRENT_TIMESTAMP and attribute = on update CURRENT_TIMESTAMP. Then i updated all records and set TIMESTAMP=NOW().

For now i did not get any error again.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.