Global temporary tables as MYSQL named locks

Question

Global temporary tables as MYSQL named locks

dimeny.barna

SSC Rookie

Points: 45
More actions
June 20, 2013 at 8:10 am

#276486

Hello!
I was wondering if it is a good idea to use global temporary tables as MySQL named locks?
In certain situations I have to "lock" a record, so only one user can modify it. And by modification I mean here the whole process, not just the update command, which finally writes the new data into the table. So I thought that by creating a global temporary table with a well defined rule based name before starting the editing of the record, I could check, if a specific record of a table is being currently edited by someone. The global temporary table would be dropped after editing the record, or in case of an application, or some other crash, it would be dropped automatically by the SQL server. And one more thing, the global temporary table would contain only a few (2-5) fields, and a single record.
So, what do you think? Is it a good idea, or it could lead to performance problems having such temp tables?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WolfgangE SSCertifiable Points: 6328 More actions · Answer 1

hi,

I think it's NOT a good idea. I think you try to solve concurrency issues that are already part of the database engine of sql server. I think you did not work a lot with it?

All you need is an explicit transaction and the appropriate transaction isolation level. These are some basics for developing SQL code and you should get familiar with it. Start with SQL Server books online and read your way though.

Start with begin transaction and transaction isolation level 🙂

dimeny.barna SSC Rookie Points: 45 More actions · Answer 2

Hello!

Thank for your response.

I think I did not explain properly, what I'm trying to do. Here's an example: imagine a form with a datagrid on it, which contains a list of clients, and ther phone numbers (multiple numbers to each client). This list of clients is accessible to multiple users, and their task is to make a phone call to these clients. The duration of a such phone call may vary between less than a minute to few minutes. Let's say that user A opens the form, picks the first client from the list, and starts the phone call using the first phone number of the client. Meanwhile user B opens the form, picks the first client, tries to make the phone call, probably will use the second available phone number, because the first one will be occupied (by user A). And so user A and B has successfully called the same client, which should not happen. It would be simple to use an additional field, to mark each client, who is being called by one of the users, but these marks can become stuck if the application stops improperly for some reason.

So I'm not trying to make sure, that a record is written only by one user at a time, I'm trying to find something, that works similar as MySQL's named locks (basically they live until they are released, or until their session is alive), which ensures me, that the user who created it, is still connected, and his lock is still alive, and not just stucked for some reason. OK, I know that this way with any other application anyone can modify, even delete data, but again this time the goal is just to know, if someone has already started working with a record, and hasn't finished yet.

Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think you could fix it easier with a design change;

each attempt to contact, whether successful or not, inserts into a new table; a view gets added that shows the Last contact information, isntead of updating the existing row like you plan now.

the form with a datagrid on it selects fromt eh view, but inserts into a detail table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WolfgangE SSCertifiable Points: 6328 More actions · Answer 4

I see.

If the application crashes or something you still need a response to the database whether a call to a customer is finished or not. Otherwise a customer could be marked as "in progress" just because the application wrote this some days ago and did not acknowlege the finish of the progress.

I'm sorry to say that I don't have any idea dealing with this. I'm a database developer only and I think this is something to be handled by the application. Lacking of experience I have no idea.

Maybe you could do the following:

You add 2 fields to the customer: StartContact and EndContact, both datetime columns.

When starting calling number, you set the StartContact, when finishing you update the EndContact

If the application crashes you have a customer with a StartContact-date but without a EndContact-date.

You could query customers, where EndContadt-date is null and StartContact-date is, for example, more than 5 hours in the past.

If the query returns any customers you at least know that you should have a look at it.

This is no optimal solution. It's just something I would do, without knowing anything about applications, to do something better than noting 🙁

dimeny.barna SSC Rookie Points: 45 More actions · Answer 5

Hi there!

As I see, none of you recommended to implement my idea, so I will try some other solutions. Probably I'll choose to use an additional field, which will show, if the current record is being currently "occupied" by someone. This is the easiest way, and in my case it won't be a problem, if a record hangs in "occupied" state for a day, and during the night hours I can easily clean then records with a job without causing any problems.

Thank you for your responses!

liteswitch SSCommitted Points: 1824 More actions · Answer 6

I've done something similar in the past, having a web application that multiple users were using to update data at the same time. Rather than even thinking about concurrency issues at the database engine level (Which is a bad idea) I just designed the database accordingly, with two columns on the relevant table (or even a separate table) denoting the userID and date/time that the user accessed that record for checking/editing. The two columns were set when the user accessed the details, and cleared when they finished with that row (either decided not to do anything, or edited it) and the application would only allow one user to access that row while it was in use by someone else, so that made it easier, and you could also audit the user activity by this method too, i.e. you can see how many rows each user has audited/updated in a given time frame, which was very handy too.