In MySQL, can multiple people be logged in simultaneously under the same account? Here’s why I am asking:
I am writing an app that will be running on different workstations (for the general public to use). These stations give read-only access to the database. However, the Xojo software will be logging in with one “username“ across all workstations. Will this work, or will each station have to have its own separate MySQL username to work simultaneously?
There will be about four workstations running the same app at the same time, using the same MySQL login credentials.
You usually create an “app or service credential” instead of a “person credential”. Multiple persons will use it transparently via your app. They even don’t need to know the credentials.
Imagine a website that have DB behind the curtains, the backend serves hundreds of people with something like user “website” password “%#3$%@21a24njjbh7cv”
1 Like
Be aware of this if you don’t have proper background:
https://www.guru99.com/dbms-concurrency-control.html
1 Like
Yes
the same account can log in as many times as you want and since each is a separate connection you should be fine
IF however you do ever need to WRITE or EDIT data then I could strongly recommend against using the same login name all the time
1 Like
You mean using Xojo native methods? Because as said, the only important thing should be using different sessions, and different instances of apps creates different sessions.
Writing, you control the protection using some level of SQL mechanisms like SELECT FOR UPDATE or some locking scheme available.
As general practice for data integrity.
1 Like
Sure if you want to lock up the entire database esp if its busy
Been there done that dont select for update unless you can GUARANTEE somone in accounts payable wont do something that selects for update and then take off for an hour and a half long lunch
Had ONE developer write an app like that - once - and it literally can lock up a db with lock contention
SELECT FOR UPDATE is row level, and transactions can rollback in timeout if not not closed. If closed, commit/rollback, the records are released.
No web application in the world uses multiple logins.
Implicit and explicit locking in MySQL using InnoDB engine
InnoDB uses a two-phase locking protocol. It can acquire locks at any time during a transaction, but it does not release them until a COMMIT or ROLLBACK . It releases all the locks at the same time. The locking mechanisms are all implicit. InnoDB handles locks automatically, according to your isolation level.
However, InnoDB also supports explicit locking, which the SQL standard does not mention at all:
SELECT … LOCK IN SHARE MODESELECT … FOR UPDATE
MySQL also supports the LOCK TABLES and UNLOCK TABLES commands, which are implemented in the server, not in the storage engines. These have their uses, but they are not a substitute for transactions. If you need transactions, use a transactional storage engine.
We often see applications that have been converted from MyISAM to InnoDB but are still using LOCK TABLES . This is no longer necessary because of row-level locking, and it can cause severe performance problems.
have you ever used this on a really busy db ?
Always, all you need is a good process design.
What I don’t need are messed results like selling the same seat twice or something like that or worse.
In SQL Server, I had one huge report that was “sleeping” ages to conclude due to dozens of lockings occurring and it waiting for unlocks, so the solution was to set a mode called “dirty read mode” for that specific SQL, it would ignore the lockings when reading and accept any records the query could find there, even locked. Because for that executive report, that should be enough.
low volume stuff like an executive report probably doesnt need to hang waiting on a consistent read state (which locks will prevent)
the financial report you submit to governmental agencies like the SEC etc does 
locks CAN lock up the entire db regardless of whether they are page level, table level, or row level which is why you even needed to switch dirty reads
thats all I was saying before
thanks for confirming that
mysql is no different here
That “executive report” generated hundreds of pages from millions of records. Not a “low volume” thing.
Before enhancements it took something like 2 hours, and decreased to less than 10 minutes later.
I’m sorry, you have no idea about what you are talking about.
In my previous experience thats relatively low volume
Millions of rows per day is more “high volume” and any locks there can lock the entire system up rapidly (just like you found with the MS sql report)