Right database for storing large amounts of entity relationship data -

i'm trying choose database suits use case. here simple example of i'm trying store in database:

lets say, have 'users' , 'groups'. each user can part of number of groups. there hierarchy in groups ie.. each group can have multiple child groups.(and child groups can again have grandchildren , on...)

requirements:

• i adding multiple users parent group , should automatically added child groups.. so, have child groups of given group , add these users every 1 of them. operation should atomic.
• i have multiple threads running in parallel updating groups & adding users them. don't want race conditions.

if go rdbms approach, have user table, group table , relationship tables user-group mapping , parentgroup-childgroup mapping. able handle requirements using transactions/locks etc..

but, - number of users going few million , each user on average part of couple of hundred groups , height of group hierarchy tree less(<20), relationship tables going really big. - data stored in user table unstructured.

from understand,

nosql databases can handle scale , unstructured data don't provide acid features need , sql databases not handling large amounts of data.

i don't need complex graph traversal queries expect 1 mentioned above. need simple queries getting users in group, getting groups user etc.. these queries should fast.

so, correct database use case??