wait timeout for locks

#11

Peter H. Coffin wrote:

Quote:

[...] I am now
locking database records when the user stars to modify them, so no
other user can do the same thing until they're free again. [...]

There's two common ways of handling this: a sloppy and easy way, and a
clever, powerful way that's a lot more work. The easy, sloppy way is to
have a marker set on the record when someone opens up the record for
editing so that anyone that comes along later and also opens the record,
they get a little alert box [...]

The clever and lot of work way keeps a copy of the record as it
originally was when loading the data for working. You don't need to
notify anyone else until such time as the user tries to save the record,
at which point it goes through all the relevant columns, and looks for
values that are NOT the same as in the orignal record [...]
Where there's conflicts, ask the user what to do.

I will try to combine the two methods: using a varchar marker for the
username of the "locking" user (serving only as a warning) and a review
popup in case of conflicts, thus avoiding the "ping" or the "lock wiping"
scheduled script.
I am now going to discuss these issues with the boss. If it depended on
me, I 'd go for it straight away, but time is running out, so the choice
is on who pays the bills.

Thank you very much for your help and your time.
Thank you also to Lennart Axel for pointing me to OCC details.

Best Regards
--
Enos

#12

Lennart Jonsson wrote:

Quote:

[...] have a locked_time and a locked_by attribute
in the table, and a thread that touches the lock say every 10 seconds
(and commits each touch immediately).

Another user can take over the lock if it's locked by himself or older
than say 30 seconds, because that means something bad happened to the
client holding the lock. [...]

It looks like it will work, but thinking about it, I don't fancy having a
"ping" running continuously. The client itself is not engineered as
internally multithreaded and it is supposed to work smoothly even through
the private DSL line, that connects different offices.

Quote:

[...] There are other possible variants as well. Example:

begin transaction
select row_1
end transaction

do edit in client

begin transaction
select row_2
if row_1 == row_2
update row
commit
else
<inform user
rollback

// Here a line must be added after the user sees his edit vanish
<user kills programmer>

Quote:

end

I need the bit that Peter added: present the user with a review popup.
I never thought about it, but it seems to combine with what we talked
about, serving for the purpose as well as the lengthy translation.

I'm off to decide what to do with the boss.

Thank you very much,
Best Regards

--
Enos

#13

I wrote:

Quote:

Peter H. Coffin wrote:
There's two common ways of handling this: a sloppy and easy way, and a
clever, powerful way that's a lot more work. The easy, sloppy way is to
have a marker set on the record when someone opens up the record for
editing so that anyone that comes along later and also opens the
record, they get a little alert box [...]

I will try to combine the two methods: [...]

We ended up choosing the "sloppy and easy way", adding an unavoidable
lock field, and a superuser feature to removed persistent locks.

I stumbled on this old Google Tech Talk about MySQL performances. It
talks about indexes, table partitioning, nested selects, data size, etc.
I found it quite illuminating, maybe someone else will appreciate it:

http://www.youtube.com/watch?v=u70mkgDnDdU

Bye,
--
Enos

#14

On 9/21/2010 10:22 AM, Lennart Jonsson wrote:

Quote:

On 2010-09-21 16:38, enos76 wrote:
[...]

Hello Peter, thank you for your interest in my issue.

You got me there! I am indeed locking records indefinitely (BEGIN;
SELECT ... WHERE ... FOR UPDATE

while the user enters data. I feel
guilty for what I'm doing, and it may be too late to change design.

Here is the scenario: I was told to build a networked application with
Java and Mysql, without using a WEB server or any other client-server
architecture. I did express my perplexity, but the boss insisted, so the
project started. Java Swing and MySQL through JDBC. I am now locking
database records when the user stars to modify them, so no other user can
do the same thing until they're free again.
The only alternative I can see with the limitations the management put,
is to dedicate database tables to the only purpose of locking records.

Your basic options are a "checkout" system or a "resolve conflict"

system.

Wikipedia has a "resolve conflict" system. When you start editing
an article, the browser is using a URL provided by the server which
contains an edit serial number or timestamp. When the user saves
the article, the database (which is MySQL) checks whether the timestamp/
serial coming in via the browser's POST request matches the last update
timestamp/serial for that article in the database. If it doesn't, the
user gets the "edit conflict" message. It's important, at this point,
that the client end not lose the user's work and that the client give
the user some way to decide what to do next - abandon the change,
make it anyway based on the updated state of the database, or work to
combine the two changes.

In a "checkout" system, when one user tries to edit something that's
being edited by someone else, the request to edit is rejected. The
problem with this is abandoned checkouts, where a user has checked
something out and left it that way. Early source code management
systems like SCCS and SourceSafe were "checkout" systems; newer
ones tend to be "resolve conflict" systems. "Checkout" systems
over HTTP work badly, because it's hard to tell if the client went
away.

Is this really a "networked application", where you have a constant
open connection to a client program you control, or is this something
where the client is a browser making HTTP requests?

John Nagle

#15

John Nagle wrote:

Quote:

Your basic options are a "checkout" system or a "resolve conflict"
system. [...]

Is this really a "networked application", where you have a constant
open connection to a client program you control, or is this something
where the client is a browser making HTTP requests?

I fear I may have used a specialistic term by mistake.

In layman's terms, two offices are connected by a private DSL line. In
one office a powerful MySql server is listening, while a dozen Java
Swing clients connect from both offices through the JDBC library.

Initially I kept a single connection open, but I though it was bad
practice, so I dediced to close it after every operation. The decision
brought performance problems though, as many lists had to be updated
after user actions, so I ended up using the connection pool from the
JConnector MySQL JDBC package (with quite some satisfaction, I must say).

After weighting the various suggestions I received from this group, the
boss decided to opt for the "checkout" system, mostly due to time issues.
We also went for some optimizations, among which reporting the blocking
user name and a superuser feature to remove locks.

By the way, the decision was taken right after I had finished
implementing the transaction based-system!

Regards,
--
Enos

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

wait timeout for locks

comp.databases.mysql comp.databases.mysql

Re: wait timeout for locks - 09-22-2010 , 03:16 AM

Re: wait timeout for locks - 09-22-2010 , 03:26 AM

Re: wait timeout for locks - 09-22-2010 , 08:06 AM

Re: wait timeout for locks - 09-23-2010 , 02:03 PM

Re: wait timeout for locks - 09-23-2010 , 02:38 PM