MySql revisited

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
vagblad
Posts: 95
Joined: Tue Jun 18, 2013 12:18 pm
DBs Used: MySQL,DBF
Location: Thessaloniki, Greece
Has thanked: 5 times
Been thanked: 10 times

Re: MySql revisited

Post by vagblad » Tue Aug 22, 2017 2:58 pm

BeGeS wrote:
Tue Aug 22, 2017 2:38 pm
I do not understand anything.
Regardless of the language or database type, a record must be blocked for modification. Otherwise, two users could modify it at the same time and the data of the last one would save the record.
And blocking the whole database to add a phone number, for example, seems excessive.
I can understand your frustration. Databases like MySql,SQL server etc. don't have the need to lock records in order to insert or update data.
And you wouldn't want to do that to be honest unless we are talking about some very niche situations. Using an autoincrement field as your primary key in sql makes sure you do not have duplicate records, do you have a table with usernames as well which you dont want to have duplicate?Easy, mark the username field as unique again. It will never allow you to enter the same name, it will always return an error code.

And if two users modify a record at the same exact time, so what? What's the problem with that? The last one who saved will update the record. Is it different if the 2 saves were 1min apart or 1 sec apart?

In my humble opinion the whole record-table locking thing was used in the past because of hardware and software restrictions mostly. We dont need to do that anymore. Ive worked professionaly with dbase the last few years only, i was always working with sql databases in the past and to be honest i find it frustrating and difficult some times. :)
To me MySql especially makes things so much much easier for the programmer.

@Dragan
Thank you very much for your effort.I think you will help a lot of people.
Vagelis Prodromidis
Email: vagblad@gmail.com, Skype: vagblad

User avatar
Rathinagiri
Posts: 5173
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 136 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Tue Aug 22, 2017 5:13 pm

I second vagblad.

I am working with MySQL for multiuser system and SQLite for single user system.

MySQL server handles more than 100s of users and 1000s of queries. ACID (Atomicity, Concurrence, Isolation and Durability) are the basic properties of SQL/Database transactions and MySQL adheres to this on the fly. We needn't worry about locking and all. If an user writes on a particular record and another user wants some data at the same time, server handles these requests efficiently without having any deadlock.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
mol
Posts: 2856
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 126 times
Been thanked: 65 times
Contact:

Post by mol » Wed Aug 23, 2017 5:29 am

Record locking is important in the situation, when user wants to edit record - this one should be locked to avoid changing it by another user/process until user writes changes to database or abandon edit.
This causes situations, when records stay locked even if no one operates on them - user left application open, user restarted computer without closing app and releasing record locks, etc.

User avatar
dragancesu
Posts: 462
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 3 times
Been thanked: 67 times

Post by dragancesu » Wed Aug 23, 2017 5:44 am

MySQL use some storage engine, major is

https://dba.stackexchange.com/questions ... and-myisam

User avatar
serge_girard
Posts: 1873
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 295 times
Been thanked: 68 times
Contact:

Post by serge_girard » Wed Aug 23, 2017 7:12 am

And if two users modify a record at the same exact time, so what? What's the problem with that? The last one who saved will update the record. Is it different if the 2 saves were 1min apart or 1 sec apart?
For simple applications probably no problem. But in banking, insurance etc. it is not allowed.

The solution used in mainframes is following:

Each table has at least 1 extra field containing the last timestamp of update (eg: change_date) which is auto-updated by MySQL when a record is updated by someone.

Now if you edit a record, the whole key + change_date is taken into memory. When saving (UPDATE table SET FIELDS .. WHERE key = complete_key AND CHANGE_DATE = old_change_date) the system while not find this record (when it was changed by antother user in about the same time) and generates a warning. Then the last user will see that the changes had wanted to make are overruled by another update.
Simple but effective but it has to be programmed standard .

Serge

User avatar
BeGeS
Posts: 90
Joined: Fri Jul 14, 2017 10:45 am
DBs Used: DBF
Location: La Mancha, Spain
Has thanked: 18 times
Been thanked: 17 times

Post by BeGeS » Wed Aug 23, 2017 5:46 pm

vagblad wrote:
Tue Aug 22, 2017 2:58 pm
I can understand your frustration. Databases like MySql,SQL server etc. don't have the need to lock records in order to insert or update data.
And you wouldn't want to do that to be honest unless we are talking about some very niche situations. Using an autoincrement field as your primary key in sql makes sure you do not have duplicate records, do you have a table with usernames as well which you dont want to have duplicate?Easy, mark the username field as unique again. It will never allow you to enter the same name, it will always return an error code.

And if two users modify a record at the same exact time, so what? What's the problem with that? The last one who saved will update the record. Is it different if the 2 saves were 1min apart or 1 sec apart?

In my humble opinion the whole record-table locking thing was used in the past because of hardware and software restrictions mostly. We dont need to do that anymore. Ive worked professionaly with dbase the last few years only, i was always working with sql databases in the past and to be honest i find it frustrating and difficult some times. :)
To me MySql especially makes things so much much easier for the programmer.
:shock:

Let me show my absolute disagreement with the things you say.

First, this issue has no relation to duplicate records. What's more, I open all files with the possibility of duplication and then decide where it can be and where not.

Neither is it related to old hardware and software, since blockages are common sense.

I can imagine the lack of blockages in certain situations (real) of some of my clients: the chaos could reach biblical proportions, and the culprit would be me. :cry:

In short, for me, locks are essential in database manipulation, whatever the type, and I can not imagine how they can be dispensed with in a serious application. The last one to record is the winner? Wow!

Greetings.
I get by with a little help from my friends

User avatar
mol
Posts: 2856
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 126 times
Been thanked: 65 times
Contact:

Post by mol » Wed Aug 23, 2017 6:05 pm

Imagine such a situation: two sellers sell the same product. Both of them read availability as 100. Everyone try to sell 100. How to control such a situation?

User avatar
apais
Posts: 258
Joined: Fri Aug 01, 2008 6:03 pm
Location: uruguay
Has thanked: 23 times
Been thanked: 15 times
Contact:

Post by apais » Wed Aug 23, 2017 7:08 pm

Same problem with dbfs.
You can't lock a dbf when there's user interaction because the user can go get a cofffe, leaving everyone else out.
So the first sale gets an ok and the second gets an error (out of stock).

User avatar
Rathinagiri
Posts: 5173
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 136 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Wed Aug 23, 2017 7:28 pm

I think everybody is discussing here about concurrence.

It is better to read MySQL documents about locking, begin...commit, and exclusive transactions.

https://dev.mysql.com/doc/refman/5.7/en ... reads.html
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
BeGeS
Posts: 90
Joined: Fri Jul 14, 2017 10:45 am
DBs Used: DBF
Location: La Mancha, Spain
Has thanked: 18 times
Been thanked: 17 times

Post by BeGeS » Wed Aug 23, 2017 8:13 pm

apais wrote:
Wed Aug 23, 2017 7:08 pm
Same problem with dbfs.
You can't lock a dbf when there's user interaction because the user can go get a cofffe, leaving everyone else out.
So the first sale gets an ok and the second gets an error (out of stock).
The problem is not of the type of database, it is of analysis. In this case the tools are the least.

When a user is selling products and is going to get a coffee with a reference asking for quantity on the screen, other users who want to output the same product will receive a "blocked reference, try later" message. It will not say that it is out of stock because it is not yet true, but at that moment another is manipulating the record and you do not know what will happen next.

Of course, blocking should only affect modifications, never to queries.
I get by with a little help from my friends

Post Reply